CREATE USER MAPPING - Hologres - Alibaba Cloud Documentation Center

In Hologres V3.0 and later, you can configure a mapping between a RAM role and an account on a Hologres instance. This allows you to use the account to access external systems by assuming the RAM role. Currently, you can use this method to access only Data Lake Formation (DLF) 2.0 and other Hologres instances. This topic describes the usage and limits of the CREATE USER MAPPING statement.

Background information

You can execute the CREATE USER MAPPING statement to create a mapping between the Hologres logon user (including a BASIC user) and an Alibaba Cloud account of an external service. This allows the user to access external services, such as MaxCompute, DLF, Object Storage Service (OSS), or another Hologres database. For more information, see CREATE USER MAPPING.

Limits

Only the superuser of a Hologres instance or the owner of a foreign server can grant related permissions to common users. Users that have the USAGE permission on a foreign server can create user mappings for themselves.
You cannot create user mappings for RAM roles to access data in MaxCompute or DLF 1.0.

Syntax

Configurations for external databases

MaxCompute data source

Syntax

CREATE USER MAPPING FOR "<user_name>"
EXTERNAL DATABASE <ext_db_name>
OPTIONS
(
    access_id '<Access_id>',
    access_key '<Access_key>'
);

Parameters

Parameter	Description
user_name	The account to which you want to grant permissions, such as a RAM user, a Hologres internal account (BASIC user), or a public account. A public account is available to all users and must be used with caution.
Access_id	The AccessKey ID of the account that is used to log on to Hologres. You can log on to the Resource Access Management (RAM) console to obtain the AccessKey ID.
Access_key	The AccessKey secret of the account that is used to log on to Hologres.

DLF data source

Syntax

CREATE USER MAPPING FOR "<user_name>"
EXTERNAL DATABASE <ext_db_name>
OPTIONS
(
    -- For DLF1.0
    [dlf_access_id 'LTxxxxxxxxxx',
    dlf_access_key 'y8xxxxxxxxxxxxx',
    oss_access_id 'LTxxxxxxxxxx',
    oss_access_key 'y8xxxxxxxxxxxxx'] 

    -- For DLF2.0
    [dlf_access_id 'LTxxxxxxxxxx',
    dlf_access_key 'y8xxxxxxxxxxxxx'] | 
    [rolearn 'RoleARN']
);

Note

The hologram::GrantAssumeRole policy must be configured in the RAM console to allow users to create user mappings for the Alibaba Cloud Resource Name (ARN) of a RAM role. Hologres checks whether a user has the permissions to create a user mapping for the ARN of a RAM role based on this policy. Authorized users can create a user mapping between the specified ARN of a RAM role and any user on the Hologres instance. Example:

{
  "Version": "1",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "hologram:GrantAssumeRole",
      "Resource": "<Role_ARN>"
    }
  ]
}

Parameters

Parameter	Description
user_name	The account to which you want to grant permissions, such as a RAM user, a Hologres internal account (BASIC user), or a public account. A public account is available to all users and must be used with caution.
ext_db_name	The external database that is created in Hologres by executing the CREATE EXTERNAL DATABASE statement. The external database is used to load the metadata of external data sources to Hologres. For more information, see CREATE EXTERNAL DATABASE.
access_id	The AccessKey ID of the account that is used to log on to Hologres. You can log on to the RAM console to obtain the AccessKey ID.
access_key	The AccessKey secret of the account that is used to log on to Hologres.
dlf_access_id	The AccessKey ID of the Alibaba Cloud account or RAM user that has the read and write permissions on DLF.
dlf_access_key	The AccessKey secret of the Alibaba Cloud account or RAM user that has the read and write permissions on DLF.
oss_access_id	The AccessKey ID of the Alibaba Cloud account or RAM user that has the read and write permissions on Object Storage Service (OSS).
oss_access_key	The AccessKey secret of the Alibaba Cloud account or RAM user that has the read and write permissions on OSS.
rolearn	The ARN of a RAM role, which is the globally unique resource identifier of the RAM role. An ARN is in the `acs:ram::<account-id>:role/<role-name>` format. `<role-name>` is the name of a RAM role in lowercase. For more information about how to view the ARN of a RAM role, see View the information about a RAM role.

Query the configured user mappings of an external database

SELECT
    datname,
    usename,
    eumoptions
FROM
    pg_holo_external_user_mappings;

Configurations for foreign servers

MaxCompute data source

Syntax

CREATE USER MAPPING FOR "<user_name>"
SERVER odps_server  -- The server is fixed.
OPTIONS
(  
   access_id '<access_id>', 
   access_key '<access_key>'
);

Parameters

Parameter	Description
user_name	The account to which you want to grant permissions, such as a RAM user, a Hologres internal account (BASIC user), or a public account. A public account is available to all users and must be used with caution.
access_id	The AccessKey ID of the account that is used to log on to Hologres. You can log on to the RAM console to obtain the AccessKey ID.
access_key	The AccessKey secret of the account that is used to log on to Hologres.

DLF data source

Syntax

