PostgreSQL CREATE ROLE (original) (raw)

Last Updated : 15 Jul, 2025

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