CREATE USER MAPPING - Hologres - Alibaba Cloud Documentation Center

Starting from Hologres V3.0, you can configure RAM role mappings for accounts within an instance. This allows logon accounts to access external systems cross-account by assuming a RAM role. Currently, this method only supports accessing DLF 2.5 data sources and other Hologres instances using RAM roles. This topic describes how to use CREATE USER MAPPING and its limits.

Background information

User Mapping defines the mapping between Hologres logon users and Alibaba Cloud accounts. It enables current Hologres users (including BASIC users) to access external services cross-account using User Mapping, such as MaxCompute, DLF, OSS, and Hologres cross-database queries. For more information, see PostgreSQL CREATE USER MAPPING.

Limits

To grant permissions to regular users, you must have SuperUser permissions for the Hologres instance or Owner permissions for the Foreign Server. Users with Foreign Server Usage permissions can also create User Mappings for themselves.
MaxCompute data sources and DLF 1.0 do not support configuring user mappings using RAM roles.

Syntax Description

External Database Configuration

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 Name	Description
user_name	The account to be authorized, such as other RAM users, Hologres internal accounts (BASIC accounts), or public (for public access to all users; proceed with caution).
Access_id	The AccessKey ID of the current access account. Click Resource Access Management (RAM) console to get the AccessKey ID.
Access_key	The AccessKey secret of the current access account.

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.5
    [dlf_access_id 'LTxxxxxxxxxx',
    dlf_access_key 'y8xxxxxxxxxxxxx'] | 
    [rolearn 'RoleARN']
);

Note

When configuring User Mapping with a RoleARN, configure the hologram::GrantAssumeRole access policy in RAM. Hologres uses this policy to determine whether the current user has permissions to create a User Mapping for the corresponding RoleARN. Users with this permission can create a User Mapping for any user for the corresponding RoleARN in Hologres. Example:

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

Parameters

Parameter Name	Description
user_name	The account to be authorized, such as other RAM users, Hologres internal accounts (BASIC accounts), or public (for public access to all users; proceed with caution).
ext_db_name	The external database created in the Hologres instance using CREATE EXTERNAL DATABASE. It loads metadata from external data sources to Hologres. For more information, see CREATE EXTERNAL DATABASE.
access_id	The AccessKey ID of the current access account. Click Resource Access Management (RAM) console to get the AccessKey ID.
access_key	The AccessKey secret of the current access account.
dlf_access_id	The AccessKey ID of the Alibaba Cloud account or RAM account with read and write permissions for DLF.
dlf_access_key	The AccessKey secret of the Alibaba Cloud account or RAM account with read and write permissions for DLF.
oss_access_id	The AccessKey ID of the Alibaba Cloud account or RAM account with read and write permissions for OSS.
oss_access_key	The AccessKey secret of the Alibaba Cloud account or RAM account with read and write permissions for OSS.
rolearn	A Role ARN is the Global Resource Descriptor (GRD) for a role. It specifies a particular role. The format is `acs:ram::<account-id>:role/<role-name>`. The `<role-name>` part converts the role name to lowercase. For more information about how to view a Role ARN, see View RAM roles.

Query User Mappings Configured for External Databases

SELECT
    datname,
    usename,
    eumoptions
FROM
    pg_holo_external_user_mappings;

Foreign Server Configuration

MaxCompute Data Source

Syntax

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

Parameters

Parameter Name	Description
user_name	The account to be authorized, such as other RAM users, Hologres internal accounts (BASIC accounts), or public (for public access to all users; proceed with caution).
access_id	The AccessKey ID of the current access account. Click Resource Access Management (RAM) console to get the AccessKey ID.
access_key	The AccessKey secret of the current access account.

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.5
    [dlf_access_id 'LTxxxxxxxxxx',
    dlf_access_key 'y8xxxxxxxxxxxxx'] | 
    [rolearn 'RoleARN']
);

Parameters

Parameter name	Description
user_name	The account to be authorized, such as other RAM users, Hologres internal accounts (BASIC accounts), or public (for public access to all users; proceed with caution).
server_name	The name of the Foreign Server. This is the name you set in the previous step. For MaxCompute data sources, the fixed built-in server is `odps_server`. For more information, see Create a Foreign Server.
access_id	The AccessKey ID of the current access account. Click Resource Access Management (RAM) console to get the AccessKey ID.
access_key	The AccessKey secret of the current access account.
dlf_access_id	The AccessKey ID of the Alibaba Cloud account or RAM account with read and write permissions for DLF.
dlf_access_key	The AccessKey secret of the Alibaba Cloud account or RAM account with read and write permissions for DLF.
oss_access_id	The AccessKey ID of the Alibaba Cloud account or RAM account with read and write permissions for OSS.
oss_access_key	The AccessKey secret of the Alibaba Cloud account or RAM account with read and write permissions for OSS.
rolearn	A Role ARN is the Global Resource Descriptor (GRD) for a role. It specifies a particular role. The format is `acs:ram::<account-id>:role/<role-name>`. The `<role-name>` part converts the role name to lowercase. For more information about how to view a Role ARN, see View RAM roles.

Hologres Data Source

Syntax

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

Parameters

Parameter Name	Description
user_name	The account to be authorized, such as other RAM users, Hologres internal accounts (BASIC accounts), or public (for public access to all users; proceed with caution).
server_name	The server created by Hologres for cross-database queries. For more information, see Cross-database Query (Beta).
access_id	The AccessKey ID of the current access account. Click Resource Access Management (RAM) console to get the AccessKey ID.
access_key	The AccessKey secret of the current access account.

Query User Mappings Configured for Internal Databases

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)

For example, with a DLF 2.5 data source, first create an External Database and associate it 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 creation, only the instance Superuser and DB Owner can access this external database. Other accounts cannot access it. Next, grant permissions to a RAM user by creating a RAM role in the Resource Access Management (RAM) console and then creating a CREATE USER MAPPING in Hologres.
1. Create a new role in RAM and configure policies.
  1. Log on to the Resource Access Management (RAM) console. Click Role > Create Role. For Trusted Entity Type, select Alibaba Cloud Service. For Role Type, select Service Role. Enter a role name. For Select Trusted Service, select Interactive Analytics. Click Complete.
  2. Go to the Permission Model page and click Create Policy > Script Editor. Create policies for Hologres and DLF permissions. The policy content is as follows.
    
    Hologres permission check policy.
```
{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "hologram:GrantAssumeRole",
            "Resource": "acs:ram::xxx:role/testramrole"
        }
    ]
}
```
    DLF permission check policy.
```
{
    "Version": "1",
    "Statement": [
        {
            "Action": "dlf:CheckPermissions",
            "Resource": "*",
            "Effect": "Allow"
        }
    ]
}
```
  3. Go to the Role page. Click Grant Permission to add the two access policies.
2. In Hologres, grant permissions for the ext_db_dlf database to a RAM user using CREATE USER MAPPING.
```
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. Then, query using the SQL editor.
```
SELECT * FROM ext_db_dlf.dlf_db.paimon_table;
```

Foreign Server

For example, with a MaxCompute data source, grant a Hologres BASIC account permissions to access MaxCompute foreign tables.

You can create a BASIC account in Hologres.
```
CREATE USER basicuser;
```

Grant the BASIC account access permissions for odps_server.

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