PostgreSQL – CREATE ROLE
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:
Contact Us