Create and Drop Roles in PostgreSQL

To test a production database server, you should create additional roles
because regularly working in your databases as the default superuser role is
risky.

This article shows you how to create additional roles with PostgreSQL®
by using either psql client commands or shell commands. You need to use
the Linux® user, postgres®, with both methods.

Log in to your instance with your Linux credentials and run the following
command to switch users to the postgres user:

# sudo su - postgres

Use psql commands

Use the following steps to create or drop users by using the psql client.

Connect with psql

Connect to the database server by using the
psql
client with the postgres role:

postgres@demo:~$ psql -U postgres
...
Welcome to psql 8.3.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=#

Create a role

After you connect with the psql client, run the following command to create a
role that has the LOGIN attribute and a non-empty, MD5-encrypted password:

postgres=#CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD 'password1';

Note: The trailing semicolon ( ; ) at the end of the SQL statement is
required. The single-quotes ( ' ' ) are not part of the password but must
enclose it.

Validate that you created the role successfully by using the following command:

postgres=# \du
                               List of roles
 Role name | Superuser | Create role | Create DB | Connections | Member of
-----------+-----------+-------------+-----------+-------------+-----------
 demorole1 | no        | no          | no        | no limit    | {}
 postgres  | yes       | yes         | yes       | no limit    | {}
(2 rows)

Drop a role

When you no longer need a role, you can drop (delete or remove) a role by
using the following command:

postgres=# DROP ROLE demorole1;

If you then check with the \du command, you can see that demorole1 is no
longer listed.

Create a superuser

Occasionally, you might need to create additional superuser roles, such as when
you have a database programmer whom you trust to administer the postgres
server.

To create a superuser, run the following command:

postgres=#CREATE ROLE mysuperuser2 WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD 'mysuperpass2';

The command sets the LOGIN attribute and specifies a non-empty password.
These factors are important if you intend this superuser role for local and
remote connections to the database.

Exit psql

To exit psql, run the following command:

postgres=# \q
...
postgres@demo:~$

Use shell commands

You can create and drop database roles by using the
createuser
and
dropuser
shell commands, which are wrappers for the CREATE and DROP
SQL statements. A standard postgres installation includes these commands.

createuser

Run the following command to create a non-superuser role that has the LOGIN
attribute:

postgres@demo:~$ createuser -PE demorole2

Enter password for new role:
Enter it again:
...
postgres@demo:~$

The -P flag prompts you to set a password for the new role, and the -E flag
indicates to store the password as an MD5-encrypted string.

To verify the role creation, connect to psql and run the following command:

postgres=# \du
                               List of roles
 Role name | Superuser | Create role | Create DB | Connections | Member of
-----------+-----------+-------------+-----------+-------------+-----------
 demorole2 | no        | no          | no        | no limit    | {}
 postgres  | yes       | yes         | yes       | no limit    | {}
(2 rows)

dropuser

Run the following command to drop a role:

postgres@demo:~$ dropuser -i demorole2
...
Role "demorole2" will be permanently removed.
Are you sure? (y/n) y
...
postgres@demo:~$

The -i flag provides a confirmation prompt, which is a good safety
measure when you run a potentially destructive command.

Create a superuser

Occasionally, you might need to create additional superuser roles, such as when
you have a database programmer whom you trust to administer the postgres
server.

To create a superuser, run the following command:

postgres@demo:~$ createuser -sPE mysuperuser