RDS for PostgreSQL Permissions Management

Basic Concepts

PostgreSQL manages user permissions using two concepts, role and privilege.

• Role

  A role is a collection of privileges. It can be thought of as either a user or a user group.

  • A login role is a role that has the LOGIN privilege.
  • A group role does not have the LOGIN privilege. It is used to manage privileges. Other roles can be added to the group to inherit privileges.
  • By default, all roles belong to a role named public (a special group role). Pay attention to its default privileges.

• Privilege
  Privileges are permissions to perform operations for database objects (such as tables, functions, and schemas). Common privileges include:

  • Table/View: SELECT (query), INSERT, UPDATE, DELETE, and TRUNCATE (clear)
  • Database: CONNECT and CREATE (create schema)
  • Schema: CREATE (create object) and USAGE (access object)
  • Function/Stored procedure: EXECUTE
  • Sequence: USAGE (use) and UPDATE (modify)

Principles

RDS for PostgreSQL uses roles to manage user permissions. A role by itself does not have the LOGIN privilege. You can create a user and assign a Login role to the user. Then the user can log in to databases and inherit the privileges the role has. If the role's privileges change, the user's privileges change as well.

Suggestions

• A privileged account root is provided for your RDS for PostgreSQL instance by default. This account has all privileges on your instance and can be used only by senior database administrators (DBAs).
• The project manager can create a resource account to manage roles and can also create multiple roles to enable fine-grained permissions management.
• You can create user accounts. These accounts can be used to log in to and perform operations on databases.
• If your project (prj) has multiple schemas, you are advised to divide role privileges by schema, for example, {prj}_{role}_{schema}_readonly and {prj}_{role}_{schema}_write. Do not put tables in the public schema. By default, all users have the CREATE and USAGE privileges for this schema.

Example of Privilege Design

• Senior DBAs have the privileged account root for RDS for PostgreSQL instances.
• The project manager has a resource account db_prj_owner. It is used to manage accounts and roles.
• The project name is db_prj, and the new schemas are db_prj, db_prj_1, and db_prj2.

The following table describes the privileges the resource account and roles have.

Procedure

1. Create resource account db_prj_owner and roles for your project.

   DBAs use the privileged account root to perform the following operations.

   ---db_prj_owner is the username of the owner. The password is only an example. Change it as required.
   CREATE USER db_prj_owner WITH LOGIN PASSWORD 'XXXXXXX';
   
   CREATE ROLE db_prj_role1_readwrite;
   CREATE ROLE db_prj_role2_readonly;
   
   ---Grant the DQL SELECT privilege and DML UPDATE, INSERT, and DELETE privileges on tables created by db_prj_owner to the role db_prj_role1_readwrite.
   ALTER DEFAULT PRIVILEGES FOR ROLE db_prj_owner GRANT ALL ON TABLES TO db_prj_role1_readwrite;
   
   ---Grant the DQL SELECT privilege and DML UPDATE, INSERT, and DELETE privileges on sequences created by db_prj_owner to the role db_prj_role1_readwrite.
   ALTER DEFAULT PRIVILEGES FOR ROLE db_prj_owner GRANT ALL ON SEQUENCES TO db_prj_role1_readwrite;
   
   ---Grant the DQL SELECT privilege on tables created by db_prj_owner to the role db_prj_role2_readonly.
   ALTER DEFAULT PRIVILEGES FOR ROLE db_prj_owner GRANT SELECT ON TABLES TO db_prj_role2_readonly;

2. Create users db_prj_user_readwrite and db_prj_user_readonly.

   DBAs use the privileged account root to perform the following operations.

   ---Grant the DQL SELECT privilege and DML UPDATE, INSERT, and DELETE privileges to the user db_prj_user_readwrite.
   CREATE USER db_prj_user_readwrite WITH LOGIN PASSWORD 'XXXXXXX';
   GRANT db_prj_role1_readwrite TO db_prj_user_readwrite;
   
   ---Grant the DQL SELECT privilege to the user db_prj_user_readonly.
   CREATE USER db_prj_user_readonly WITH LOGIN PASSWORD 'XXXXXXXX';
   GRANT db_prj_role2_readonly TO db_prj_user_readonly;

