How to Create and Manage Superusers in PostgreSQL

Key Takeaways

To create a PostgreSQL superuser quickly, run:

createuser –superuser –pwprompt username

This command creates a new user with full privileges and prompts you securely for a password.

Ever wished you could have ultimate power, the kind of authority that lets you control everything within your domain?

A PostgreSQL superuser is the most privileged role in the system, which has unrestricted privileges: they can bypass permission checks, alter critical configurations, and even override security restrictions.

In this tutorial, I will guide you through the step-by-step method for creating a superuser in PostgreSQL. I will also help you learn how to manage existing users, revoke privileges, and adopt safer alternatives with role-based access control.

What is a PostgreSQL Superuser?

A PostgreSQL superuser is a special role that has the highest level of privileges within the database system. This role is analogous to the root user in Linux or the sa (system administrator) account in SQL Server. The default superuser account is typically named postgres.

They can bypass permission checks, manage users, modify schemas, and perform actions that no regular user can.

Key Capabilities of a Superuser

The key capabilities of a superuser are:

• Create, alter, or drop any object (databases, tables, schemas, functions, etc.).
• Bypass all permission checks, as superusers are not subject to normal privilege restrictions.
• Modify system catalogs and internal database structures.
• Create, alter, or drop other users/roles, including other superusers.
• Change server configuration and perform maintenance operations.
• Terminate any session and override row-level security policies.
• Install extensions and manage replication.

Superuser vs. Regular User

In the following table, I have summarized the permissions and features that the Superuser has over a regular user.

By now, you should have an understanding of the capabilities of a superuser and how different it is from a regular user. Now, let us see how to create a super user.

The Prerequisites

Before creating or managing superusers, ensure you have:

• PostgreSQL installed on your server.
• Access to psql (Postgres command-line client) or a GUI client.
• Ability to log in as an existing superuser (commonly postgres, or via sudo -u postgres psql).

Before creating a superuser, it is important to know the current users in PostgreSQL.

How to List Users in PostgreSQL

You can check existing users and their roles by executing the following command in the psql command-line interface:

# \du

This command provides a concise overview of all roles, including their attributes, like superuser status.

Alternatively, you can run a SQL query directly on the system catalog pg_roles:

SELECT usename, usesuper, createdb, createrole, replication

FROM pg_user;

This query displays detailed information about each role, including whether they have superuser privileges.

Now, let us see how to create a superuser in Postgres.

How to Create a Superuser in Postgres

A new superuser role in PostgreSQL can be created in multiple ways:

• Use CREATE USER
• Utilize CREATE ROLE
• Use ALTER USER

Let us discuss each method in detail.

Option #1: Use CREATE USER

The simplest way to create a new PostgreSQL superuser is with the CREATE USER command

For instance, to create a new user named Resdwitches with superuser privilege, run the following SQL statement:

CREATE USER Resdwitches WITH SUPERUSER CREATEDB CREATEROLE LOGIN PASSWORD ‘securepass’;

However, if you prefer the shell, you can use the createuser utility:

# createuser –superuser –pwprompt Resdwitches

Here, –superusergrants full privileges, and –pwprompt prompts you to set a password securely from the terminal.

To verify that the new user was created successfully, connect to your PostgreSQL instance and run the following command:

\du

Option #2: Utilize CREATE ROLE

This is an alternative method to create a superuser using the CREATE ROLEstatement. This method is more flexible for advanced role management.

To create a user named RS with superuser privilege, execute the following query.

CREATE ROLE RS WITH LOGIN SUPERUSER PASSWORD ‘securepass’;

Alternatively, you can also run the following command in the postgres terminal:

# createuser –pwprompt –superuser RS

To verify that the new user with superuser privileges is created, run the following command:

# \du

You should see the user created in the list displayed.

Option #3: Use ALTER USER

Now, if the user already exists and you need to grant them superuser privileges, you can simply promote the user with ALTER USER:

ALTER USER username WITH SUPERUSER;

Replace username with the existing role name. This adds the SUPERUSER attribute to the account.

Check the role list again to verify that the user has been promoted to a superuser.

# \du

How to Revoke Superuser Privileges in PostgreSQL

Due to the security risks associated with having too many superusers, it’s important to revoke privileges when they are no longer needed.

You can use the ALTER ROLE query to remove a user’s superuser status.

