Create application users and roles in Aurora PostgreSQL-Compatible - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsArchitectureToolsEpicsRelated resourcesAdditional information

Create application users and roles in Aurora PostgreSQL-Compatible

Created by Abhishek Verma (AWS)

Environment: PoC or pilot

Source: Any database

Target: PostgreSQL database

R Type: Re-architect

Workload: Open-source

Technologies: Migration; Databases

AWS services: Amazon RDS; Amazon Aurora

Summary

When you migrate to Amazon Aurora PostgreSQL-Compatible Edition, the database users and roles that exist on the source database must be created in the Aurora PostgreSQL-Compatible database. You can create the users and roles in Aurora PostgreSQL-Compatible by using two different approaches:

  • Use similar users and roles in the target as in the source database. In this approach, the data definition languages (DDLs) are extracted for users and roles from the source database. Then they are transformed and applied to the target Aurora PostgreSQL-Compatible database. For example, the blog post Use SQL to map users, roles, and grants from Oracle to PostgreSQL covers using extraction from an Oracle source database engine.

  • Use standardized users and roles that are commonly used during development, administration, and for performing other related operations in the database. This includes read-only, read/write, development, administration, and deployment operations performed by the respective users.

This pattern contains the grants required for users and roles creation in Aurora PostgreSQL-Compatible required for the standardized users and roles approach. The user and role creation steps are aligned to the security policy of granting least privilege to the database users. The following table lists the users, their corresponding roles, and their details on the database.

Users

Roles

Purpose

APP_read

APP_RO

Used for read-only access on the schema APP

APP_WRITE

APP_RW

Used for the write and read operations on the schema APP

APP_dev_user

APP_DEV

Used for the development purpose on the schema APP_DEV, with read-only access on the schema APP

Admin_User

rds_superuser

Used for performing administrator operations on the database

APP

APP_DEP

Used for creating the objects under the APP schema and for deployment of objects in the APP schema

Prerequisites and limitations

Prerequisites 

  • An active Amazon Web Services (AWS) account

  • A PostgreSQL database, Amazon Aurora PostgreSQL-Compatible Edition database, or Amazon Relational Database Service (Amazon RDS) for PostgreSQL database

Product versions

  • All versions of PostgreSQL

Architecture

Source technology stack  

  • Any database

Target technology stack  

  • Amazon Aurora PostgreSQL-Compatible

Target architecture

The following diagram shows user roles and the schema architecture in the Aurora PostgreSQL-Compatible database.

User roles and schema architecture for the Aurora PostgreSQL-Comaptible database.

                                                                                                                                    

Automation and scale

This pattern contains the users, roles, and schema creation script, which you can run multiple times without any impact to existing users of the source or target database.

Tools

AWS services

Other services

  • psql is a terminal-based front-end tool that is installed with every PostgreSQL Database installation. It has a command line interface for running SQL, PL-PGSQL, and operating system commands.

  • pgAdmin is an open-source management tool for PostgreSQL. It provides a graphical interface that helps you create, maintain, and use database objects.

Epics

TaskDescriptionSkills required

Create the deployment user.

The deployment user APP will be used to create and modify the database objects during deployments. Use the following scripts to create the deployment user role APP_DEP in the schema APP. Validate access rights to make sure this user has only the privilege to create objects in the required schema APP.

  1. Connect to the admin user, and create the schema.

    CREATE SCHEMA APP;

  2. Create the user.

    CREATE USER APP WITH PASSWORD  <password> ;

  3. Create the role.

    CREATE ROLE APP_DEP ;
GRANT all on  schema APP to APP_DEP ;
GRANT USAGE ON SCHEMA APP to APP_DEP ;
GRANT connect on database <db_name>  to APP_DEP ;
GRANT APP_DEP to APP;

  4. To test the privileges, connect to APP and create the tables.

    set search_path to APP;
SET
CREATE TABLE test(id integer ) ;
CREATE TABLE

  5. Check the privileges.

    select schemaname  , tablename , tableowner    from pg_tables where tablename like 'test' ;
schemaname   | tablename |  tableowner 
APP                  | test            | APP
DBA

Create the read-only user.

The read-only user APP_read will be used for performing the read-only operation in the schema APP. Use the following scripts to create the read-only user. Validate access rights to make sure that this user has privilege only to read the objects in the schema APP and for automatically granting read access for any new objects created in the schema APP.

  1. Create the user APP_read.

    create user APP_read ;
alter user APP_read with password 'your_password' ;

  2. Create the role.

    CREATE ROLE APP_ro ;
