PostgreSQL - Administrator Role and SUPERUSER

 Step 1: Creating an Administrator Role with SUPERUSER Privileges (Standard PostgreSQL)

 In a self-managed PostgreSQL environment (on-premises or cloud VM), you can create an administrator role with full SUPERUSER privileges using:

 CREATE ROLE administrator SUPERUSER LOGIN PASSWORD 'StrongSecurePassword';

 This role will have:

• Full access to all databases

• Ability to create, alter, and drop any database objects

• Manage roles and permissions

• Access system catalogs and perform backups

• Enable or disable extensions

• Perform maintenance and configuration tasks

 Behavior in Amazon RDS for PostgreSQL

 In Amazon RDS, even though the master user is granted the rds_superuser role, it is NOT a true PostgreSQL superuser.

 AWS restricts certain superuser capabilities for security and managed service operations.

 Important Limitation:

Creating another user with the SUPERUSER attribute is NOT permitted in Amazon RDS, even if you are using the rds_superuser role.

 The SUPERUSER attribute is one of the restricted operations in RDS.

 Workaround / Alternatives in Amazon RDS

 

Although you cannot create a true SUPERUSER role in RDS, you can create a role with almost all administrative privileges by granting the rds_superuser role.

 Example:

 CREATE ROLE administrator NOLOGIN;

GRANT rds_superuser TO administrator;

 You can then grant this role to specific users as needed.

 

Key Takeaways

 

• True SUPERUSER roles are only available in self-managed PostgreSQL.

• Amazon RDS uses rds_superuser as a controlled alternative.

• SUPERUSER creation is blocked in RDS environments.

• Administrative access can still be delegated safely using role grants.

PostgreSQL - Read Write Role

 This guide explains how to create a PostgreSQL role that allows users to:

• Read data and schema
• View query execution plans
• Modify data (INSERT / UPDATE / DELETE) without schema changes
• Create and manage objects (CRUD) within their own schema only

Step 1: Create a Base Role (No Login)

CREATE ROLE read_write NOLOGIN;

Step 2: Grant Read Access on Shared Schema

GRANT USAGE ON SCHEMA public TO read_write;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_write;

Step 3: Allow Data Modification (Without Schema Changes)

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;

Step 4: Ensure Future Tables Inherit Permissions

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO read_write;

Step 5: Enable Query Performance Insights

ALTER ROLE read_write SET track_io_timing TO on;

Step 6: Create User with Personal Schema

CREATE ROLE rw_user LOGIN PASSWORD 'securepassword';
GRANT read_write TO rw_user;

CREATE SCHEMA AUTHORIZATION rw_user;

Conclusion

The user now has:
• Read and write access to shared schema tables
• No permission to alter shared schema structure
• Full CREATE / ALTER / DROP privileges within their own schema

This approach follows PostgreSQL security best practices by separating:
• Permission roles (NOLOGIN)
• User login roles
• Shared schema access
• Personal schema ownership

PostgreSQL - Read-Only Role

 

PostgreSQL Read-Only Role Setup 

Overview

This guide explains how to create a reusable READ_ONLY role in PostgreSQL that allows users to:

• Read data from tables

• Access schema objects

• Use temporary tables

 The approach uses a NOLOGIN role for permissions and a LOGIN role for end users. All database, schema, and user names are generic so the steps can be reused across environments.

Step 1: Create the Read-Only Role (No Login)

CREATE ROLE read_only NOLOGIN;

Explanation

A NOLOGIN role acts as a permission group. Users inherit privileges by being granted membership in this role.

Step 2: Grant Schema Usage

GRANT USAGE ON SCHEMA schema_name TO read_only;

GRANT USAGE ON SCHEMA public TO read_only;

Explanation

USAGE allows the role to see objects inside the schema.

Step 3: Grant SELECT on Existing Tables

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO read_only;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

Explanation

This grants read access to all current tables in the schemas.

Step 4: Grant SELECT on Future Tables

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name

