How to Create a Superuser in Postgres?

February 25th, 2024
How to Create a Superuser in Postgres?

A Postgres superuser, or the postgres user, is automatically created during the PostgreSQL installation. You can make a user superuser and create an additional superuser later on. In this tutorial, I will show you how to create a superuser in PostgreSQL.

What is a Postgres superuser?

A Postgres superuser is a user in PostgreSQL with the highest privileges in the database. The user or role has absolute permissions and is allowed to perform any operation in the database server, including having direct access to any data or user, creating and deleting databases, and modifying data, among others. The Postgres superuser is equivalent to the root user in a Linux system.

Prerequisites

To follow along, ensure you already have a PostgreSQL database server installed on your system.

Check out how to install PostgreSQL on Ubuntu 22.04 or install PostgreSQL on Ubuntu 20.04 if you are using the Ubuntu 20.04 version.

How to create a superuser in Postgres?

The default superuser in PostgreSQL is the postgres user, which is installed during the installation of the PostgreSQL database server. You can optionally create additional superuser accounts later using CREATE USER or CREATE ROLE statements.

You can use both CREATE USER or CREATE ROLE statements to create a user/role. The CREATE USER statement is analogous to CREATE ROLE except that the CREATE USER statement grants LOGIN privileges, whereas the CREATE ROLE doesn't.

Option 1: Create Postgres superuser using CREATE USER statement

The CREATE USER statement is followed by the name of the Postgres superuser you want to create and the user’s password. Check out the syntax below.

CREATE USER username WITH SUPERUSER PASSWORD 'password';

For example, run the following query to create a superuser called cherry. Replace the password string with your preferred user password.

CREATE USER cherry  WITH SUPERUSER PASSWORD 'password';

Once you run the query, you will get the CREATE ROLE output, confirming that we have created the user.

create-superuser-using-create-user-statement-postgresql

To verify user creation, run the \du command. The command lists all the users in the database server.

\du

From the snippet below, you can confirm that the cherry user has been created with the superuser attribute.

list-all-users-in-postgresql

Option 2: Create Postgres superuser using CREATE ROLE statement

As mentioned earlier, the CREATE ROLE statement can also create a Postgres superuser. By default, the user created will not have granted login privileges. To enable the login capability, you must specify the LOGIN attribute after the SUPERUSER attribute, as shown in the following syntax.

CREATE ROLE username SUPERUSER LOGIN PASSWORD 'password';

Let’s create another super user called bob using the CREATE ROLE statement. To do so, run the query:

CREATE ROLE bob SUPERUSER LOGIN PASSWORD 'password';

As before, the output CREATE ROLE will be printed on the screen to confirm the creation of the user. Once again, you can verify the creation of the user using the \du command.

\du

The output in the snippet below confirms that we have created the user that is granted superuser privileges.

create-superuser-using-create-role-statement-postgresql

Option 3: Make user superuser in Postgres using ALTER USER statement

You can also make a user a Postgres superuser by modifying an existing database user into a superuser using the ALTER USER statement.

To see this in action, let’s first create a regular database user called test_user.

CREATE USER test_user WITH PASSWORD 'password';

create-a-regular-user-in-postgresql

Next, run the ALTER USER statement to alter the test_user from a regular database user to a superuser.

ALTER USER test_user WITH SUPERUSER;

As before, you can verify the alteration using the \du command.

\du

The output confirms that the user is now a superuser.

create-superuser-from-a-regular-user-in-postgresql

Conclusion

And that's it for this guide. I've demonstrated how to create a superuser in PostgreSQL in various ways. You can create a Postgres superuser using the CREATE USER and CREATE ROLE statements and use the 'ALTER USER` statement to make user a superuser in Postgres, converting a simple database user into a superuser.

Winnie is a seasoned Linux Systems administrator, currently specializing in writing technical Linux tutorials. With over seven years of experience in deploying and working with major Linux distributions such as Ubuntu, Debian, RHEL, OpenSUSE, and ArchLinux, she has written detailed and well-written "How to" Linux guides and tutorials. Winnie holds a Bachelor's Degree in Computer Science from Masinde Muliro University, Kenya and resides in Nairobi, Kenya. She is an expert in authoring Linux and DevOps topics involving Docker, Ansible, and Kubernetes. She currently works as a freelance technical writer and consultant. In her previous roles, she worked in the capacity of an IT support specialist and Linux administrator. Her key roles included offering level 1 and 2 support to both in-house and remote staff and managing and monitoring Linux servers.

Start Building Now

Deploy your new Cloud VPS server in 3 minutes starting from $5.83 / month.

We use cookies to ensure seamless user experience for our website. Required cookies - technical, functional and analytical - are set automatically. Please accept the use of targeted cookies to ensure the best marketing experience for your user journey. You may revoke your consent at any time through our Cookie Policy.
build: 0cab4b92.684