Operating Systems

How to Create and Manage Superusers in PostgreSQL

A PostgreSQL superuser is a user with unrestricted access to all database operations, including altering roles, creating databases, and bypassing permission checks.

Table of contents

Summarize this blog on:

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.

Permission/Feature	Superuser (postgres)	Regular User	Notes
Database Creation	Full access	Unless granted CREATEDB role	CREATE DATABASE privilege required
User/Role Management	Create/alter/drop users	Cannot modify users/roles	Requires CREATEROLE privilege
Schema Operations	Create/alter/drop schemas	Unless granted schema privileges	Schema ownership required
Table Operations	Full CRUD on all tables	Only on owned/granted tables	GRANT SELECT ON TABLE required
Extension Installation	Install any extension	Cannot install extensions	Some extensions allow limited user access
Server Configuration	Modify postgresql.conf	Read-only access	Requires filesystem access
Backup/Restore	pg_dumpall, pg_restore	Only own databases with pg_dump	GRANT CONNECT required
View System Catalogs	Full access to pg_catalog	Limited access	GRANT USAGE ON SCHEMA pg_catalog
Kill Connections	Terminate any session	Only own connections	pg_terminate_backend() function
Bypass Row-Level Security	Override RLS policies	Subject to RLS	ALTER ROLE … BYPASSRLS
Replication	Configure replication slots	No access	Requires REPLICATION privilege
Comment Management	Comment on any object	Only owned objects	GRANT COMMENT ON required
Foreign Data Wrapper Usage	Create/modify FDWs	Usage-only unless granted	GRANT USAGE ON FOREIGN SERVER
Large Object Access	Full read/write	Only if granted	GRANT SELECT/UPDATE ON LARGE OBJECT
Vacuum Operations	Full VACUUM privileges	Only VACUUM on owned tables	AUTOVACUUM runs as superuser

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).

Commands to Create and Manage PostgreSQL Superusers
Steps	Commands	Notes
List all users and roles	\du	Shows existing users and their privileges
Create a superuser (SQL)	CREATE USER username WITH SUPERUSER PASSWORD ‘securepass’;	Direct SQL method to create a new superuser
Create a superuser (Shell)	createuser –superuser –pwprompt username	Prompts for password and grants full privileges
Promote an existing user	ALTER USER username WITH SUPERUSER;	Adds superuser privileges to an existing account
Revoke superuser privileges	ALTER USER username NOSUPERUSER;	Safely demotes user back to a regular role
Check role attributes	SELECT usename, usesuper FROM pg_user;	Displays superuser status of each user

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, –superuser grants 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 ROLE statement. 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:

Issue	Cause/Indication	Fix
ERROR: permission denied to create role	Trying to create or alter roles without superuser rights	Log in as the default postgres user: sudo -u postgres psql
ERROR: role “username” already exists	Attempting to create a user that already exists	Use ALTER USER username WITH SUPERUSER instead
Must be superuser to alter role	Non-superuser trying to promote/demote another role	Switch to postgres account or another superuser first
Password authentication failed	Wrong password entered during login or user creation	Reset password: ALTER USER username WITH PASSWORD ‘newpass’;

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 psql command-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.

Pratik

Pratik Kshirsagar is a Linux system administrator at RedSwitches (A fully Customizable Dedicated Server Hosting platform). Besides his work life, he loves music and traveling. You can email him at [email protected]