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.