Create user mapping-Hologres(Hologres)-阿里云帮助中心

Hologres V3.0 and later versions support mapping RAM roles to accounts in an instance. This allows a logon account to assume a RAM role for cross-account access to external systems. Currently, you can use a RAM role to access only DLF 2.5 data sources and other Hologres instances. This topic describes the usage of and conditions for the CREATE USER MAPPING command.

Background information

A user mapping defines a relationship between a Hologres logon user and an Alibaba Cloud account. It allows the current Hologres user, including a BASIC user, to use a user mapping to access external services across accounts. These services include MaxCompute, DLF, OSS, and cross-database queries in Hologres. For more information, see PostgreSQL CREATE USER MAPPING.

Limits

To grant permissions to a regular user, you must have SuperUser permissions for the Hologres instance or Owner permissions for the foreign server. A user with Foreign Server Usage permissions can also create a user mapping for themselves.
MaxCompute data sources and DLF 1.0 do not support configuring user mappings with RAM roles.

Syntax description

Configuration for an external database

MaxCompute data source

Syntax

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

Parameter description

Parameter Name	Description
user_name	The account to be granted permissions. This can be another RAM user, an internal Hologres account (BASIC account), or public for public access to all users. Use public with caution.
Access_id	The AccessKey ID of the current account. To get the AccessKey ID, go to the Resource Access Management (RAM) console.
Access_key	The AccessKey secret of the current account.

DLF data source

Syntax

CREATE USER MAPPING FOR "<user_name>"
EXTERNAL DATABASE <ext_db_name>
OPTIONS
(
    -- For DLF 1.0
    [dlf_access_id 'LTxxxxxxxxxx',
    dlf_access_key 'y8xxxxxxxxxxxxx',
    oss_access_id 'LTxxxxxxxxxx',
    oss_access_key 'y8xxxxxxxxxxxxx'] 
    -- For DLF 2.5
    [dlf_access_id 'LTxxxxxxxxxx',
    dlf_access_key 'y8xxxxxxxxxxxxx'] | 
    [rolearn 'RoleARN']
);

Note

When you use a RoleARN to configure a user mapping, you must configure the hologram::GrantAssumeRole access policy in RAM. Hologres uses this policy to check whether the current user has permission to create the user mapping for the specified RoleARN. A user with this permission can create a user mapping to the RoleARN for any user in Hologres. The following example shows the policy:

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

Parameter description

Parameter name	Description
user_name	The account to be granted permissions. This can be another RAM user, an internal Hologres account (BASIC account), or public for public access to all users. Use public with caution.
ext_db_name	The external database created in the Hologres instance using CREATE EXTERNAL DATABASE. It is used to load metadata from the external data source into Hologres. For more information, see CREATE EXTERNAL DATABASE.
access_id	The AccessKey ID of the current account. To get the AccessKey ID, go to the Resource Access Management (RAM) console.
access_key	The AccessKey secret of the current account.
dlf_access_id	The AccessKey ID of an Alibaba Cloud account or RAM user that has read and write permissions on DLF.
dlf_access_key	The AccessKey secret of an Alibaba Cloud account or RAM user that has read and write permissions on DLF.
oss_access_id	The AccessKey ID of an Alibaba Cloud account or RAM user that has read and write permissions on OSS.
oss_access_key	The AccessKey secret of an Alibaba Cloud account or RAM user that has read and write permissions on OSS.
rolearn	The Alibaba Cloud Resource Name (ARN) of the role. The ARN is the Global Resource Descriptor for the role and is used to specify a role. The format is `acs:ram::<account-id>:role/<role-name>`. The `<role-name>` part is converted to lowercase. For information about how to view the ARN of a role, see View a RAM role.

Query configured user mappings for an external database

SELECT
    datname,
    usename,
    eumoptions
FROM
    pg_holo_external_user_mappings;

Configuration for a foreign server

MaxCompute data source

Syntax

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

Parameter description

Parameter name	Description
user_name	The account to be granted permissions. This can be another RAM user, an internal Hologres account (BASIC account), or public for public access to all users. Use public with caution.
access_id	The AccessKey ID of the current account. To get the AccessKey ID, go to the Resource Access Management (RAM) console.
access_key	The AccessKey secret of the current account.

DLF data source

