How To Create User With Superuser Privileges in PostgreSQL

PostgreSQL 8.1 and above

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.

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;

PostgreSQL 8.1 and below

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

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