Change Postgres User to Superuser

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

When you’re working with PostgreSQL, you may need to alter a user’s permission to give them elevated “superuser” privileges. It’s easy to make this change with the help of the ALTER USER command. In this article, we’ll show you how to change a Postgres user to a superuser, and we’ll also show you how to undo these changes in the event that they were made in error.

Prerequisite

Before you proceed with the instructions in this tutorial, make sure that the following product is installed and configured:

Create a New Postgres User

We’ll begin by creating a user that we can modify by altering the user permissions.

We can obtain a list of all existing users using the following command:

1
SELECT usename FROM pg_user;

We should see something like the following:

1
2
3
4
 usename
----------
 PostgreSQL
(1 row)

By default, the only user that exists in PostgreSQL is the PostgreSQL user. We’re going to create another user that we can use for the purposes of this tutorial.

We can create this new user using the CREATE USER command:

1
2
3
4
5
6
7
8
PostgreSQL=# CREATE USER developer;
CREATE ROLE
SELECT usename FROM pg_user;
  usename
-----------
 PostgreSQL
 developer
(2 rows)

We now have two users in our PostgreSQL environment.

View User Permissions

The psql utility provides us with the \du command, which lists existing permissions assigned to users.

The output of this command should look like the following:

1
2
3
4
5
6
PostgreSQL=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 developer |                                                            | {}
 PostgreSQL  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Change User Permissions

In the previous section, we created a user named developer. Let’s imagine that we want to change this user to a superuser. We can change the permissions granted to this user using the ALTER USER command.

Shown below is the basic form of the ALTER USER command:

1
ALTER USER <roleSpecification> WITH OPTION1;

Let’s take a closer look at this syntax. Notice that there are a number of options we can use in conjunction with ALTER USER, including CREATEROLE, CREATEUSER, CREATEDB and SUPERUSER. Each of these options also has a counterpart that begins with the word NO; for example, NOCREATEDB or NOSUPERUSER. The NO counterpart for each of these options is used to revoke, rather than grant, permissions. We’ll see how to use those options in the next section.

Now that we understand the process of creating users with their corresponding permission, we’ll use the ALTER USER command to grant new permissions to the existing user:

1
ALTER USER developer WITH SUPERUSER;

The statement shown above will produce a result that looks like the following:

1
ALTER ROLE

We can then verify that our ALTER USER operation was successful using the psql command \du. The result should look something like this:

1
2
3
4
5
6
PostgreSQL=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 developer | Superuser                                                  | {}
 PostgreSQL  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

How to Remove or Revoke Permissions

A Postgres superuser has powerful permissions, so it’s important that these privileges are granted carefully. In the event that a user was granted superuser permissions by mistake, we can revoke those permissions using the same ALTER USER command. We simply add the NO prefix to revoke permissions.

For example, a command that revokes superuser permissions will look like the following: ALTER USER developer WITH NOSUPERUSER;

We can verify that our command was successful with \du:

1
2
3
4
5
6
PostgreSQL=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 developer |                                                            | {}
 PostgreSQL  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

We can see that the superuser permissions for the user developer have been revoked.

Conclusion

If you’re a Postgres database administrator, there will be times when you need to elevate the permissions for a user and grant them superuser privileges. In this article, we showed you how to change a Postgres user to a superuser by using the ALTER USER command. With this tutorial to get you started, you’ll be able to grant and revoke superuser permissions for any user in your PostgreSQL environment.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.