All Products
Search

This Product

    Hologres:Grant permissions using SPM

    Document Center

    Hologres:Grant permissions using SPM

    Last Updated:Mar 26, 2026

    The simple permission model (SPM) replaces manual object-level grants with four predefined user groups per database. Assign a user to a group and SPM handles all underlying permissions automatically — reducing configuration overhead and eliminating permission drift as objects are added.

    User groups

    Each database in a Hologres instance has four user groups. Choose the group that matches the access level your user needs:

    User group Who it's for Access level
    admin Database administrators Full control over the database, including managing other users and objects
    developer Engineers who build and maintain data pipelines Read and write access to all tables and schemas; can create and drop objects
    writer Users who need to insert or update data Read and write access to tables, but cannot create or drop objects
    viewer Analysts and read-only consumers Read-only access to all tables and schemas
    After you enable SPM, the developer group automatically has default permissions on all tables and table-like objects in all schemas of the database.

    Prerequisites

    Before you begin, make sure you have:

    • Superuser access to the Hologres instance (required for all SPM setup commands)

    • Access to a SQL client connected to the target Hologres instance

    Enable SPM and grant permissions

    The workflow for enabling SPM and granting user access is:

    1. Enable the SPM extension.

    2. Enable SPM for the target database.

    3. (If migrating from the standard PostgreSQL authorization model) Migrate existing objects.

    4. Create a user.

    5. Add the user to a user group.

    Step 1: Enable the SPM extension

    Before you enable SPM, run the following command to enable function invocation:

    CREATE EXTENSION spm;

    Step 2: Enable SPM for the target database

    Connect to the target database and run:

    CALL spm_enable();

    SPM is disabled by default. For details on this function, see spm_enable.

    Step 3: Migrate existing objects (if applicable)

    Skip this step if the database is newly created and has no objects.

    If the database currently uses the standard PostgreSQL authorization model and contains tables, views, or foreign tables, migrate those objects to SPM before proceeding. Without migration, existing table permissions are lost, which can break running workloads.

    Warning

    Make sure no SQL statements are running in the database before proceeding. Running this command while other statements are active may cause the operation to fail.

    CALL spm_migrate();

    This command changes the owner of all existing objects to the developer group. Because migration runs ALTER ... OWNER TO on each object, it is bounded by PostgreSQL's max_locks_per_transaction limit per run. Run spm_migrate() multiple times until all objects are migrated. For details, see spm_migrate.

    Step 4: Create a user

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

    Alibaba Cloud accounts and RAM users

    Use spm_create_user to create a user. Optionally add the user to a user group in the same call:

    -- Create a user only
CALL spm_create_user('<account-id-or-email-or-ram-user>');

-- Create a user and add to a group in one step
CALL spm_create_user('<account-id-or-email-or-ram-user>', '<dbname>_[admin|developer|writer|viewer]');

    Replace <dbname> with the name of the target database.

    Example — add the RAM user xxx.onaliyun.com to the developer group of testdb:

    CALL spm_create_user('xxx.onaliyun.com', 'testdb_developer');

    Custom users

    CREATE USER "BASIC$<user_name>" WITH PASSWORD '<password>';
    For RAM users, add the p4_ prefix to the account UID when calling spm_create_user. For example: p4_564306222995xxx.
    Custom usernames cannot end with admin, developer, writer, viewer, or all_users.

    Step 5: Add the user to a user group

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

    Run spm_grant to add a user to a user group. After this, the user can connect to the database using a development tool and operate within the group's permitted scope.

    CALL spm_grant('<dbname>_[admin|developer|writer|viewer]', '<account-id-or-email-or-ram-user>');

    For details on this function, see spm_grant. For user group details, see User groups.

    Examples

    All examples below use mydb as the database name. Replace it with your actual database name.

    -- Add a RAM user to the admin group
CALL spm_grant('mydb_admin', 'p4_564306222995xxx');

-- Add an Alibaba Cloud account to the admin group
CALL spm_grant('mydb_admin', '197006222995xxx');

-- Add an Alibaba Cloud account (email format) to the admin group
CALL spm_grant('mydb_admin', 'ALIYUN$xxx');

-- Add a RAM user to the developer group
CALL spm_grant('mydb_developer', 'p4_564306222995xxx');

