Hologres:APPLY_PRIVILEGES

Use scenarios

When you use the CREATE TABLE LIKE or INSERT OVERWRITE statement or perform automatic partitioning to create a table in the standard PostgreSQL authorization model, a user must be granted permissions on the table before the user can use the table. The APPLY_PRIVILEGES function 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_PRIVILEGES function. If the version of your 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 Instance upgrades. 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_PRIVILEGES function applies permissions only to the parent table, not the child tables.

Syntax

-- 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>');

The following table describes the parameters in the syntax.

Examples

The following examples show how to use the APPLY_PRIVILEGES function to apply permissions.

-- Apply the ACL-based and owner permissions on 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 and owner permissions on 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');