Hologres:Using the schema-level permission model

Prerequisites

Before you begin, ensure that you have:

• Superuser access to the Hologres instance

• A development tool connected to the instance (for example, HoloWeb or psql)

Enable SLPM for a database

1. Install the SLPM extension. Run this once per database.

   create extension slpm;

2. Enable SLPM. Make sure no SQL statements are running on the database when you run this command.

   call slpm_enable ();

3. (Optional) Migrate from the standard PostgreSQL permission model. If the database already has tables, views, or foreign tables managed under the standard PostgreSQL model, migrate existing object ownership to SLPM with the following command.

   1. Log on to the Hologres console. In the left navigation pane, click Go to HoloWeb.

   2. Click Security Center. On the DB Authorization page, check the current permission model.

   slpm_migrate processes up to 64 users per run (adjustable). If the database has more users, run the function multiple times until all permissions are migrated. For parameter details, see slpm_migrate.

   -- Transfer ownership of existing objects to the developer group for SLPM management.
   call slpm_migrate ();

   To check which permission model is active before migrating:

Step 1: Create the user

Skip this step if the user already exists in the instance.

-- Create a user.
call slpm_create_user('<account>');

-- Create a user and add them to a group in one step.
call slpm_create_user('<account>', '{dbname}.[admin|{schemaname}.developer|{schemaname}.writer|{schemaname}.viewer]');

Replace <account> with one of the following formats:

To use a RAM user UID, add the p4_ prefix. Get the UID from the Users page of the RAM console. For more on account formats, see Account system.

Step 2: Add the user to a group

call slpm_grant('{dbname}.[admin|{schemaname}.developer|{schemaname}.writer|{schemaname}.viewer]', '<account>');

If you already added the user to a group during creation, skip this step.

Examples:

The following example adds an Alibaba Cloud account to the admin group of mydb.

call slpm_grant('mydb.admin', '197006222995xxx');
call slpm_grant('mydb.admin', 'ALIYUN$xxx');

The following example adds users to the developer group of the public schema in mydb.

call slpm_grant('mydb.public.developer', '197006222995xxx');
call slpm_grant('mydb.public.developer', 'RAM$mainaccount:subuser');

The following example adds a user to the viewer group of the lisa schema in MYDB (case-sensitive database name).

call slpm_grant('"MYDB.lisa.viewer"', '197006222995xxx');
call slpm_grant('mydb.lisa.viewer', '"xxx@aliyun.com"');

Step 1: Disable the model

Only a superuser can disable SLPM.

call slpm_disable ();

After disabling:

• The four user groups ({db}.admin, {db}.{schemaname}.developer, {db}.{schemaname}.writer, {db}.{schemaname}.viewer) retain their permissions on existing objects. Permissions do not extend to new objects.

• PUBLIC is granted the following: USAGE and CREATE on the public schema; CONNECT and TEMPORARY on the database; EXECUTE on functions and procedures; USAGE on languages and data types (including domains).

• PUBLIC is not granted permissions on tables, views, materialized views, table columns, sequences, foreign data wrappers, foreign servers, or non-public schemas. Contact a superuser to grant those permissions individually.

Step 2: Clean up user groups (optional)

User groups are not deleted automatically when SLPM is disabled. To remove them, call slpm_cleanup.

Make sure no SQL statements are running on the database before calling slpm_cleanup. slpm_cleanup transfers object ownership in batches of 64 (adjustable). Run it multiple times if needed, but avoid more than five runs. For details, see slpm_cleanup.

Scenario 1: Delete user groups but keep the database.

Run the following command in the target database as a superuser.

call slpm_cleanup('<dbname>');

Scenario 2: Delete user groups after the database has been dropped.

Run the following command in another database (such as postgres) as a superuser.

call slpm_cleanup('mydb');

When to use this feature

A common data warehouse pattern is to organize data into layered schemas—for example, operation data store (ODS), DWD, data warehouse service (DWS), and ADS—and then create summary views in an outer layer that join tables from multiple inner layers.

Consider the following example where a view in the ads schema joins tables from ods and dwd:

The view DDL:

CREATE VIEW ads.customer_total_order_price_view AS
SELECT
    c_name,
    sum(o_totalprice)
FROM
    ods.orders AS o
INNER JOIN dwd.customer AS c
ON o.o_custkey = c.c_custkey
GROUP BY
    1;

Permission requirements

In the example above, to create ads.customer_total_order_price_view as ads_dev_user:

• Grant ads_dev_user the developer permission on ads.

• Grant ads_dev_user the viewer permission on ods and dwd.

To allow ads_view_user to query the view, grant ads_view_user the viewer permission on ads.

Enable the cross-schema view feature

Run the following command as a superuser.

call slpm_enable_multi_schema_view();

Transfer view ownership

After the feature is enabled, the user who creates a view becomes its owner. Only the owner can modify or delete it. To transfer ownership—for example, before removing a user from the database—run the following command. The new owner must have developer on the view's schema and viewer or higher on all source schemas.

-- Syntax
call slpm_alter_view_owner('view_name', '<account>');

-- Example: transfer ownership of ads.customer_total_order_price_view to p4_xxxxx.
call slpm_alter_view_owner('ads.customer_total_order_price_view', 'p4_xxxxx');

Disable the cross-schema view feature

-- Disable cross-schema view support.
call slpm_disable_multi_schema_view();
-- Transfer all view ownership back to the developer group of each view's schema.
call slpm_migrate();

After running these commands, existing non-cross-schema views remain queryable and SLPM behaves as normal. Cross-schema views can no longer be queried.