Hologres:Create user mapping

MaxCompute

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

DLF

CREATE USER MAPPING FOR "<user_name>"
EXTERNAL DATABASE <ext_db_name>
OPTIONS
(
    -- DLF 1.0: AccessKey credentials required
    dlf_access_id '<AccessKey_ID>',
    dlf_access_key '<AccessKey_secret>',
    oss_access_id '<AccessKey_ID>',
    oss_access_key '<AccessKey_secret>'

    -- DLF 2.5 option 1: AccessKey credentials
    -- dlf_access_id '<AccessKey_ID>',
    -- dlf_access_key '<AccessKey_secret>'

    -- DLF 2.5 option 2: RAM role (Hologres V3.0+)
    -- rolearn 'acs:ram::<account-id>:role/<role-name>'
);

When using rolearn, configure the hologram:GrantAssumeRole permission in RAM before creating the user mapping. See Configure RAM permissions for rolearn.

Parameters

Query existing mappings

SELECT
    datname,
    usename,
    eumoptions
FROM
    pg_holo_external_user_mappings;

MaxCompute

CREATE USER MAPPING FOR "<user_name>"
SERVER odps_server  -- Built-in server for MaxCompute
OPTIONS
(
    access_id '<AccessKey_ID>',
    access_key '<AccessKey_secret>'
);

DLF

CREATE USER MAPPING FOR "<user_name>"
SERVER <server_name>
OPTIONS
(
    -- DLF 1.0
    dlf_access_id '<AccessKey_ID>',
    dlf_access_key '<AccessKey_secret>',
    oss_access_id '<AccessKey_ID>',
    oss_access_key '<AccessKey_secret>'

    -- DLF 2.5 option 1: AccessKey credentials
    -- dlf_access_id '<AccessKey_ID>',
    -- dlf_access_key '<AccessKey_secret>'

    -- DLF 2.5 option 2: RAM role (Hologres V3.0+)
    -- rolearn 'acs:ram::<account-id>:role/<role-name>'
);

Hologres (cross-database queries)

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

Parameters

Query existing mappings

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;

Grant a RAM user access to DLF 2.5 via a RAM role (external database)

This example grants a RAM user access to a Paimon table in DLF 2.5 using a RAM role. All SQL steps are shown in order; the RAM console steps are described between them.

Step 1: Create the external database.

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 the database owner can access ext_db_dlf. Other accounts need a user mapping.

Step 2: Create a RAM role with the required permissions.

In the Resource Access Management (RAM) console:

1. Click Role > Create Role. Set Trusted Entity Type to Alibaba Cloud Service, Role Type to Service Role, and Select Trusted Service to Interactive Analytics. Enter a role name and click Complete.

2. On the Permission Model page, click Create Policy > Script Editor. Create two policies: 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. On the Role page, click Grant Permission and attach both policies.

Step 3: Create the user mapping in Hologres.

CREATE USER MAPPING FOR "p4_203535926287167253"
EXTERNAL DATABASE ext_db_dlf
OPTIONS
(
    rolearn 'acs:ram::xxx:role/testramrole'
);

Step 4: Verify access.

Log in to Hologres as the RAM user and run:

SELECT * FROM ext_db_dlf.dlf_db.paimon_table;

Grant a BASIC account access to MaxCompute (foreign server)

-- Step 1: Create the BASIC account
CREATE USER basicuser;

-- Step 2: Grant the account access to odps_server
CREATE USER MAPPING
FOR "basicuser"
SERVER odps_server
OPTIONS
(
    access_id 'LTxxxxxxxxxx',
    access_key 'y8xxxxxxxxxxxxx'
);