This topic describes the account and permission management functions supported by Hologres.
Type | Function | Description |
Account management functions | Converts the name of an Alibaba Cloud account that is specified by an email address or the name of a RAM user into the ID of the Alibaba Cloud account or RAM user. | |
Converts the ID of an Alibaba Cloud account into the name of the Alibaba Cloud account that is specified by an email address, or converts the ID of a RAM user into the name of the RAM user. | ||
Authorization function | Applies the permissions of users on a source table to a destination table in the standard PostgreSQL authorization model. This way, permissions on the tables are identical. | |
Permission query functions | Checks whether the current user or a specified user has specific permissions on all columns in a specified table. | |
Checks whether the current user has specific permissions on a specified column. | ||
Checks whether the current user or a specified user has specific permissions on a specified database. | ||
Checks whether the current user or a specified user has specific permissions on a foreign data wrapper. | ||
Checks whether the current user or a specified user has specific permissions on a specified function. | ||
Checks whether the current user or a specified user has specific permissions on a specified language. | ||
Checks whether the current user or a specified user has specific permissions on a specified schema. | ||
Checks whether the current user or a specified user has specific permissions on a specified foreign server. | ||
Checks whether the current user or a specified user has specific permissions on a specified table. | ||
Checks whether the current user or a specified user has specific permissions on a specified tablespace. | ||
Checks whether the current user or a specified user has specific permissions on a specified type of object, such as tables, views, or sequences. |
Account management functions
USER_DISPLAY_NAME
Description: Converts the ID of an Alibaba Cloud account into the name of the Alibaba Cloud account that is specified by an email address, or converts the ID of a RAM user into the name of the RAM user.
SELECT USER_DISPLAY_NAME (user_name);Parameters
user_name: required. The ID of the Alibaba Cloud account or RAM user. In most cases, the value is a numeric identifier for an Alibaba Cloud account such as
13532241323xxx, or a string for a RAM user such asp4_23402030200xxx.Return value
The name of the Alibaba Cloud account that is specified by an email address or the name of the RAM user is returned. Example for an Alibaba Cloud account:
xx@aliyun.com. Example for a RAM user:RAM$mainaccount:subuserorRAM$public.Example
-- Convert the ID of an Alibaba Cloud account or RAM user into the name of the Alibaba Cloud account that is specified by an email address or the name of the RAM user. SELECT USER_DISPLAY_NAME ('13532241323xxx');
HG_DISPLAY_NAME_TO_ID
Description: Converts the name of an Alibaba Cloud account that is specified by an email address or the name of a RAM user into the ID of the Alibaba Cloud account or RAM user.
SELECT HG_DISPLAY_NAME_TO_ID (aliyun_id);Parameters
aliyun_id: required. The Alibaba Cloud account name or RAM user name. In most cases, the value is an email address for an Alibaba Cloud account such as
xx@aliyun.com, or a string for a RAM user such asRAM$mainaccount:subuserorRAM$public.Return value
The ID of the Alibaba Cloud account or RAM user is returned. In most cases, a numeric identifier is returned. Example:
13532241323xxxorp4_23402030200xxx.Example
SELECT HG_DISPLAY_NAME_TO_ID ('RAM$main:subuser');
Authorization functions
APPLY_PRIVILEGES
Description: Applies the permissions of users on a source table to a destination table in the standard PostgreSQL authorization model. This way, permissions on the tables are identical.
-- Apply permissions on a source table to a destination table without revoking existing permissions on the destination table. CALL APPLY_PRIVILEGES('<old_table>','<new_table>', false); -- Apply permissions on a source table to a destination table and revoke existing permissions on the destination table. CALL APPLY_PRIVILEGES('<old_table>','<new_table>');NoteAfter you use the
CREATE TABLE LIKEorINSERT OVERWRITEstatement or perform automatic partitioning to create a table in the standard PostgreSQL authorization model, you must grant permissions on the table to users. TheAPPLY_PRIVILEGESfunction can be used to apply all permissions on a source table to a destination table to simplify permission granting operations.Limits
Only Hologres V1.1.48 and later allow you to use the
APPLY_PRIVILEGESfunction. If the version of your Hologres instance is earlier than V1.1.48, manually upgrade your Hologres instance in the Hologres console or join a DingTalk group to apply for an instance upgrade. For more information about how to manually upgrade a Hologres instance, see the "Manual upgrade" section in Upgrade instances. For more information about how to join a DingTalk group, see Obtain online support for Hologres.You must enable the standard PostgreSQL authorization model for the database. If the simple permission model (SPM) or the schema-level permission model (SLPM) is enabled for the database, you must switch the permission model to the standard PostgreSQL authorization model. For more information, see Switch between permission models.
You must have the GRANT and REVOKE permissions on the destination table.
If the destination table is a partitioned table, the
APPLY_PRIVILEGESfunction applies permissions only to the parent table instead of the child tables.
Parameters
old_table: required. The name of the source table. The function applies permissions on the source table to the destination table.
new_table: required. The name of the destination table. The function applies permissions on the source table to the destination table.
Example
-- Apply the ACL-based authorization information and ownership of the test_table1 table to the test_table2 table. The value false indicates that existing permissions on the test_table2 table are not revoked. CALL APPLY_PRIVILEGES('test_table1','test_table2', false); -- Apply the ACL-based authorization information and ownership of the test_table1 table to the test_table2 table and revoke existing permissions on the test_table2 table to keep the permissions on the two tables identical. CALL APPLY_PRIVILEGES('test_table1','test_table2');
Permission query functions
Sample data
In this topic, the test table is used to show how to use permission query functions. The test table is created and data is inserted into the table by executing the following statements:
CREATE TABLE test (
a INT
);
INSERT INTO test (a) VALUES (1);
INSERT INTO test (a) VALUES (2);
INSERT INTO test (a) VALUES (3);HAS_ANY_COLUMN_PRIVILEGE
Description: Checks whether the current user or a specified user has specific permissions on all columns in a specified table.
HAS_ANY_COLUMN_PRIVILEGE([user, ]table, privilege)Parameters
user: optional. The name or ID of the user account.
table: required. The name of the table.
privilege: required. The permission. You can specify multiple permissions separated by commas (,). Valid values: SELECT, INSERT, UPDATE, and REFERENCES.
Return value
A value of the BOOLEAN type is returned. If the current user or specified user has permissions on all columns in the specified table, this function returns t, which indicates true. In other cases, this function returns f, which indicates false.
Example
SELECT HAS_ANY_COLUMN_PRIVILEGE('139699392458****', 'test' , 'SELECT,INSERT,UPDATE,REFERENCES');The following result is returned:
has_any_column_privilege -------------------------- t
HAS_COLUMN_PRIVILEGE
Description: Checks whether the current user has specific permissions on a specified column.
HAS_COLUMN_PRIVILEGE(table, column, privilege)Parameters
table: required. The name of the table.
column: required. The name of the column.
privilege: required. The permission. You can specify multiple permissions separated by commas (,). Valid values: SELECT, INSERT, UPDATE, and REFERENCES.
Return value
A value of the BOOLEAN type is returned. If the current user has the specified permissions on the column, this function returns t, which indicates true. In other cases, this function returns f, which indicates false.
Example
SELECT HAS_COLUMN_PRIVILEGE('test' , 'a', 'SELECT,INSERT,UPDATE,REFERENCES');The following result is returned:
has_column_privilege ---------------------- t
HAS_DATABASE_PRIVILEGE
Description: Checks whether the current user or a specified user has specific permissions on a specified database.
HAS_DATABASE_PRIVILEGE([user, ]database, privilege)Parameters
user: optional. The name or ID of the user account.
database: required. The name of the database.
privilege: required. The permission. You can specify multiple permissions separated by commas (,). Valid values: CREATE, TEMPORARY, and TEMP.
Return value
A value of the BOOLEAN type is returned. If the current user or specified user has the specific permissions on the specified database, this function returns t, which indicates true. In other cases, this function returns f, which indicates false.
Example
SELECT HAS_DATABASE_PRIVILEGE('glz', 'CREATE,TEMPORARY,TEMP');--- Specify the name of your database.The following result is returned:
has_database_privilege ------------------------ t
HAS_FOREIGN_DATA_WRAPPER_PRIVILEGE
Description: Checks whether the current user or a specified user has specific permissions on a foreign data wrapper.
HAS_FOREIGN_DATA_WRAPPER_PRIVILEGE([user, ]fdw, privilege)Parameters
user: optional. The name or ID of the user account.
fdw: required. The name or ID of the foreign data wrapper.
privilege: required. The permission. You can specify multiple permissions separated by commas (,). Valid values: USAGE.
Return value
A value of the BOOLEAN type is returned. If the current user or specified user has the specific permissions on the foreign data wrapper, this function returns t, which indicates true. In other cases, this function returns f, which indicates false.
Example
SELECT HAS_FOREIGN_DATA_WRAPPER_PRIVILEGE('dlf_fdw', 'USAGE');-- Replace dlf_fdw with the name of your foreign data wrapper.The following result is returned:
has_foreign_data_wrapper_privilege ------------------------------------ t
HAS_FUNCTION_PRIVILEGE
Description: Checks whether the current user or a specified user has specific permissions on a specified function.
has_function_privilege([user, ]function, privilege)Parameters
user: optional. The name or ID of the user account.
function: required. The name of the function.
privilege: required. The permission. You can specify multiple permissions separated by commas (,). Valid values: EXECUTE.
Return value
A value of the BOOLEAN type is returned. If the current user or specified user has the specific permission on the specified function, this function returns t, which indicates true. In other cases, this function returns f, which indicates false.
Example
SELECT has_function_privilege('now()', 'EXECUTE');The following result is returned:
has_function_privilege ------------------------ t
HAS_LANGUAGE_PRIVILEGE
Description: Checks whether the current user or a specified user has specific permissions on a specified language.
HAS_LANGUAGE_PRIVILEGE([user, ]language, privilege)Parameters
user: optional. The name or ID of the user account.
language: required. The name of the language.
privilege: required. The permission. You can specify multiple permissions separated by commas (,). Valid values: USAGE.
Return value
A value of the BOOLEAN type is returned. If the current user or the specified user has the specific permissions on the specified language, this function returns t, which indicates true. In other cases, this function returns f, which indicates false.
Example
SELECT HAS_LANGUAGE_PRIVILEGE('plpgsql', 'USAGE');The following result is returned:
has_language_privilege ------------------------ t
HAS_SCHEMA_PRIVILEGE
Description: Checks whether the current user or a specified user has specific permissions on a specified schema.
HAS_SCHEMA_PRIVILEGE([user, ]schema, privilege)Parameters
user: optional. The name or ID of the user account.
schema: required. The name of the schema.
privilege: required. The permission. You can specify multiple permissions separated by commas (,). Valid values: CREATE and USAGE.
Return value
A value of the BOOLEAN type is returned. If the current user or the specified user has the specific permissions on the specified schema, this function returns t, which indicates true. In other cases, this function returns f, which indicates false.
Example
SELECT HAS_SCHEMA_PRIVILEGE('public', 'CREATE,USAGE');The following result is returned:
has_schema_privilege ---------------------- t
HAS_SERVER_PRIVILEGE
Description: Checks whether the current user or a specified user has specific permissions on a specified foreign server.
HAS_SERVER_PRIVILEGE([user, ]server, privilege)Parameters
user: optional. The name or ID of the user account.
server: required. The name of the foreign server.
privilege: required. The permission. You can specify multiple permissions separated by commas (,). Valid values: USAGE.
Return value
A value of the BOOLEAN type is returned. If the current user or the specified user has the specific permissions on the specified foreign server, this function returns t, which indicates true. In other cases, this function returns f, which indicates false.
Example
SELECT HAS_SERVER_PRIVILEGE('meta_warehouse_server', 'USAGE');The following result is returned:
has_server_privilege ---------------------- t
HAS_TABLE_PRIVILEGE
Description: Checks whether the current user or a specified user has specific permissions on a specified table.
HAS_TABLE_PRIVILEGE([user, ]table, privilege)Parameters
user: optional. The name or ID of the user account.
table: required. The name of the table.
privilege: required. The permission. You can specify multiple permissions separated by commas (,). Valid values: SELECT, INSERT, UPDATE, DELETE, and TRUNCATE.
Return value
A value of the BOOLEAN type is returned. If the current user or the specified user has the specific permissions on the specified table, this function returns t, which indicates true. In other cases, this function returns f, which indicates false.
Example
SELECT HAS_TABLE_PRIVILEGE('test', 'SELECT,INSERT');The following result is returned:
has_table_privilege --------------------- t
HAS_TABLESPACE_PRIVILEGE
Description: Checks whether the current user or a specified user has specific permissions on a specified tablespace.
HAS_TABLESPACE_PRIVILEGE([user, ]tablespace, privilege)Parameters
user: optional. The name or ID of the user account.
tablespace: required. The name of the tablespace.
privilege: required. The permission. You can specify multiple permissions separated by commas (,). Valid values: CREATE.
Return value
A value of the BOOLEAN type is returned. If the current user or the specified user has the specific permissions on the specified tablespace, this function returns t, which indicates true. In other cases, this function returns f, which indicates false.
Example
SELECT HAS_TABLESPACE_PRIVILEGE('pg_default', 'CREATE');The following result is returned:
has_table_privilege --------------------- t
HAS_TYPE_PRIVILEGE
Description: Checks whether the current user or a specified user has specific permissions on a specified type of object, such as tables, views, or sequences.
HAS_TYPE_PRIVILEGE([user, ]type, privilege)Parameters
user: optional. The name or ID of the user account.
type: required. The name of the object type.
privilege: required. The permission. You can specify multiple permissions separated by commas (,).
Return value
A value of the BOOLEAN type is returned. If the current user or the specified user has the specific permissions on the specified type of object, this function returns t, which indicates true. In other cases, this function returns f, which indicates false.
Example
SELECT HAS_TYPE_PRIVILEGE('test', 'USAGE');The following result is returned:
has_type_privilege -------------------- t