Hey there, let's clear up the confusion around RDS Postgres's superuser-like role and how to create new users with your default "stan" account.

First, let's get this straight: the rds_superuser role that your default users (jack, stan) are assigned to is not a full Postgres superuser. AWS restricts certain critical privileges to maintain control over the RDS instance (like modifying system configs, creating true superusers, or accessing some internal system tables). That's why you noticed it doesn't match the permissions of a self-hosted Postgres superuser.

Now, here's how to handle creating new users with "stan":

1. Creating a Regular, Privileged User #

If you need a user for everyday database operations (like querying tables, writing data), use these commands:

• First, create the user with a password:

  CREATE USER your_new_user WITH PASSWORD 'your_secure_password_here';
  

• Grant connection access to your target database:

  GRANT CONNECT ON DATABASE your_database_name TO your_new_user;
  

• Grant permissions on existing tables in a schema (e.g., public):

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

• To ensure future tables inherit these permissions, set default privileges:

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

2. What You Can't Do (And Why) #

You cannot create a full Postgres superuser with rds_superuser. If you try running:

CREATE USER super_user WITH SUPERUSER PASSWORD 'pass123';

You'll get an error. AWS reserves the ability to create true superusers for their internal management only.

3. Granting rds_superuser to Another User #

If you need another user with the same elevated permissions as "stan", you can grant the rds_superuser role directly:

GRANT rds_superuser TO your_new_admin_user;

This user will have all the same privileges as your default account, but still won't be a full superuser (no access to AWS-managed system configs, etc.).

Quick Reminder of rds_superuser Limitations #

Just to recap the key restrictions you might run into:

• Can't modify pg_hba.conf (use RDS console/CLI to manage access rules)
• Can't run pg_reload_conf() to apply config changes
• Can't create or drop roles with the SUPERUSER attribute
• Can't access some system catalogs that contain sensitive AWS-managed data

内容的提问来源于stack exchange，提问作者Tomislav Mikulin