Hologres:Simple permission model functions - Hologres

The simple permission model (SPM) is managed through a set of stored procedures. This topic describes each function's syntax, parameters, required permissions, and behavior.

Function overview

Function	Description
`spm_enable`	Enables SPM for the current database
`spm_migrate`	Migrates existing objects (tables, views, foreign tables) to SPM ownership
`spm_create_user`	Creates a user with logon-only permissions
`spm_grant`	Adds a user to a user group
`spm_revoke`	Removes a user from a user group
`spm_disable`	Disables SPM for the current database
`spm_cleanup`	Removes SPM-reserved user groups from a database

spm_enable

Enables SPM for the current database. On success, the system creates four user groups: {db}_admin, {db}_developer, {db}_writer, and {db}_viewer.

Required permissions: Superuser only.

Syntax

CALL spm_enable();

What happens after enabling

Change	Details
PUBLIC access revoked	All permissions on the database and its schemas are removed from PUBLIC. Users without explicit group membership cannot connect.
Group connectivity	All four groups (`{db}_admin`, `{db}_developer`, `{db}_writer`, `{db}_viewer`) gain CONNECT permission.
Ownership	`{db}_admin` becomes owner of the database and all schemas. Objects created by `{db}_admin` or `{db}_developer` members are owned by `{db}_developer`.
Schema permissions	`{db}_developer`, `{db}_writer`, and `{db}_viewer` receive USAGE on all schemas. `{db}_developer` also receives CREATE on all schemas.
Object permissions	`{db}_writer` receives read-write permissions on all objects. `{db}_viewer` receives read-only permissions.

spm_migrate

Migrates existing database objects to SPM ownership in batches. Call this function after spm_enable to bring pre-existing tables, views, and foreign tables under SPM management.

Required permissions: Superuser or {db}_admin member.

Syntax

CALL spm_migrate( [ batch_size ] );

Parameters

Parameter	Description	Value range
`batch_size`	Number of objects to migrate per call. Defaults to the current `max_locks_per_transaction` value when set to `0`.	`[0, max_locks_per_transaction]`. Values outside this range are invalid. Run `show max_locks_per_transaction;` to check the current limit.

Return values

Return value	Meaning
`DONE BUT NOT COMPLETED`	Some objects remain. Call `spm_migrate()` again.
`COMPLETED`	All objects are migrated.

If your instance has thousands of objects, call spm_migrate() multiple times until COMPLETED is returned. Contact the Hologres support group to request a higher max_locks_per_transaction value if needed. For details, see Get more online support.

Examples

-- Migrate up to max_locks_per_transaction objects per call
CALL spm_migrate();

-- Migrate 128 objects per call
CALL spm_migrate(128);

spm_create_user

Creates a Hologres user with logon-only permissions. After creation, add the user to a group with spm_grant to grant development permissions.

Required permissions: Superuser or {db}_admin member.

Syntax

CALL spm_create_user( user_name [, role_name] );

Parameters

Parameter	Description
`user_name`	The account to create. Accepts an Alibaba Cloud account (e.g., `13432193xxxx` or `example@aliyun.com`) or a RAM user account (e.g., `RAM$mainaccount:subuser` or `p4_202338382183xxx`).
`role_name`	(Optional) The user group to add the user to immediately. Valid values: `{db}_admin`, `{db}_developer`, `{db}_writer`, `{db}_viewer`.

Examples

-- Create a user with logon-only permissions
CALL spm_create_user('my_t***@aliyun.com');

-- Create a user and immediately add them to the developer group
CALL spm_create_user('RAM$my_test:mysubuser', 'mydb_developer');

-- Create a user using an Alibaba Cloud account ID
CALL spm_create_user('13532313103042xxx');

-- Create a user and immediately add them to the admin group
CALL spm_create_user('p4_23319103042xxx', 'mydb_admin');

spm_grant

Adds a user to a user group, granting the permissions associated with that group.

Required permissions: Superuser or {db}_admin member. SPM must be enabled.

Syntax

CALL spm_grant( role_name, user_name );

Parameters

Parameter	Description
`role_name`	The target user group: `{db}_admin`, `{db}_developer`, `{db}_writer`, or `{db}_viewer`. For a description of each group's permissions, see Simple permission model (SPM).
`user_name`	The user to add. Accepts an Alibaba Cloud account or RAM user account in the same formats as `spm_create_user`.

Examples

-- Grant developer permissions on mydb to a RAM user
CALL spm_grant('mydb_developer', 'p4_202338382183xxx');

-- Grant admin permissions on mydb to a RAM user
CALL spm_grant('mydb_admin', 'RAM$my_test:xxx');

-- Error: connect to otherdb first, then run spm_grant
CALL spm_grant('otherdb_admin', '13532313103042xxx');

To add a user to a group in another database, connect to that database first, then call spm_grant.