Syntax

CREATE USER MAPPING FOR "<user_name>"
SERVER <server_name>
OPTIONS
(
    -- For DLF 1.0
    [dlf_access_id 'LTxxxxxxxxxx',
    dlf_access_key 'y8xxxxxxxxxxxxx',
    oss_access_id 'LTxxxxxxxxxx',
    oss_access_key 'y8xxxxxxxxxxxxx'] 
    -- For DLF 2.5
    [dlf_access_id 'LTxxxxxxxxxx',
    dlf_access_key 'y8xxxxxxxxxxxxx'] | 
    [rolearn 'RoleARN']
);

Parameter description

Parameter name	Description
user_name	The account to be granted permissions. This can be another RAM user, an internal Hologres account (BASIC account), or public for public access to all users. Use public with caution.
server_name	The name of the foreign server. This is the custom name you set in the previous step. For a MaxCompute data source, the server is the fixed built-in server odps_server. For more information, see Create a foreign server.
access_id	The AccessKey ID of the current account. To get the AccessKey ID, go to the Resource Access Management (RAM) console.
access_key	The AccessKey secret of the current account.
dlf_access_id	The AccessKey ID of an Alibaba Cloud account or RAM user that has read and write permissions on DLF.
dlf_access_key	The AccessKey secret of an Alibaba Cloud account or RAM user that has read and write permissions on DLF.
oss_access_id	The AccessKey ID of an Alibaba Cloud account or RAM user that has read and write permissions on OSS.
oss_access_key	The AccessKey secret of an Alibaba Cloud account or RAM user that has read and write permissions on OSS.
rolearn	The Alibaba Cloud Resource Name (ARN) of the role. The ARN is the Global Resource Descriptor for the role and is used to specify a role. The format is `acs:ram::<account-id>:role/<role-name>`. The `<role-name>` part is converted to lowercase. For information about how to view the ARN of a role, see View 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>'
);

Parameter description

Parameter Name	Description
user_name	The account to be granted permissions. This can be another RAM user, an internal Hologres account (BASIC account), or public for public access to all users. Use public with caution.
server_name	The server created in Hologres to perform cross-database queries. For more information, see Cross-database queries (Beta).
access_id	The AccessKey ID of the current account. To get the AccessKey ID, go to the Resource Access Management (RAM) console.
access_key	The AccessKey secret of the current account.

Query configured user mappings for 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;

Usage examples

External database (Recommended)

This example uses a DLF 2.5 data source. First, create an external database to associate with a Paimon catalog on DLF.

CREATE EXTERNAL DATABASE ext_db_dlf
WITH 
  metastore_type 'dlf-rest' 
  catalog_type 'paimon' 
  dlf_catalog 'paimon_catalog';

After the external database is created, only the instance Superuser and the DB Owner can access it. Other accounts cannot. You can then grant permissions to a RAM user by creating a RAM role in the Resource Access Management (RAM) console and running the CREATE USER MAPPING command in Hologres.
1. Create a role and configure policies in RAM.
  1. Log on to the RAM console and click Role > Create Role. Select Alibaba Cloud Service as the trusted entity type, select Service Role as the role type, enter a role name, set Select Trusted Service to Interactive Analytics, and click Complete.
  2. Go to the Permission Model page. Click Create Policy > Script Editor. Create separate policies for the permission checks required by Hologres and DLF. The policies are as follows.
    
    Permission check policy required by Hologres.
```
{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "hologram:GrantAssumeRole",
            "Resource": "acs:ram::xxx:role/testramrole"
        }
    ]
}
```
    Permission check policy required by DLF.
```
{
    "Version": "1",
    "Statement": [
        {
            "Action": "dlf:CheckPermissions",
            "Resource": "*",
            "Effect": "Allow"
        }
    ]
}
```
  3. Go to the Role page. Click Grant Permission. Add the two policies you created.
2. In Hologres, run the CREATE USER MAPPING command to grant the RAM user 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 run a query in the SQL editor.
```
SELECT * FROM ext_db_dlf.dlf_db.paimon_table;
```

Foreign server

This example uses a MaxCompute data source. It shows how to grant permissions to a Hologres BASIC account to access a MaxCompute foreign table.

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

Grant the BASIC account access permissions to odps_server.

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