GRANT SELECT ON ALL TABLES IN SCHEMA  APP  TO  APP_RO   ;
GRANT USAGE ON SCHEMA  APP   TO APP_RO
GRANT CONNECT ON DATABASE  testdb  TO APP_RO ;
GRANT APP_RO TO APP_read;

  3. To test the privileges, log in using the APP_read user.

    set search_path to APP ;
create table test1( id integer) ;
ERROR:  permission denied for schema APP
LINE 1: create table test1( id integer) ;
insert into test values (34) ;
ERROR: permission denied for table test SQL state: 42501
select from test
no rows selected
DBA

Create the read/write user.

The read/write user APP_WRITE will be used to perform read and write operations on the schema APP. Use the following scripts to create the read/write user and grant it the APP_RW role. Validate access rights to make sure that this user has read and write privileges only on the objects in the schema APP and for automatically granting read and write access for any new object created in schema APP.

  1. Create the user.

    CREATE USER APP_WRITE ;
alter user APP_WRITE  with password 'your_password' ;

  2. Create the role.

    CREATE ROLE APP_RW;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA APP   TO APP_RW ;
GRANT CONNECT ON DATABASE postgres to APP_RW ;
GRANT USAGE ON SCHEMA APP to APP_RW ;
ALTER DEFAULT PRIVILEGES IN SCHEMA APP
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO APP_RW ;
GRANT APP_RW to APP_WRITE

  3. To test the privileges, log in using the APP_WRITE user.

    SET SEARCH_PATH to APP;
CREATE TABLE test1( id integer) ;
ERROR:   permission denied for schema APP
LINE 1: create table test1( id integer) ;
SELECT * FROM test ;
id 
----
12
INSERT INTO test values (31) ;
INSERT 0 1

Create the admin user.

The admin user Admin_User will be used to perform admin operations on the database. Examples of these operations are CREATE ROLE and CREATE DATABASE. Admin_User uses the built-in role rds_superuser to perform admin operations on the database. Use the following scripts to create and test the privilege for the admin user Admin_User in the database.

  1. Create the user and grant the role.

    create user Admin_User WITH PASSWORD   ‘Your password’
ALTER user Admin_user CREATEDB;
ALTER user Admin_user CREATEROLE;

  2. To test the privilege, log in from the Admin_User user.

    SELECT * FROM APP.test ;
  id
  ----
   31
CREATE ROLE TEST ;
CREATE DATABASE test123 ;
DBA

Create the development user.

The development user APP_dev_user will have rights to create the objects in its local schema APP_DEV and read access in the schema APP. Use the following scripts to create and test the privileges of the user APP_dev_user in the database.

  1. Create the user.

    CREATE USER APP1_dev_user with password ‘your password’;

  2. Create the APP_DEV schema for the App_dev_user.

    CREATE SCHEMA APP1_DEV ;

  3. Create the APP_DEV role.

    CREATE ROLE APP1_DEV ;
GRANT   APP1_RO to APP1_DEV ;
GRANT SELECT ON ALL TABLES IN SCHEMA APP1_DEV to APP1_dev_user
GRANT USAGE, CREATE ON SCHEMA APP1_DEV to APP1_DEV_USER
GRANT APP1_DEV  to APP1_DEV_USER ;

  4. To test the privileges, log in from APP_dev_user.

    CREATE TABLE APP1_dev.test1( id integer ) ;
CREATE TABLE
INSERT into APP1_dev.test1 ( select * from APP1.test );
INSERT 0 1
CREATE TABLE APP1.test4 ( id int) ;
ERROR:   permission denied for schema APP1
LINE 1: create table APP1.test4 ( id int) ;
DBA

Related resources

PostgreSQL documentation

 

Additional information

PostgreSQL 14 enhancement

PostgreSQL 14 provides a set of predefined roles that give access to certain commonly needed, privileged capabilities and information. Administrators (including roles that have the CREATE ROLE privilege) can grant these roles or other roles in their environment to users, providing them with access to the specified capabilities and information.

Administrators can grant users access to these roles using the GRANT command. For example, to grant the pg_signal_backend role to Admin_User, you can run the following command.

GRANT pg_signal_backend TO Admin_User;

The pg_signal_backend role is intended to allow administrators to enable trusted, non-superuser roles to send signals to other backends. For more information, see PostgreSQL 14 enhancement.

Fine-tuning access

In some cases, it might be necessary to provide more granular access to the users (for example, table-based access or column-based access). In such cases, additional roles can be created to grant those privileges to the users. For more information, see PostgreSQL Grants.