PostgreSQL CREATE ROLE

Introduction to PostgreSQL – CREATE ROLE

The CREATE ROLE statement in PostgreSQL is used to create roles that represent users or groups that are used to manage access and permissions. Roles can be assigned login privileges, passwords, and attributes such as superuser or database creation rights.

Through role membership, hierarchical connections can be made, enabling the spread of permissions. The CREATE ROLE in PostgreSQL allows administrators to establish role characteristics and assign specific privileges.

The CREATE ROLE in PostgreSQL is critical for ensuring data integrity and protection by controlling security, access control, and authorization within the database.

Privilege in PostgreSQL

For a better understanding of the concept of role, we must first understand what privileges are in PostgreSQL.

Privileges in PostgreSQL are rights and permissions given to roles or users that determine their level of access and control over database objects. Privileges specify which activities can be performed, such as SELECT, INSERT, UPDATE, DELETE and others, assuring data security and integrity.

CREATE ROLE in PostgreSQL allows us to create various roles and assign them various privileges based on our needs.

Creating Roles in PostgreSQL

There are several ways to CREATE ROLE in PostgreSQL. Roles can be created either within PostgreSQL or through the command line.

Creating Roles From Within PostgreSQL

The first method for creating a role in PostgreSQL is by using the PostgreSQL prompt interface. Here, we'll create a role using the following syntax.

Syntax

Example

To see the roles use '\du' command.

Output:

Creating Roles from the Command Line

In this section, we will use the command line to create new roles. When you use createuser --interactive command, you will be prompted for the name of the new role as well as whether it should have superuser permissions.

Syntax

Depending on our needs, we can use 'y' to indicate yes and 'n' to indicate no.

Execute '\du' command to view all the roles.

Output:

Role Attributes

In PostgreSQL, role attributes define the capabilities and permissions associated with a role. Role attributes allow for fine-grained control over role behavior and access levels within a PostgreSQL database, ensuring proper permissions and security mechanisms are in place.

Here is the syntax for defining several sorts of roles in PostgreSQL, along with examples:

Create Login Roles

In this section, we are going to create a role with login privileges. In PostgreSQL, creating a login role refers to the process of creating a role with the ability to connect to the database and initiate a session. A login role enables users to log in and authenticate themselves to the database.

Output:

In the above example, a login role named "role1" is created with a specified password "loginpassword111". This role will have login privileges, allowing users to connect to the database using this role.

Create Superuser Roles

Here we will create a role having superuser privileges. Creating a superuser role in PostgreSQL refers to the process of creating a role with the maximum level of privileges and authority over the database. A superuser role has full authority and can perform any action within the database.

Output:

In the above example, a superuser role named "role2" is created with a specified password "Superuser111". This role will have login privileges and superuser status, granting it full control over the database.

When creating a superuser role, be cautious since superusers have full access to all database objects and can perform any action.

Create Roles that can Create Databases

In this section, we are going to create a role that can create databases. Creating roles in PostgreSQL that can create databases involves granting the CREATEDB attribute to the roles. Roles with the CREATEDB attribute are granted permission to create new databases within the PostgreSQL cluster.

Output:

In the above example, a role named "role3" is created with a specified password "db121212". This role will have login privileges and the CREATEDB attribute, which allows it to create new databases within the PostgreSQL cluster.

Create Roles with Validity Period

Here we will create a role that will have a validity period. These are the roles that have a time limit. After this time, the role's password is no longer valid. We can implement this feature by using a timestamp.

Output:

In the above example, a role named "role4" is created with a validity period, set to expire on 01-01-2030.

Create Roles with Connection Limit

In this section, we are going to create a role that will have a connection limit. In PostgreSQL, you can create roles with a connection limit to restrict the maximum number of concurrent connections allowed for that role. This helps control resource usage and manage access to the database.

Output:

In the above example, a role named "role5" is created with a specified password "connect1212". The connection limit is set to 1000, meaning that only up to 1000 concurrent connections can be established using this role

Deleting Roles in PostgreSQL

You can delete roles in PostgreSQL by using the DROP ROLE statement. When you delete a role, it also removes all privileges and permissions from the database related to it. Here's how to delete a PostgreSQL role:

Example To delete role5 created above.

Output:

Here we can see role5 is deleted successfully.