How To Create User With Superuser Privileges in PostgreSQL

Last updated on August 2nd, 2024 at 06:38 am

Sometimes database administrators need to create a superuser to carry out certain administrative tasks. In this regard, you may need to create superuser in PostgreSQL for database management or website administration. PostgreSQL allows you to create users and roles with varying degree of privileges and permissions. In this article, we will look at how to create user with superuser privileges in PostgreSQL.

What is PostgreSQL Superuser

PostgreSQL superuser is a database user with highest privileges in PostgreSQL database. It has access to all databases and tables, and can run any kinds of queries such as creation, modification and deletion of tables, databases, users and roles. By default, at the time of installation, PostgreSQL automatically creates a superuser postgres. Generally, most database programmers and web developers work with this user instead of creating another superuser. This is because if unauthorized person gains access to superuser account, they can seriously damage your system.

How To Create User With Superuser Privileges in PostgreSQL

Here are the steps to create user with superuser privileges in PostgreSQL. There are 4 different ways to create superuser, depending on your PostgreSQL version.

1. Using CREATE ROLE

PostgreSQL 8.1 onwards uses the concept of ROLE instead of a USER. A role can be a user or a group depending on your configuration. So we will create a ROLE with superuser privileges.

Log into PostgreSQL and run the following command to create a new role with superuser privileges. Replace test_role as per your requirement.

CREATE ROLE test_role LOGIN SUPERUSER;

A database role has all permissions, except the permission to login. So you need to grant it to the new role.

Also read : How to copy data from one table to another

If you also want to assign a log in password, modify the above command to the following. Replace test_password with your choice of password.

CREATE ROLE rolename LOGIN SUPERUSER PASSWORD 'test_password';

You can also create a non-super user ROLE and then make it superuser as shown below. Replace test_role with your choice of role.

CREATE ROLE test_role LOGIN;
ALTER ROLE test_role WITH SUPERUSER;

Here is the command to change superuser into a regular role.

ALTER ROLE test_role WITH NOSUPERUSER;

Also read : How to list all users in PostgreSQL

2. Using CREATE USER

Here is the command to create user with superuser privileges as per the PostgreSQL version lower than 8.1. Replace test_user as your requirement.

CREATE USER test_user SUPERUSER;

If you want you superuser to have a login password, modify the above command as shown below.

CREATE USER username SUPERUSER WITH PASSWORD 'test_password';

3. Using ALTER ROLE

You can also convert a regular user into a superuser using ALTER ROLE and ALTER USER commands. Let us first create an ordinary user.

CREATE USER test_user WITH PASSWORD 'test_password';

Next, convert it into a superuser using the ALTER USER command.

ALTER USER test_user WITH SUPERUSER;

4. Using ALTER USER

Similarly, you can also change an ordinary user to superuser using ALTER USER statement. Here is the command for it. You just need to add ‘WITH SUPERUSER’ after username in ALTER USER statement.

ALTER USER username WITH SUPERUSER

For this purpose, let us first create a regular user.

CREATE USER test_user WITH PASSWORD 'test_password';

Next, convert it into superuser with the following command.

ALTER USER test_user WITH SUPERUSER

5. Check Status

No matter which of the above commands you use to create a superuser, you can easily verify it using the following command.

\du

It will give the following output.

ROLE      | Attributes
----------+-------------
test_user | Superuser

5. Revoke Superuser

You can also revoke superuser privileges granted to a user using ALTER USER statement. Here is its syntax.

ALTER USER username NOSUPERUSER;

Here is an example to revoke superuser privileges of test_user.

ALTER USER test_user NOSUPERUSER;

That’s it. As you can see it is quite easy to create a superuser in PostgreSQL.

If you want to change user password, you can also do so with ALTER ROLE or ALTER USER statements.

Conclusion

In this article, we have learnt how to create a superuser in PostgreSQL. You can directly create a superuser using CREATE USER or CREATE ROLE statement, or change an regular user into a superuser using ALTER USER or ALTER ROLE statement, depending on your requirement. We have also learnt how to revoke superuser privileges and convert a superuser to regular user. In most cases, the default postgres superuser is enough to get the work done. So please be careful while creating a superuser for your database. It is important to monitor the usage of superuser accounts and revoke them if not required, to avoid any abuse.

Need a good reporting tool for PostgreSQL? Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.