GRANT SELECT ON TABLES TO read_only;

Explanation

This ensures new tables created later automatically grant SELECT permission to the read_only role.

Step 5: Allow Temporary Tables (Database Level)

GRANT TEMP ON DATABASE database_name TO read_only;

Explanation

This allows users to create temporary tables, which is often required for reporting or analytics queries.

Step 6: Create a User and Assign Read-Only Role

CREATE ROLE user_name LOGIN PASSWORD 'secure_password';

GRANT read_only TO user_name;

Explanation

This creates a login user and grants membership in the read_only role.

Example: Applying to Another Schema

CREATE ROLE read_only NOLOGIN;

 

GRANT USAGE ON SCHEMA another_schema TO read_only;

 

GRANT SELECT ON ALL TABLES IN SCHEMA another_schema TO read_only;

 

ALTER DEFAULT PRIVILEGES IN SCHEMA another_schema

GRANT SELECT ON TABLES TO read_only;

 

CREATE ROLE another_user LOGIN PASSWORD 'secure_password';

GRANT read_only TO another_user;

Best Practices

• Use group roles for permission management

• Avoid granting privileges directly to users

• Periodically review role memberships

• Restrict write access unless required

• Use strong passwords or IAM/SSO authentication

Conclusion

Using a READ_ONLY role simplifies access management and improves security by centralizing permissions. This approach is scalable and recommended for production environments.

PostgreSQL - Users and Roles Queries - \du+ in DBever

 

PostgreSQL Users and Roles Queries

Introduction

In PostgreSQL, there is no strict distinction between users and roles. Everything is a role; a user is simply a role that has the LOGIN privilege (rolcanlogin = true). This document provides practical SQL queries to audit roles, role memberships, and privileges in a PostgreSQL database environment. All names are kept generic for reuse.

Key Concept

Users: Roles with LOGIN privilege (rolcanlogin = true)

Groups/Roles: Roles without LOGIN privilege (rolcanlogin = false)

A role can be a member of other roles, inheriting permissions.

1.      List All Roles with Attributes and Membership

 

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;

Explanation

This query lists all non-system roles and shows:

• Superuser privileges

• Database creation privileges

• Login capability

• Connection limits

• Membership in other roles

• Replication privileges

• Row-level security bypass capability

2. List Users and Their Granted Roles

SELECT

    u.rolname AS username,

    r.rolname AS granted_role

FROM pg_auth_members m

JOIN pg_roles u ON m.member = u.oid

JOIN pg_roles r ON m.roleid = r.oid

WHERE u.rolcanlogin = true

ORDER BY u.rolname, r.rolname;

Explanation

This query shows which roles are granted to login users only, making it useful for access audits.

3. Get Roles Assigned to a Specific User

SELECT roleid::regrole,

       member::regrole

FROM pg_auth_members

WHERE member = 'username'::regrole;

Explanation

Replace 'username' with the target user. This query returns all roles granted to that specific user.

4. Alternative Query — User Membership View

SELECT

    r.oid AS role_id,

    r.rolname,

    r.rolcanlogin,

    COALESCE(b.rolname, '') AS memberof

FROM pg_catalog.pg_roles r

LEFT JOIN pg_catalog.pg_auth_members m ON r.oid = m.member

LEFT JOIN pg_catalog.pg_roles b ON m.roleid = b.oid

WHERE r.rolcanlogin = true

  AND r.rolname !~ '^pg_';

Explanation

This query focuses only on login roles (users) and shows which group roles they belong to.

Common Audit Use Cases

• Security audits

• Access reviews

• Compliance checks

• Migration planning

• Privilege troubleshooting

Best Practices

• Avoid granting superuser unless absolutely required

• Use group roles for permission management

• Periodically audit role memberships

• Monitor replication and bypass RLS privileges

• Remove unused roles

Conclusion

Understanding PostgreSQL roles and memberships is essential for secure database administration. The queries provided in this guide help administrators audit and manage access efficiently.