ApsaraDB RDS:Account permissions

Permissions

Standard account

The permissions of a standard account on an RDS instance are the same as the permissions of a non-privileged account in open source PostgreSQL. For more information, see PostgreSQL official documentation.

Privileged account

The privileged account on an RDS instance has the following permissions in addition to all permissions of a standard account:

• Commit all prepared transactions during the two-phase commit (2PC) process.

  Note

  • When transactions involve operations that must be performed among multiple databases or systems, the 2PC protocol is used to ensure data consistency. The 2PC protocol ensures that all involved transactions are committed or rolled back.

  • For more information, see SQL PREPARE TRANSACTION.

• Manually execute VACUUM statements on tables.

  Note

  VACUUM statements are used to reclaim the disk space that is occupied by deleted rows and update statistics. For more information, see VACUUM.

  Examples

  • Execute the VACUUM statement to vacuum all tables in a database. When the VACUUM statement is executed, the default VACUUM parameter settings are used.

    VACUUM;

  • Execute the VACUUM FULL statement to permanently vacuum all tables in a database. When the VACUUM FULL statement is executed, the physical storage of the tables is reorganized and the unused disk space is reclaimed. The VACUUM FULL statement may require a longer period of time and more system resources than the VACUUM statement.

    VACUUM FULL;

  • Execute the VACUUM statement on specific tables. You can specify the tables on which you want to execute the VACUUM statement and specify custom VACUUM parameter settings for the tables.

    VACUUM FULL <table_name>;

    table_name specifies the name of a table.

• Execute the CREATE EVENT TRIGGER statement to create an event trigger or change the owner of the event trigger to the owner of the privileged account.

  Note

  For more information about an event trigger, see CREATE EVENT TRIGGER.

  Examples

  • Create an event trigger.

    Scenario: Create an event trigger named test_trigger. The event trigger is used to call the test_function() function when a DDL statement such as a CREATE, ALTER, or DROP statement is executed.

    CREATE EVENT TRIGGER test_trigger
    ON ddl_command_end
    EXECUTE FUNCTION test_function();

  • Change the owner of the event trigger.

    ALTER EVENT TRIGGER test_trigger OWNER TO new_owner;

    new_owner: the owner of the event trigger after the change

• Execute the CREATE EXTENSION statement to create an extension. For more information about supported extensions, see Extensions supported by ApsaraDB RDS for PostgreSQL.

  Example

  CREATE EXTENSION <Extension name>;

• Configure the LEAKPROOF parameter for a function when you create the function, or modify the LEAKPROOF parameter for the function.

  Note

  For more information, see CREATE FUNCTION.

  Example

  CREATE OR REPLACE FUNCTION test_function() 
  RETURNS void 
  LANGUAGE sql 
  LEAKPROOF 
  AS $$
  -- Implementation code of the function
  $$;

• Execute the CREATE PUBLICATION statement to create a publication or change the owner of the publication.

  Note

  In PostgreSQL, the logical replication feature is used to create a publication. The feature applies the changes of a database to other databases.

  Example

  • Create a publication. The following statement is an example. For more information, see CREATE PUBLICATION.

    CREATE PUBLICATION test_publication FOR ALL TABLES;

  • Change the owner of a publication.

    ALTER PUBLICATION <publication_name> OWNER TO <new_owner>;

• Execute the CREATE SUBSCRIPTION statement to create a subscription or change the owner of the subscription.

  Note

  In PostgreSQL, the logical replication feature is used to create a subscription. The subscription allows the system to apply the changes of a publication to databases that you subscribe to.

  Example

  • Create a subscription. The following statement is an example. For more information, see CREATE SUBSCRIPTION.

    CREATE SUBSCRIPTION test_subscription
      CONNECTION 'host=<Publisher endpoint> port=<Publisher port> dbname=<Database> user=<Account> password=<Password>'
      PUBLICATION test_publication
      WITH (slot_name = <replication_slot_name>, create_slot = false);

    test_publication specifies the name of the publication on the publisher.

  • Change the owner of the subscription.

    ALTER SUBSCRIPTION <subscription_name> OWNER TO <new_owner>;

• Use the text search feature to perform full-text searches and text-based matches. For more information, see Full Text Search.

• Change the owner of a data type.

  Example

  ALTER TYPE <type_name> OWNER TO <new_owner>;

• Create a privileged account or change a standard account to a privileged account.

  Example

  -- Create an account.
  CREATE USER <User name> WITH PASSWORD '<Password>';
  -- Change an account to a privileged account.
  GRANT pg_rds_superuser TO <user_name>;

• Query the backend processes of the privileged account and all standard accounts, and call functions such as pg_terminate_backend() for the processes.

  Example

  -- Query all backend processes of an account
  SELECT pid, datname, usename, application_name
  FROM pg_stat_activity
  WHERE usename = '<Privileged account or standard accounts>';
  
  -- call the pg_terminate_backend() function to terminate a process.
  SELECT pg_terminate_backend(pid);

• Obtain the backend connection settings of the privileged account and all standard accounts from the pg_stat_activity view.

  Example

  SELECT *
  FROM pg_stat_activity
  WHERE usename = '<Privileged account or standard accounts>'; 

• Execute GRANT and REVOKE statements on objects of a standard account. For more information, see GRANT and REVOKE.

  Example

  -- Execute the GRANT statement to authorize a standard account to execute the DELETE statement to delete rows from a table.
  GRANT DELETE ON <table_name> TO <user_name>;
  
  -- Execute the REVOKE statement to revoke the permissions.
  REVOKE DELETE ON <table_name> FROM <user_name>;

• Execute the SET ROLE statement to change the permissions of the privileged account in the current session to the permissions of the specified standard account.

  Example

  SET ROLE <Standard account>;

References

For more information about how to manage permissions in a fine-grained manner, see Manage permissions in an ApsaraDB RDS for PostgeSQL instance.