CREATE USER MAPPING FOR "<user_name>"
SERVER <server_name>
OPTIONS
(
    -- For DLF1.0
    [dlf_access_id 'LTxxxxxxxxxx',
    dlf_access_key 'y8xxxxxxxxxxxxx',
    oss_access_id 'LTxxxxxxxxxx',
    oss_access_key 'y8xxxxxxxxxxxxx'] 

    -- For DLF2.0
    [dlf_access_id 'LTxxxxxxxxxx',
    dlf_access_key 'y8xxxxxxxxxxxxx'] | 
    [rolearn 'RoleARN']
);

Parameters

Parameter	Description
user_name	The account to which you want to grant permissions, such as a RAM user, a Hologres internal account (BASIC user), or a public account. A public account is available to all users and must be used with caution.
server_name	The name of the foreign server. For a MaxCompute data source, the value is fixed as odps_server. For other data sources, you can specify a custom value. For more information, see Create a foreign server.
access_id	The AccessKey ID of the account that is used to log on to Hologres. You can log on to the RAM console to obtain the AccessKey ID.
access_key	The AccessKey secret of the account that is used to log on to Hologres.
dlf_access_id	The AccessKey ID of the Alibaba Cloud account or RAM user that has the read and write permissions on DLF.
dlf_access_key	The AccessKey secret of the Alibaba Cloud account or RAM user that has the read and write permissions on DLF.
oss_access_id	The AccessKey ID of the Alibaba Cloud account or RAM user that has the read and write permissions on OSS.
oss_access_key	The AccessKey secret of the Alibaba Cloud account or RAM user that has the read and write permissions on OSS.
rolearn	The ARN of a RAM role, which is the globally unique resource identifier of the RAM role. An ARN is in the `acs:ram::<account-id>:role/<role-name>` format. `<role-name>` is the name of a RAM role in lowercase. For more information about how to view the ARN of a RAM role, see View the information about a RAM role.

Hologres data source

Syntax

CREATE USER MAPPING FOR "<user_name>"
SERVER <server_name>
OPTIONS (
  access_id '<access_id>', 
  access_key '<access_key>'
);

Parameters

Parameter	Description
user_name	The account to which you want to grant permissions, such as a RAM user, a Hologres internal account (BASIC user), or a public account. A public account is available to all users and must be used with caution.
server_name	The name of the server that is created to implement data queries across databases in Hologres. For more information, see Query data across databases (beta).
access_id	The AccessKey ID of the account that is used to log on to Hologres. You can log on to the RAM console to obtain the AccessKey ID.
access_key	The AccessKey secret of the account that is used to log on to Hologres.

Query the configured user mappings of an internal database

SELECT
    um.srvname AS "Server",
    um.usename AS "User name"
FROM
    pg_catalog.pg_user_mappings um
WHERE
    um.srvname != 'query_log_store_server'
ORDER BY 1, 2;

Examples

External database (recommended)

Create an external database in a Hologres instance to map an Apache Paimon catalog in DLF 2.0. Only users that have superuser permissions on the instance or the owner of the external database can access the external database.

CREATE EXTERNAL DATABASE ext_db_dlf
WITH 
  metastore_type 'dlf-paimon' 
  catalog_type 'paimon' 
  dlf_region 'cn-hangzhou' 
  dlf_endpoint 'dlfnext-share.cn-hangzhou.aliyuncs.com' 
  dlf_catalog 'clg-paimon-d241aee***********';

Create a RAM role in the RAM console and execute the CREATE USER MAPPING statement in Hologres to create a mapping between the RAM role and a RAM user.
1. Create a RAM role and attach policies to the role.
  1. Log on to the RAM console. In the left-side navigation pane, choose Identities > Roles. On the page that appears, click Create Role. Select Alibaba Cloud Service for Select Trusted Entity and Normal Service Role for Role Type, configure the RAM Role Name parameter, and then select Hologres from the Select Trusted Service drop-down list. Then, click OK.
  2. On the Policies page, click Create Policy. On the JSON tab, configure policies for permission check in Hologres and DLF.
    Sample code for Hologres:
```
{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "hologram:GrantAssumeRole",
            "Resource": "acs:ram::xxx:role/testramrole"
        }
    ]
}
```
    Sample code for DLF:
```
{
    "Version": "1",
    "Statement": [
        {
            "Action": "dlf:CheckPermissions",
            "Resource": "*",
            "Effect": "Allow"
        }
    ]
}
```
  3. On the Roles page, select the RAM role and click Grant Permission to attach policies to the RAM role.
2. Execute the CREATE USER MAPPING statement in Hologres to create a mapping between a RAM user and the RAM role. This way, the RAM user obtains permissions on the ext_db_dlf database.
```
CREATE USER MAPPING FOR "p4_203535926287167253"
EXTERNAL DATABASE ext_db_dlf
OPTIONS
(
    rolearn 'acs:ram::xxx:role/testramrole'
);
```
Log on to the Hologres console as the RAM user and use the SQL editor to query data.
```
SELECT * FROM ext_db_dlf.dlf_db.paimon_table;
```

Foreign server

This section describes how to grant permissions on a MaxCompute foreign table to a Hologres BASIC account.

Create a BASIC account in Hologres.
```
CREATE USER basicuser;
```

Grant access permissions on odps_server to the BASIC account.

CREATE USER MAPPING
FOR "basicuser"
SERVER odps_server
OPTIONS
(
    access_id 'LTxxxxxxxxxx',
    access_key 'y8xxxxxxxxxxxxx'
);