Amazon Aurora PostgreSQL database authorization using role-based access control

Many customers are using Amazon Aurora PostgreSQL-Compatible Edition for running their business-critical database workloads in AWS. Aurora PostgreSQL is a fully managed, PostgreSQL-compatible, and ACID-compliant relational database engine that combines the speed, reliability, and manageability of Amazon Aurora with the simplicity and cost-effectiveness of open-source databases.

In this post, we present a database authorization solution using role-based access control (RBAC). You can use the same solution on Amazon Relational Database Service (Amazon RDS) for PostgreSQL.

Terminology

We use the following terminology throughout this post:

PostgreSQL database cluster – This is a collection of databases managed by a PostgreSQL server.
Database – The database contains one or more named schemas, which in turn contain tables and other named object types such as views, functions, and so on.
Role – PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up. Roles can own database objects (for example, tables and functions) and can assign privileges on those objects to other roles to control who has access to which objects. Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster.
Database authentication – This is the process of validating identity in order to control the access to the database resources.
Database authorization – This defines who can do what inside the database.
Least privilege model – With this method, users are given minimum levels of access or permissions needed to perform their job. For example, a business user of an application who runs the sales report only needs read access to the data in selective tables and shouldn’t be able to write or modify the data.

In the following sections, we demonstrate how you can implement database authorization with a set of predefined roles and least access privileges.

Role setup

We categorize authorization into three roles to provide granular access that is applicable to most applications.

Read-only role – This role has access to read the data in the database. An example of users that need a read-only role are the users who generate the reports or need to query the data on an ad hoc basis for troubleshooting business tickets.
Read/write role – This role can read as well as write and modify the data in the database. For example, the read/write role is granted to application users that are defined in the application code to connect to the database to perform the transactions in the tables.
DDL (Data Definition Language) role – This role can read, write, and modify the data, and also alter the schema by making structural changes to the database objects. For example, the DDL role is granted to the users (or developers) who are deploying new code that requires schema changes in the database.

These roles can be created in each database for each schema. The users (application or individual users) can be granted one of the three roles according to their job role for the particular application. You can design and expand this role setup further based on various requirements and scenarios, for example applying additional granularity levels or sub-grouping based on your use cases.

PostgreSQL function code

In PostgreSQL, roles are synonymous to users, with the difference that users can log in to the database but roles can’t. We create the roles, and the roles can then be granted to users according to their required access in the application.

The following sample code creates the function in the PostgreSQL database that creates the three RBAC roles (read-only, read/write, and DDL) with the necessary permissions. The input parameters for this function are as follows:

Schema name – For which the roles and permissions need to be set up.
List of tables – The default value NULL means the roles and permissions are set up for all the tables in the schema. Sometimes because of security policies related to sensitive data, all the tables of a schema aren’t permitted to the roles or users. In that case, if the list of tables is passed, it sets up the permissions only for the specified list of tables.

This example handles only the privileges on tables, but you can enhance the code as needed to handle least privileges on the rest of the object types like sequences, functions, and so on. You can deploy this code in an Aurora PostgreSQL database that has schemas and tables already set up.

-- Create this Function connecting to master user in target database
CREATE OR REPLACE FUNCTION rbac_aurora_postgres(p_schemaname text, p_tablename text[] DEFAULT NULL) 
RETURNS text
security definer as 
$$
DECLARE
    
    /* To test : select rbac_aurora_postgres('hr',ARRAY['t1','t2']); */
    dbname varchar;
	rl_found1 int;
	rl_found2 int;
	rl_found3 int;
    v_tablename text;
    v_schemaname text;
	