3. Create a schema and grant privileges on the schema to the project roles.

   DBAs use the privileged account root to perform the following operations.

   ---Specify db_prj_owner as the owner of the db_prj schema.
   CREATE SCHEMA db_prj  AUTHORIZATION db_prj_owner;
   
   ---Grant the privileges on this schema to the project roles.
   GRANT USAGE ON SCHEMA db_prj TO db_prj_role1_readwrite;
   GRANT USAGE ON SCHEMA db_prj TO db_prj_role2_readonly;

   

   The users db_prj_user_readwrite and db_prj_user_readonly inherit the privilege changes of their associated roles. You do not need to grant privileges to the users.

Scenarios

Scenario 1: Using the account db_prj_owner to perform DDL operations on tables in the db_prj schema

CREATE TABLE db_prj.table1(col1 bigserial primary key, col2 int);
DROP TABLE db_prj.table1;

Scenario 2: Using the user db_prj_user_readwrite or db_prj_user_readonly for workload development

The user that you use for workload development follows the principle of least privilege. Use the db_prj_user_readonly user if existing data does not need to be changed. Use the db_prj_user_readwrite user when you need to perform DML operations (INSERT, UPDATE, and DELETE) to change data. Data operations of different user accounts are isolated, enhancing security.

• Use db_prj_user_readwrite to add, delete, query, and modify data of tables in the db_prj schema.

  # Common DML and DQL operations are not affected.
  INSERT INTO db_prj.table1 (col2) VALUES(88),(99);
  SELECT * FROM db_prj.table1;
  
  ---The user db_prj_user_readwrite does not have the DDL CREATE, DROP, or ALTER privilege.
  CREATE TABLE db_prj.table2(id int);
  ERROR:  permission denied for schema db_prj
  LINE 1: create table db_prj.table2(id int);
  
  DROP TABLE db_prj.table1;
  ERROR:  must be owner of table test
  
  ALTER TABLE db_prj.table1 ADD col3 int;
  ERROR:  must be owner of table test
  
  CREATE INDEX idx_xxxx on db_prj.table1(col1);
  ERROR:  must be owner of table test

• Use db_prj_user_readonly to perform operations on tables in the db_prj schema.

  INSERT INTO db_prj.table1 (col2) VALUES(88),(99);
  ERROR:  permission denied for table table1
  
  SELECT id,name FROM db_prj.table1 limit 1;
   col1 | col2
  ----+-------
    1 | 88
  (1 row)

Scenario 3: Granting privileges of a project to a user belonging to another project

Assume that there is another project db_prj1 and you need to grant the read privilege on tables of project db_prj to the user db_prj1_user_readwrite that belongs to project db_prj1. DBAs use the privileged account root to perform the following operation.

---Grant the privileges of user db_prj_role2_readonly to the user db_prj1_user_readwrite.
GRANT db_prj_role2_readonly TO db_prj1_user_readwrite;

Scenario 4: Creating a schema db_prj1 and granting privileges on this schema to project roles

The users db_prj1_user_readwrite, db_prj1_user_readonly, and db_prj1_user_readwrite inherit the privilege changes of their associated roles. You do not need to grant privileges to these users. DBAs use the privileged account root to perform the following operations.

CREATE SCHEMA db_prj1 AUTHORIZATION db_prj_owner;

---Grant the access privilege on the schema to roles.
---Grant the DDL CREATE, DROP, and ALTER privileges on tables in schema db_prj1 to the account db_prj_owner.
GRANT USAGE ON SCHEMA db_prj1 TO db_prj_role1_readwrite;
GRANT USAGE ON SCHEMA db_prj1 TO db_prj_role2_readonly;

Querying Users and Roles

• Use a command-line tool to connect to your RDS for PostgreSQL instance. Run the \du command to query all users and roles. The following is an example.
  Figure 1 Running the \du command
  

  The command output shows that db_prj_role2_readonly, db_prj1_role1_readwrite is displayed in the Member of column of the user db_prj_user_readwrite.

• Run SQL statements to query users and roles.

  SELECT r.rolname, r.rolsuper, r.rolinherit,
    r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
    r.rolconnlimit, r.rolvaliduntil,
    ARRAY(SELECT b.rolname
          FROM pg_catalog.pg_auth_members m
          JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
          WHERE m.member = r.oid) as memberof
  , r.rolreplication
  , r.rolbypassrls
  FROM pg_catalog.pg_roles r
  WHERE r.rolname !~ '^pg_'
  ORDER BY 1;

  Figure 2 Running SQL statements