spm_revoke

Removes a user from a user group.

Required permissions: Superuser or {db}_admin member. SPM must be enabled.

Syntax

CALL spm_revoke( role_name, user_name );

Parameters

Parameter	Description
`role_name`	The user group to remove the user from: `{db}_admin`, `{db}_developer`, `{db}_writer`, or `{db}_viewer`.
`user_name`	The user to remove. Accepts an Alibaba Cloud account or RAM user account.

Examples

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

-- Remove a RAM user from the admin group of mydb
CALL spm_revoke('mydb_admin', 'RAM$my_test:xxx');

-- Error: connect to otherdb first, then run spm_revoke
CALL spm_revoke('otherdb_admin', '13532313103042xxx');

To remove a user from a group in another database, connect to that database first, then call spm_revoke.

spm_disable

Disables SPM for the current database, reverting to the standard PostgreSQL permission model.

Required permissions: Superuser only.

Syntax

CALL spm_disable();

What happens after disabling

Change	Details
Groups retained	`{db}_admin`, `{db}_developer`, `{db}_writer`, and `{db}_viewer` still exist and can be used with standard `GRANT` statements in expert mode.
Object ownership	All database objects remain owned by `{db}_developer`.
PUBLIC access restored	CONNECT and TEMPORARY permissions on the database are granted to PUBLIC.
Public schema access restored	USAGE and CREATE permissions on the public schema are granted to PUBLIC.
Function and language access restored	EXECUTE permission on functions and procedures in the public schema, USAGE on user-defined languages, and USAGE on user-defined types are all granted to PUBLIC.

Example

CALL spm_disable();

spm_cleanup

Removes the SPM-reserved user groups ({db}_admin, {db}_developer, {db}_writer, {db}_viewer) from a database.

Required permissions: Superuser only.

Syntax

CALL spm_cleanup( db_name [, batch_size ] );

Parameters

Parameter	Description	Value range
`db_name`	The database from which to remove the user groups. Enclose names containing special characters or uppercase letters in double quotation marks (e.g., `"MYDB"`).	None
`batch_size`	Number of objects to transfer ownership of per call. Defaults to the current `max_locks_per_transaction` value when set to `0`.	`[0, max_locks_per_transaction]`. Values outside this range are invalid.

Return values

Return value	Meaning
`DONE BUT NOT COMPLETED`	Cleanup is incomplete; the reserved groups are not yet deleted. Call `spm_cleanup()` again.
`COMPLETED`	Cleanup is complete; all reserved groups are deleted.

If your instance has many objects to migrate, call spm_cleanup() multiple times until COMPLETED is returned. Join the Hologres communication group to request an increase in the max_locks_per_transaction parameter value if needed. For details, see Get more online support.

Examples

-- Transfer ownership in batches of max_locks_per_transaction objects
CALL spm_cleanup('mydb');

-- Transfer ownership in batches of 128 objects
CALL spm_cleanup('mydb', 128);

Scenario 1: Delete the database first, then clean up

DROP DATABASE mydb;
CALL spm_cleanup('mydb');  -- Completes in one call, no retry needed

Scenario 2: Clean up while the database still exists

Connect to mydb first, then call spm_cleanup. Calling it from another database returns an error:

ERROR: Permission Denied. execute in database otherdb, or drop database before call spm_cleanup.

Function call permissions

Function	Description	Superuser	db_admin	db_developer / db_writer / db_viewer
`spm_enable`	Enables SPM	Yes	No	No
`spm_disable`	Disables SPM	Yes	No	No
`spm_grant`	Adds a user to a group	Yes	Yes	No
`spm_revoke`	Removes a user from a group	Yes	Yes	No
`spm_migrate`	Migrates objects to SPM	Yes	Yes	No
`spm_cleanup`	Removes reserved user groups	Yes	No	No
`spm_create_user`	Creates a user with logon-only permissions	Yes	Yes	No

Restricted commands

After enabling SPM, the following PostgreSQL permission commands are restricted or unnecessary.

Command	Restriction
`ALTER TABLE owner TO xx`	Object ownership is managed automatically by SPM. The owner is always `{db}_developer` and cannot be changed manually.
`GRANT`	Not needed after adding a user with `spm_grant`.
`REVOKE`	Not needed after removing a user with `spm_revoke`.
`ALTER DEFAULT PRIVILEGES`	Not needed. SPM grants permissions based on group membership, not object creation time, so future tables are covered automatically.
`CREATE ROLE / DROP ROLE / ALTER ROLE / ALTER ROLE SET DEFAULT USER GROUP`	The four default user groups are system-managed. No user, including superusers, can create or modify them.
`RENAME TO/FROM default user group`	The reserved group names (`_admin`, `_developer`, `_writer`, `_viewer`) cannot be renamed.