Open In App

PostgreSQL – CREATE ROLE

Improve
Improve
Like Article
Like
Save
Share
Report

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 called group roles. When you create a role, it is valid in all databases in the database server (or cluster).

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

Syntax: CREATE ROLE role_name;

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

Syntax: SELECT rolname FROM pg_roles;

This will result in the following:

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

Syntax: \du

It will behave as shown below:

Role attributes

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

Syntax: 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

The following statement creates a role called ‘Raju’ that has the login privilege and an initial password:

CREATE ROLE raju
LOGIN 
PASSWORD 'mypassword1';

Note: It is required to place the password in single quotes ().

Now verify the role using the below command:

\du

The role creation is successful as shown below:

2. Create superuser roles

The following statement creates a role called ‘Nikhil’ that has the superuser attribute:

CREATE ROLE Nikhil
SUPERUSER 
LOGIN 
PASSWORD 'mypassword1';

This will lead to the following:

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

Note: One 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';

This will lead to the following:

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'

Example:

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

Output:

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 concurrent connections:

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

This will create a new role as follows:

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

\du

This will show you the results as depicted below:


Last Updated : 28 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads