SLPM Overview: Schema-Level Permission Model Explained - Hologres

Hologres provides eight stored functions for managing the schema-level permission model (SLPM). Use these functions to enable, configure, and clean up SLPM-based access control in your databases.

Function overview

Function	Purpose	Required permission
`slpm_enable`	Enable the SLPM for a database	Superuser
`slpm_migrate`	Migrate existing objects to the SLPM	—
`slpm_create_user`	Create a user in the SLPM	Superuser or `{db}.admin` member
`slpm_grant`	Add a user to a user group	Superuser or `{db}.admin` member
`slpm_revoke`	Remove a user from a user group	Superuser or `{db}.admin` member
`slpm_disable`	Disable the SLPM for a database	Superuser
`slpm_cleanup`	Delete the user groups retained after disabling	Superuser
`slpm_rename_schema`	Rename a schema and its associated user groups	Superuser or `{db}.admin` member

Typical workflows

Initial setup

Call slpm_enable to enable the SLPM. The system creates four user groups automatically.
Call slpm_migrate to bring existing objects (tables, views, foreign tables) under SLPM ownership.
Call slpm_create_user or slpm_grant to assign users to groups.

Ongoing user management

Use slpm_grant to add a user to a group.
Use slpm_revoke to remove a user from a group.

Database decommission

Call slpm_disable to disable the SLPM. User groups are retained.
Call slpm_cleanup to delete the retained user groups.

slpm_enable

Enables the SLPM for the current database. After enabling, the system automatically creates the following user groups: {db}.admin, {db}.{schema}.developer, {db}.{schema}.writer, and {db}.{schema}.viewer.

Prerequisites

Requirement	Details
Permission	Must be a superuser of the Hologres instance

Syntax

CALL slpm_enable ();

Permission changes after enabling

Subject	Before enabling	After enabling
PUBLIC group	Has permissions on the database and all schemas	All permissions revoked — unauthorized users cannot connect
`{db}.admin`	—	Becomes owner of the database and all schemas; can connect to the database
`{db}.{schema}.developer`, `writer`, `viewer`	—	Gain USAGE permission on the schema; can connect to the database
`{db}.{schema}.developer`	—	Gains CREATE permission on the schema
Objects created within a schema	—	`{db}.{schema}.developer` = owner; `writer` = read/write; `viewer` = read-only
Schema-less objects (foreign servers, foreign data wrappers, languages)	—	`{db}.admin` = owner; `developer`, `writer`, `viewer` gain USAGE permission

slpm_migrate

Migrates existing objects — tables, views, and foreign tables — to the SLPM. If your database has many objects, call this function multiple times until all objects are migrated.

Syntax

CALL slpm_migrate ( [ batch_size ] );

Parameters

Parameter	Description	Value range
`batch_size`	Maximum number of objects to migrate per call. Set to `0` to use the value of the `max_locks_per_transaction` parameter.	`[1, 64]`. Values outside this range are invalid.

For databases with thousands or tens of thousands of objects, contact technical support to increase max_locks_per_transaction before migrating.

Return messages

Message	Meaning
`DONE BUT NOT COMPLETED`	Some objects remain. Call `slpm_migrate` again.
`COMPLETED`	All objects are migrated.

Examples

-- Migrate objects using the limit set by max_locks_per_transaction
CALL slpm_migrate ();

-- Migrate up to 64 objects per call
CALL slpm_migrate (64);

slpm_create_user

Creates a user in the SLPM. After creation, the user has only the logon permission. To allow data analytics, add the user to a group using slpm_grant or by specifying role_name directly in this call.

Prerequisites

Requirement	Details
Permission	Superuser, or a member of the `{db}.admin` group

Syntax

CALL slpm_create_user ( user_name [, role_name] );

Parameters

Parameter	Description
`user_name`	The user to create. Accepted formats: Alibaba Cloud account ID (e.g., `13432193xxxx`), account display name (e.g., `x*@aliyun.com`), RAM user UID with `p4_` prefix (e.g., `p4_202338382183xxx`), or RAM user display name (e.g., `RAM$mainaccount:subuser`).
`role_name`	(Optional) The group to add the user to: `{db}.admin`, `{db}.{schema}.developer`, `{db}.{schema}.writer`, or `{db}.{schema}.viewer`. For group permissions, see User groups and permissions in SLPM.

Examples

-- Create a user by Alibaba Cloud account display name (no group assigned)
CALL slpm_create_user ('my_t***@aliyun.com');

-- Create a RAM user and add them to the mydb.public.developer group
CALL slpm_create_user ('RAM$my_test:mysubuser', 'mydb.public.developer');

-- Create a user by Alibaba Cloud account ID
CALL slpm_create_user ('13532313103042xxx');

-- Create a RAM user by UID and add them to the mydb.admin group
CALL slpm_create_user ('p4_23319103042xxx', 'mydb.admin');

slpm_grant

Adds a user to a user group in the current database.

Prerequisites

Requirement	Details
Permission	Superuser, or a member of the `{db}.admin` group
SLPM status	Must be enabled
Scope	Can only add users to groups in the current database

Syntax

CALL slpm_grant ( role_name, user_name );

Parameters

Parameter	Description
`role_name`	The target group: `{db}.admin`, `{db}.{schema}.developer`, `{db}.{schema}.writer`, or `{db}.{schema}.viewer`. Must belong to the current database. For group permissions, see User groups and permissions in SLPM.
`user_name`	The user to add. Accepted formats: Alibaba Cloud account ID (e.g., `13432193xxxx`), account display name (e.g., `x*@aliyun.com`), RAM user UID with `p4_` prefix (e.g., `p4_202338382183xxx`), or RAM user display name (e.g., `RAM$mainaccount:subuser`).

Examples

-- Add a RAM user to the mydb.public.developer group
CALL slpm_grant ('mydb.public.developer', 'p4_202338382183xxx');

-- Add a RAM user to the mydb.admin group
CALL slpm_grant ('mydb.admin', 'RAM$my_test:xxx');

-- This fails: cross-database grants are not supported.
-- Connect to otherdb first, then call slpm_grant.
CALL slpm_grant ('otherdb.admin', '13532313103042xxx');

slpm_revoke

Removes a user from a user group in the current database.

Prerequisites

Requirement	Details
Permission	Superuser, or a member of the `{db}.admin` group
SLPM status	Must be enabled
Scope	Can only remove users from groups in the current database

Syntax

CALL slpm_revoke ( role_name, user_name );

Parameters

Parameter	Description
`role_name`	The group to remove the user from: `{db}.admin`, `{db}.{schema}.developer`, `{db}.{schema}.writer`, or `{db}.{schema}.viewer`. Must belong to the current database.
`user_name`	The user to remove. Accepted formats: Alibaba Cloud account ID (e.g., `13432193xxxx`), account display name (e.g., `x*@aliyun.com`), RAM user UID with `p4_` prefix (e.g., `p4_202338382183xxx`), or RAM user display name (e.g., `RAM$mainaccount:subuser`).

Examples

-- Remove a RAM user from the mydb.public.developer group
CALL slpm_revoke ('mydb.public.developer', 'p4_202338382183xxx');

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

-- This fails: cross-database revocations are not supported.
-- Connect to otherdb first, then call slpm_revoke.
CALL slpm_revoke ('otherdb.admin', '13532313103042xxx');

slpm_disable

Disables the SLPM for the current database. The four user groups are retained after disabling — use slpm_cleanup to delete them.

Prerequisites

Requirement	Details
Permission	Must be a superuser

Syntax

CALL slpm_disable ();

Permission changes after disabling

Subject	Permissions granted
`{db}.admin`, `{db}.{schema}.developer`, `{db}.{schema}.writer`, `{db}.{schema}.viewer`	Retained. Members of `{db}.{schema}.developer` remain owners of database objects.
PUBLIC group	CONNECT and TEMPORARY on the database; USAGE and CREATE on the public schema; EXECUTE on functions and procedures in the public schema; USAGE on user-defined languages and types

Example

CALL slpm_disable ();

slpm_cleanup

Deletes the {db}.admin, {db}.{schema}.developer, {db}.{schema}.writer, and {db}.{schema}.viewer user groups that are retained after slpm_disable. If your database has many objects, call this function multiple times until all groups are deleted.

Prerequisites

Requirement	Details
Permission	Must be a superuser
Scope	For an existing database: must be connected to the target database. For a deleted database: connect from any database.

Syntax

CALL slpm_cleanup ( db_name [, batch_size ] );

Parameters

Parameter	Description	Default	Value range
`db_name`	The database whose user groups to delete. Enclose in double quotation marks if the name contains special characters or uppercase letters, e.g., `"MYDB"`.	—	—
`batch_size`	Maximum number of objects to process per call. Set to `0` to use the value of the `max_locks_per_transaction` parameter.	`64`	`[0, 64]`. Values outside this range are invalid.

Return messages

Message	Meaning
`DONE BUT NOT COMPLETED`	Some objects remain; user groups are not yet deleted. Call `slpm_cleanup` again.
`COMPLETED`	All objects are processed and user groups are deleted.

Examples

-- Delete user groups for mydb, processing up to max_locks_per_transaction objects per call
CALL slpm_cleanup ('mydb');

-- Delete user groups for mydb, processing up to 64 objects per call
CALL slpm_cleanup ('mydb', 64);

Case 1: Delete the database first, then clean up its user groups

DROP DATABASE mydb;
-- After dropping the database, clean up its user groups from any database.
CALL slpm_cleanup ('mydb');

Case 2: Clean up user groups for an existing database

Connect to otherdb before calling slpm_cleanup. Calling it from a different database returns the following error:

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

slpm_rename_schema

Renames a schema and automatically renames its four associated user groups ({db}.{schema}.developer, {db}.{schema}.writer, {db}.{schema}.viewer, and the schema entry in {db}.admin). Always use this function instead of a plain ALTER SCHEMA RENAME — the latter does not update the user group names.

Prerequisites

Requirement	Details
Permission	Superuser, or a member of the `{db}.admin` group

Syntax

CALL slpm_rename_schema ( old_name, new_name );

Parameters

Parameter	Description
`old_name`	The current schema name. Enclose in double quotation marks if the name contains special characters or uppercase letters, e.g., `"MYSCHEMA"`.
`new_name`	The new schema name. Same quoting rules apply.

Example

CALL slpm_rename_schema ('oldschema', 'newschema');

What's next

User groups and permissions in SLPM — understand what each group can do
Obtain online support for Hologres — contact technical support for large-scale migrations