-- Add an Alibaba Cloud account to the developer group
CALL spm_grant('mydb_developer', '197006222995xxx');

-- Add a RAM sub-user to the developer group
CALL spm_grant('mydb_developer', 'RAM$mainaccount:subuser');

-- Add a RAM user to the viewer group of a case-sensitive database name "MYDB"
CALL spm_grant('"MYDB_viewer"', 'p4_564306222995xxx');

-- Add an Alibaba Cloud account to the viewer group of a case-sensitive database name "MYDB"
CALL spm_grant('"MYDB_viewer"', '197006222995xxx');

-- Add an account (email format) to the viewer group
CALL spm_grant('mydb_viewer', '"xxx@aliyun.com"');

    Revoke permissions

    To remove a user from a user group, run spm_revoke. This revokes the user's access within that group but does not delete the user from the instance.

    CALL spm_revoke('<dbname>_[admin|developer|writer|viewer]', '<account-id-or-email-or-ram-user>');

    For details, see spm_revoke.

    Examples

    -- Remove a RAM user from the admin group
CALL spm_revoke('dbname_admin', 'p4_564306222995xxx');

-- Remove an Alibaba Cloud account from the admin group
CALL spm_revoke('dbname_admin', '197006222995xxx');

-- Remove an account (email format) from the admin group
CALL spm_revoke('dbname_admin', 'xxx@aliyun.com');

-- Remove a RAM sub-user from the developer group
CALL spm_revoke('mydb_developer', 'RAM$mainaccount:subuser');

-- Remove a RAM user from the developer group
CALL spm_revoke('mydb_developer', 'p4_564306222995xxx');

-- Remove a RAM user from the viewer group of a case-sensitive database name "MYDB"
CALL spm_revoke('"MYDB_viewer"', 'p4_564306222995xxx');

    Delete a user

    Important

    Deleting a user removes them from the instance entirely and revokes all their permissions. This action cannot be undone — proceed with caution.

    DROP ROLE "<account-id-or-email-or-ram-user>";

    Disable SPM

    Important

    Only a Superuser can disable SPM.

    Step 1: Disable SPM

    Run the following in the target database:

    CALL spm_disable();

    After disabling, the four user groups (admin, developer, writer, viewer) are not deleted automatically. For details on what happens to permissions after disabling, see SPM functions.

    Step 2: Clean up user groups (optional)

    After disabling SPM, you can clean up user groups with spm_cleanup if needed. Keeping the user groups is fine — leave them in place if you plan to re-enable SPM later.

    Warning

    Make sure no SQL statements are running in the database before running spm_cleanup. Running it while other statements are active may cause the operation to fail.

    Scenario 1: Delete user groups but keep the database

    Run the following as a Superuser in the target database:

    CALL spm_cleanup('<dbname>');

    Because cleanup runs ALTER ... OWNER TO on business tables, it is bounded by the max_locks_per_transaction limit per run. Run spm_cleanup multiple times until all objects are migrated and the four user groups are deleted. For details, see spm_cleanup.

    Scenario 2: Database already deleted, user groups remain

    If the original database was deleted before its user groups were cleaned up, run the following from another database (for example, postgres) as a Superuser:

    CALL spm_cleanup('mydb');

    Permissions after disabling SPM

    After SPM is disabled, the following permissions apply:

    Public role permissions

    Permission Scope
    USAGE, CREATE public schema
    CONNECT, TEMPORARY Database
    EXECUTE Functions and procedures
    USAGE Language, data types (including domains)
    No permissions Tables, views, materialized views, table columns, sequences, foreign data wrappers, foreign servers, schemas other than public

    User group permissions

    All four groups (admin, developer, writer, viewer) retain their permissions on existing objects. These permissions do not extend to new database objects created after SPM is disabled.

    Re-enable SPM

    If you previously disabled SPM and switched back to the standard PostgreSQL authorization model, run the following to re-enable it:

    Warning

    Make sure no SQL statements are running in the database before proceeding.

    CALL spm_enable('t');   -- Re-enable SPM for the current database
CALL spm_migrate();     -- Transfer ownership of existing objects to the developer group

    Run spm_migrate() multiple times if needed, until all objects are migrated. Migration is bounded by the max_locks_per_transaction limit per run.