How to Create a Superuser ROLE in PostgreSQL

How to Create a Superuser ROLE in PostgreSQL.

PostgreSQL onwards uses the concept of ROLE instead of 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 login password, modify the above command to the following. Replacetest_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 a superuser as shown below. Replacetest_role with your choice of role.

CREATE ROLE test_role LOGIN;

ALTER ROLE test_role WITH SUPERUSER;

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

ALTER ROLE test_role WITH NOSUPERUSER;

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

CREATE USER test_user SUPERUSER;

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

CREATE USER username SUPERUSER WITH PASSWORD 'test_password';