BEGIN
    -- Get the database name
    select current_database() into dbname;
    
    select schema_name into v_schemaname from information_schema.schemata WHERE schema_name = p_schemaname;
    IF NOT FOUND THEN
     RAISE EXCEPTION 'ERROR: Schema name % doesnt exist', p_schemaname;
    END IF;
    
    -- Create Read Only, Read Write and DDL roles
    EXECUTE FORMAT ('SELECT 1 FROM pg_catalog.pg_roles
                     WHERE  rolname = ''ro_%s_%s''',p_schemaname,dbname);
	GET DIAGNOSTICS rl_found1 = ROW_COUNT;				 
	IF rl_found1=0 THEN      
	 EXECUTE format ('create role RO_%s_%s',p_schemaname,dbname);
	END IF;
	
    EXECUTE FORMAT ('SELECT 1 FROM pg_catalog.pg_roles
                     WHERE  rolname = ''rw_%s_%s''',p_schemaname,dbname);
	GET DIAGNOSTICS rl_found2 = ROW_COUNT;				 
	IF rl_found2=0 THEN      
	 EXECUTE format ('create role RW_%s_%s',p_schemaname,dbname);
	END IF;
	
    EXECUTE FORMAT ('SELECT 1 FROM pg_catalog.pg_roles
                     WHERE  rolname = ''ddl_%s_%s''',p_schemaname,dbname);
	GET DIAGNOSTICS rl_found3 = ROW_COUNT;				 
	IF rl_found3=0 THEN      
	 EXECUTE format ('create role DDL_%s_%s',p_schemaname,dbname);
	END IF;
        
    -- Grant Connect to all 3 roles
    EXECUTE format ('GRANT CONNECT ON DATABASE %s TO RO_%s_%s,RW_%s_%s,DDL_%s_%s',dbname,p_schemaname,dbname,p_schemaname,dbname,p_schemaname,dbname);

    -- Grant Usage on schemas to all 3 roles
    EXECUTE format ('GRANT USAGE ON SCHEMA %s TO RO_%s_%s,RW_%s_%s,DDL_%s_%s', p_schemaname,p_schemaname,dbname,p_schemaname,dbname,p_schemaname,dbname);
    
    -- Grant Read only on table of schemas to RO role if the specific list of tables is not passed as parameter
        
        IF p_tablename is NOT NULL
        THEN
         FOREACH v_tablename IN ARRAY p_tablename 
         LOOP
          -- Grant select to Read only role on list of tables
          EXECUTE format ('GRANT SELECT ON TABLE %s.%s TO RO_%s_%s',p_schemaname,v_tablename,p_schemaname,dbname);
          
          -- Grant write to Read write role on list of tables
          EXECUTE format ('GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE %s.%s TO RW_%s_%s',p_schemaname,v_tablename,p_schemaname,dbname);
          
          -- Grant ALL to DDL role on list of tables
          EXECUTE format ('GRANT ALL ON TABLE %s.%s TO DDL_%s_%s',p_schemaname,v_tablename,p_schemaname,dbname);
         END LOOP;
         
        ELSE
        
        -- Grant Read only on all tables
        EXECUTE format ('GRANT SELECT ON ALL TABLES IN SCHEMA %s TO RO_%s_%s', p_schemaname,p_schemaname,dbname);
        
        -- Grant Read write on all tables,sequences and functions of schemas to RW role
        EXECUTE format ('GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA %s TO RW_%s_%s', p_schemaname,p_schemaname,dbname);

        
        -- Grant DDL (ALL) on all tables,sequence and functions to DDL role
        EXECUTE format ('GRANT ALL ON ALL TABLES IN SCHEMA %s TO DDL_%s_%s', p_schemaname,p_schemaname,dbname);

        
        -- Automatically grant permissions for new objects
        EXECUTE format ('ALTER DEFAULT PRIVILEGES IN SCHEMA %s GRANT SELECT ON TABLES TO RO_%s_%s', p_schemaname,p_schemaname,dbname);
        EXECUTE format ('ALTER DEFAULT PRIVILEGES IN SCHEMA %s GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO RW_%s_%s', p_schemaname,p_schemaname,dbname);        
        EXECUTE format ('ALTER DEFAULT PRIVILEGES IN SCHEMA %s GRANT ALL ON TABLES TO DDL_%s_%s', p_schemaname,p_schemaname,dbname);
        
        END IF;
                
    RETURN 'Completed the ReadOnly,ReadWrite and DDL Roles and Grants setup';
END;
$$
LANGUAGE 'plpgsql';

The following diagram describes a typical setup of an Aurora database in a customer environment and shows various roles and permissions that are automatically created by the function provided in this post.

After you create the RBAC roles using this function, you can assign them to the application or individual users who want to use the database. The users are able to perform their job duties within the database (read-only, read/write, or DDL) based on their assigned roles.

Solution overview

To test our PostgreSQL function, complete the following steps:

Create the databases and schemas.
Deploy and call the function to configure the roles and permissions.
Create users.
Grant the configured roles to the users.

Now let’s walk through the complete steps to deploy the solution using the following example. This example illustrates how to set up the database authorization as shown in the previous diagram.

Prerequisites

An AWS account with permissions to create CloudFormation stacks and to create Amazon VPC resources, EC2 instances, and an Aurora database
The prerequisites as mentioned in the post Deploy an Amazon Aurora PostgreSQL DB cluster with recommended best practices using AWS CloudFormation
Please consider the cost incurred for some resources during their use.
EC2 instance with PostgreSQL client (psql) installed to connect to Aurora instance.

Create the databases and schemas

Complete the following steps:

Connect to the EC2 instance

Connect to Aurora database

psql --host=testdbcluster.cluster-XXXXXX.us-east-1.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=testdb

Create the databases MGMT and FINANCE (these should already exist in the customer environment):
```
create database mgmt;
create database finance;
```
Create the schemas HR and PAYROLL in MGMT, and the schemas ACC and GL in FINANCE
Connect to the mgmt database
Create the HR and PAYROLL schema

Create and populate test tables in HR and PAYROLL schemas.

create schema HR;
create schema PAYROLL;

create table HR.hr_t1(name varchar(50) NOT NULL, id INT NOT NULL);
create table HR.hr_t2(name varchar(50) NOT NULL, id INT NOT NULL);
create table HR.hr_t3(name varchar(50) NOT NULL, id INT NOT NULL);
insert into HR.hr_t1 values('HR',1);
insert into HR.hr_t2 values('HR',2);
insert into HR.hr_t3 values('HR',3);

create table PAYROLL.pr_t1(name varchar(50) NOT NULL, id INT NOT NULL);
create table PAYROLL.pr_t2(name varchar(50) NOT NULL, id INT NOT NULL);
insert into PAYROLL.pr_t1 values('PAYROLL',1);
insert into PAYROLL.pr_t2 values('PAYROLL',2);

Deploy and call the function

Now you deploy and call the function in the MGMT database.

Connect to the MGMT database
Create the function by copying the function code from PostgreSQL function code section

Take the before-image of the roles and permissions – run the following SQL statements:

select * from pg_roles
where rolname like '%mgmt%';

select * from information_schema.role_table_grants
where grantee like '%mgmt%'
order by grantee,table_name;

We call the function with a different configuration for each schema.
- In the Payroll schema, access to all the tables is granted to the three roles (read-only, read/write, and DDL):
```
select rbac_aurora_postgres('payroll');
```
- In the HR schema, only t1 and t2 are granted access to the three roles, based on the input parameters to the function:
```
select rbac_aurora_postgres('hr',ARRAY['hr_t1','hr_t2']);
```

To check the roles and permissions after calling the function, run the following SQL statements:

select * from pg_roles
where rolname like '%mgmt%';

select * from information_schema.role_table_grants
where grantee like '%mgmt%'
order by grantee,table_name;

Create users and grant the roles

Create the following users in the MGMT database :

JOHN – Their job duty is to run the HR reports
PR_APP – This role is configured as the database connection string in the code of the Payroll application
HR_DEVELOPER – The developer of the HR application

Perform the following tasks:

Connect to MGMT database

Create the users using following SQL statements :

create user JOHN with password 'pw_in_secrets_manager';
create user ACC_APP with password 'pw_in_secrets_manager';
create user HR_DEVELOPER with password 'pw_in_secrets_manager';

Grant the appropriate roles(the roles were created by the function) to the users

grant ro_hr_mgmt to JOHN;
grant rw_payroll_mgmt to PR_APP;
grant ddl_hr_mgmt to HR_DEVELOPER;

We have demonstrated how the roles and permissions are set up within the database, and these can now be granted to the various database users as per their job duties.

RBAC and IAM database authentication

The recommended database authentication solution is AWS Identity and Access Management (IAM) database authentication, wherein the tokens are stored external to the database in IAM. This is a secured way of authenticating to the database because the passwords aren’t used and the lifetime of the tokens is just 15 minutes, after which the token isn’t valid and a new token needs to be requested to authenticate to the database. After the user is authenticated, they have access to log in to the database and grant the configured roles using RBAC to authorize users to access the required data as per their job duties.

RBAC and AWS Secrets Manager

AWS Secrets Manager is the centralized solution for credential management. You no longer have to store the database credentials (user name and password) in the source code or configuration files of your application. Instead, the application calls Secrets Manager API to retrieve the credentials and pass it to the application for database authentication. Additionally, you can configure Secrets Manager to automatically rotate the secret as per your security policies and guidelines. After authentication occurs, a connection is established to the database, and you can configure access control to determine authorization. Authorization is achieved through RBAC using the three preconfigured roles that we described in this post.

Conclusion

In this post, you learned how to configure Aurora PostgreSQL database authorization using role-based access control. This automation can save you time and resources, enabling you to focus on solving other business problems instead of managing database access.

If you have questions or feedback, leave a comment in the comments section.

About the Authors

Vivek Pinyani is a Data Architect at AWS Professional Services with expertise in Big Data and Database technologies. He focuses on helping customers build robust and performant Data Analytics solutions and Data Lake migrations. In his free time, he loves to spend time with his family, enjoys playing cricket and running.

John Lonappan is a Senior Database Specialist Consultant / Solutions Architect at Amazon Web Services (AWS) with a focus on relational databases. Prior to AWS, John has worked as Database Architect for large Data center providers across the Globe. Outside of work, he is passionate about, Long drives, EV Conversion, playing chess and traveling.

AWS Database Blog