Using rds_hwdrs_privs

Introduction

The rds_hwdrs_privs plugin is used to escalate root privileges for some RDS for PostgreSQL versions, including:

• Granting the SELECT permission on pg_catalog.pg_authid
• Granting the bypassrls and replication permissions
• Granting the permissions required to create a publication for ALL TABLES
• Granting the permissions required to execute some pg_replication_origin_xxx functions

Supported Versions

This plugin is available to RDS for PostgreSQL 9.5 to 15. This plugin is used to escalate root privileges for RDS for PostgreSQL 9.5, 9.6, 10, 11.5, and earlier versions. For versions later than 11.5, you can perform operations granted by this plugin as user root directly.

You can run the following SQL statement to check whether your DB instance supports this plugin:

SELECT * FROM pg_available_extension_versions WHERE name = 'rds_hwdrs_privs';

To see more plugins supported by RDS for PostgreSQL, go to Supported Plugins.

Plugin Installation and Uninstallation

• Installing the plugin

  SELECT control_extension ('create', 'rds_hwdrs_privs');

• Uninstalling the plugin

  SELECT control_extension ('drop', 'rds_hwdrs_privs');

For more information, see Installing and Uninstalling a Plugin on the RDS Console and Installing and Uninstalling a Plugin Using SQL Commands.

How to Use

This plugin can be used only by user root or a member user of user root.

You can directly perform operations as user root or grant root permissions to another user (for example, drs_sync) by running the following command:

grant root to drs_sync;

and use user drs_sync to perform operations.

• Granting the SELECT permission on pg_catalog.pg_authid to a user

  select control_select_on_pg_authid('grant', 'drs_sync');

  The first parameter can be grant or revoke. The second parameter indicates a specific user, which must have been created.

• Granting the bypassrls and replication permissions to a user

  select control_user_privilege('bypassrls', 'drs_sync');

  The first parameter indicates the permission to be assigned. The options are bypassrls, nobypassrls, replication, and noreplication. The second parameter indicates a specific user, which must have been created.

• Granting the permissions required to create a publication for ALL TABLES

  select create_publication_for_all_tables('foo_pub', 'insert, update');
  select create_publication_for_all_tables('foo_pub');

  

  This function creates only a publication for all tables. You can use SQL statements to create a publication for a specific table.

  The first parameter is the publication name, which must be different from any existing publication name. The second parameter indicates which DML operations will be published by the new publication to the subscribers. By default, the value is the same as that of create publication foo_pub for all tables. In RDS for PostgreSQL 10, the allowed operations are insert, update, and delete. In RDS for PostgreSQL 11, truncate is also supported.

  The owner of the new publication is root. You can perform subsequent operations on the publication using SQL statements as root or a member user of root.

• Granting the permissions required to execute some pg_replication_origin_xxx functions

  -- Creating a replication origin
  select exec_pg_replication_origin_func('pg_replication_origin_create', 'foo_repl_origin');
  -- Deleting a replication origin
  select exec_pg_replication_origin_func('pg_replication_origin_create', 'foo_repl_origin');
  -- Checking whether the current session is bound to a replication origin
  select exec_pg_replication_origin_func('pg_replication_origin_session_is_setup');

  The first parameter indicates the name of the function to be executed. The options are pg_replication_origin_create, pg_replication_origin_drop, pg_replication_origin_oid, pg_replication_origin_session_setup, pg_replication_origin_session_reset, and pg_replication_origin_session_is_setup. The second parameter can be left blank. Whether the second parameter is left blank depends on whether the function to be executed requires a parameter value.