For instance, execute the following statement to remove the user’s superuser privilege:

ALTER USER username NOSUPERUSER;

This command revokes the superuser attribute, converting the user back to a regular role with standard privileges.

Now, to fully clean up database-level permissions, run:

REVOKE ALL PRIVILEGES ON DATABASE dbname FROM username;

NOTE: Always audit superuser roles in production environments. Limiting the number of superusers reduces the risk of accidental schema-wide changes or malicious activity.

Should You Have Multiple Superusers?

I always discourage creating multiple superusers in PostgreSQL, especially in production environments, due to security reasons.

Each superuser has unrestricted access to every aspect of the database: schemas, configurations, roles, and security policies. The more superusers you have, the harder it becomes to track accountability and prevent accidental or malicious changes.

Risks of Multiple Superusers

• Accidental schema-wide changes: Any superuser can make changes that affect the entire database cluster, increasing the risk of accidental data loss or corruption.
• Security breaches: More superusers mean a larger attack surface. If any superuser account is compromised, the entire system is at risk
• Audit complexity: Tracking and auditing actions becomes more difficult as the number of superusers increases, complicating compliance and incident response

The best practice is to limit the number of superusers to the absolute minimum required for administration. Regular users should be granted only the specific privileges they need, following the principle of least privilege

Role-Based Access Control (RBAC)

Role-Based Access Control (RBAC) is a security model that restricts system access to authorized users based on their assigned roles. Each role is granted specific privileges, and users are assigned to roles according to their job responsibilities

Instead of giving superuser access, assign specific roles to users.

To create roles with specific privileges, execute the following statement:

CREATE ROLE app_reader;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader;

Next, assign roles to users with the following query:

GRANT app_reader TO dev_user;

Finally, remove direct privileges from users and revoke unnecessary privileges. You can learn more about the PostgreSQL user roles in the official documentation.

Best Practices for PostgreSQL User Management

• Avoid SUPERUSER unless required: Only grant superuser privileges for essential administrative tasks
• Use password rotation and vaults: Enforce strong password policies, rotate passwords regularly, and store credentials securely.
• Separate roles for app vs. admin: Create distinct roles for application access and administrative tasks. Never use superuser accounts for routine application connections
• Monitor and audit login events: Enable logging and regularly review user activity for suspicious behavior
• Lock unused accounts: Remove or disable accounts that are no longer needed to reduce the attack surface
• Limit PUBLIC permissions: Restrict default access to the PUBLIC schema to prevent unauthorized access
• Regularly review and audit roles: Periodically check user roles and privileges to ensure compliance with security policies
• Keep PostgreSQL updated: Apply security patches and updates promptly to protect against known vulnerabilities

Troubleshooting PostgreSQL Superusers

Even after following the steps, you may encounter issues when creating or managing superusers. Here are common problems and their fixes:

Conclusion

A PostgreSQL superuser is the most powerful role in your database system, but with great power comes great responsibility.

You now know how to:

• Create superusers with CREATE USER or CREATE ROLE.
• Promote users using ALTER USER.
• Revoke privileges with NOSUPERUSER.
• Implement RBAC for safer user management.

Superusers are essential, but they should be granted sparingly. For long-term stability, always favor role-based access over unrestricted privileges.

FAQ

How do I check if a user is a superuser in PostgreSQL?

You can run \du in the psqlcommand-line or query the system catalog with:

SELECT usename, usesuper FROM pg_user;

If usesuper is t, the user is a superuser.

Can I promote an existing PostgreSQL user to a superuser?

Yes. Use the ALTER USER command:

ALTER USER username WITH SUPERUSER;

Replace username with the account you want to promote.

How do I revoke superuser privileges in PostgreSQL?

Run the following command:

ALTER USER username NOSUPERUSER;

This safely demotes the user back to a regular role.

Is it safe to have multiple superusers in PostgreSQL?

No. Best practice is to limit superusers to the minimum required. Multiple superusers increase the risk of accidental schema-wide changes, security breaches, and audit complexity.

What is the difference between a PostgreSQL superuser and the CREATEDB or CREATEROLE privileges?

• SUPERUSER → bypasses all permission checks and has unrestricted power.
• CREATEDB → allows a user to create new databases, but not manage roles.
• CREATEROLE → allows a user to create/modify roles, but not bypass all security.