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.
Cloud VPS - Cheaper Each Month
Start with $9.99 and pay $0.5 less until your price reaches $6 / month.