PostgreSQL Tutorial: CREATE ROLE

Summary: in this tutorial, you will learn about the PostgreSQL roles and how to use the CREATE ROLE statement to create new roles.

PostgreSQL uses roles to represent user accounts. It doesn’t use the user concept like other database systems.

Typically, roles can log in are called login roles. They are equivalent to users in other database systems.

When roles contain other roles, they are call group roles.

Note that PostgreSQL combined the users and groups into roles since version 8.1

PostgreSQL CREATE ROLE statement

To create a new role, you use the CREATE ROLE statement as follows:

CREATE ROLE role_name;

When you create a role, it is valid in all databases in the database server (or cluster).

The following statement uses the CREATE ROLE statement to create a new role called bob:

CREATE ROLE bob;

To get all roles in the current PostgreSQL database server, you can query them from the pg_roles system catalog as follows:

SELECT rolname FROM pg_roles;

Output:

          rolname
---------------------------
 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend
 postgres
 bob

Noice that the roles that start with with pg_ are system roles.

If you use the psql tool, you can use the \du command to list all existing roles in the current PostgreSQL database server.

\du

Output:

                                  List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 bob       | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

As you can see clearly from the output, the role bob cannot login.

To allow the role bob to log in to the PostgreSQL database server, you need to add the LOGIN attribute to it.

Role attributes

The attributes of a role define privileges for that role including login, superuser, database creation, role creation, password, etc:

CREATE ROLE name WITH option;

In this syntax, the WITH keyword is optional. And the option can be one or more attributes including SUPER, CREATEDB, CREATEROLE, etc.

1) Create login roles

For example, the following statement creates a role called alice that has the login privilege and an initial password:

CREATE ROLE alice 
LOGIN 
PASSWORD 'securePass1';

Note that you place the password in single quotes (').

Now, you can use the role alice to log in to the PostgreSQL database server using the psql client tool:

psql -U alice -W postgres

It will prompt you for a password. You need to enter the password that you entered in the CREATE ROLE statement.

2) Create superuser roles

The following statement creates a role called john that has the superuser attribute.

CREATE ROLE john 
SUPERUSER 
LOGIN 
PASSWORD 'securePass1';

The superuser can override all access restrictions within the database therefore you should create this role only when needed.

Notice that you must be a superuser in order to create another superuser role.

3) Create roles that can create databases

If you want to create roles that have the database creation privilege, you use the CREATEDB attribute:

CREATE ROLE dba 
CREATEDB 
LOGIN 
PASSWORD 'Abcd1234';

4) Create roles with validity period

To set a date and time after which the role’s password is no longer valid, you use the valid until attribute:

VALID UNTIL 'timestamp'

For example, the following statement creates a dev_api role with password valid until the end of 2029:

CREATE ROLE dev_api WITH
LOGIN
PASSWORD 'securePass1'
VALID UNTIL '2030-01-01';

After one second tick in 2030, the password of dev_api is no longer valid.

5) Create roles with connection limit

To specify the number of concurrent connections a role can make, you use the CONNECTION LIMIT attribute:

CONNECTION LIMIT connection_count

The following creates a new role called api that can make 1000 concurent connections:

CREATE ROLE api
LOGIN
PASSWORD 'securePass1'
CONNECTION LIMIT 1000;

The following psql command shows all the roles that we have created so far:

\du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 alice     |                                                            | {}
 api       | 1000 connections                                           | {}
 bob       | Cannot login                                               | {}
 dba       | Create DB                                                  | {}
 john      | Superuser                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Summary

• PostgreSQL uses roles to represent user accounts. A role that can login is equivalent to a user acocunt in other database systems.
• Use role attributes to specify the privileges of the roles e.g., LOGIN allows the role to login, CREATEDB allows the role to create a new database, SUPERUSER allows the role to have all privileges.