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.

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.

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

#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';

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.

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