PostgreSQL - CREATE ROLE

The CREATE ROLE command in PostgreSQL is essential for managing database roles and user permissions within a PostgreSQL cluster. With PostgreSQL role creation, database administrators can define roles that control access to database objects, making it easier to enforce security and manage access across users and groups.

In this article, we will explore how to create and manage roles in PostgreSQL using the CREATE ROLE command, with practical examples to demonstrate syntax and use cases.

PostgreSQL CREATE ROLE

In PostgreSQL, a role represents an entity with specific privileges within a database. Roles can act as users (with login capabilities), groups (aggregating users for permissions), or a combination of both. Using roles, administrators can set user privileges, manage data access, and define restrictions, making PostgreSQL role management crucial for effective database administration.

Syntax

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

CREATE ROLE role_name;

Retrieving All Roles

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

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

\du

Output

Role attributes

The role attributes define specific privileges and permissions for a role. These attributes control what the role can do within the database, including the ability to log in, manage databases, create new roles, or gain superuser privileges.

CREATE ROLE name WITH option;

key terms

• SUPERUSER: Grants the role superuser privileges, allowing it to bypass all access restrictions.
  
• CREATEDB: Enables the role to create databases.
  
• CREATEROLE: Allows the role to create, alter, and drop other roles.
  
• LOGIN: Enables the role to log into the database (making it function as a user).
  
• PASSWORD: Sets a password for roles with LOGIN capability.

Examples of PostgreSQL CREATE ROLE Command

Let us take a look at some of the examples of CREATE ROLE Statement in PostgreSQL to better understand how it works in various scenarios, enabling efficient management of user roles and permissions.

Example 1: Create login roles

The following statement creates a role called 'Raju' that has the login privilege and an initial password. Note that It is required to place the password in single quotes (').

Query:

CREATE ROLE raju
LOGIN 
PASSWORD 'mypassword1';

Now verify the role using the below command:

\du

Output

Example 2: Create superuser roles

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

Query:

CREATE ROLE Nikhil
SUPERUSER 
LOGIN 
PASSWORD 'mypassword1';

Output

The superuser can override all access restrictions within the database therefore we should create this role only when needed. One must be a superuser in order to create another superuser role.

Example 3: Create roles that can create databases

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

Query:

CREATE ROLE dba 
CREATEDB 
LOGIN 
PASSWORD 'Abcd1234';

Output

Example 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.

Query:

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

Output

Example 5: Create roles with connection limit

To specify the number of concurrent connections a role can make, we use the 'CONNECTION LIMIT' attribute. The following creates a new role called API that can make 1000 concurrent connections.

Query:

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

Output

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

\du

Output

Important Points About PostgreSQL CREATE ROLE Statement

• Global Scope: Roles are valid across the entire PostgreSQL server, so they don’t need to be recreated for each database.
  
• Inheritance: Roles inherit privileges from other roles they belong to by default, controlled by the INHERIT attribute.
  
• Password Management: Specifying PASSWORD NULL removes the password, preventing login for roles with the LOGIN attribute.
  
• Connection Limits: Use the CONNECTION LIMIT attribute to control the number of concurrent connections for each role.

Conclusion

Understanding PostgreSQL user roles and effectively using the CREATE ROLE command is essential for secure and organized database management. By creating roles with specific permissions, PostgreSQL allows administrators to control user access and functionality, from superuser privileges to restricted access roles.

Mastering PostgreSQL roles and permissions enables reliable user management and enhances database security, ensuring each user has the appropriate level of access. Using these capabilities supports better data governance and streamlined access control in PostgreSQL environments.