PostgreSQL Roles

Introduction to PostgreSQL Roles

In order to manage the permissions for accessing the database, PostgreSQL uses a mechanism of roles. We can create a role as a user role or as a group role. The user can have the right to log in whereas the group role is not having login rights. The PostgreSQL started supporting the mechanism of users and groups from version 8.1 onwards. We can create a role within a role that is considered as a group, but it not allowed to have circular memberships. We can create roles as per the requirements and we also can remove the roles as well. Whenever we remove any role the PostgreSQL removes all its memberships and its privileges.

How does PostgreSQL Roles work?

• We can create a role as a user role or as a group role.
• If you are creating a group role then it is possible to add users to a group and you can grant or revoke the privilege of roles.
• We can remove the roles but before that, we need to reassign all its database objects as PostgreSQL removes all of the privileges and memberships of the role being dropped.
• It is not possible in PostgreSQL to create a group role with login privilege.

How to create PostgreSQL Roles?

Consider the following CREATE ROLE statement which explains how to create a new role:

Code:

CREATE ROLE role_name;

Consider the following statement and snapshot which will illustrate the all roles exist in cluster:

Code:

SELECT rolname FROM pg_roles;

Output:

Create a role named ‘David’ for the demonstration by using the following statement:

Code:

CREATE ROLE David;

After executing the above statement now, execute the following statement and snapshot which will illustrate the all roles exist in cluster:

Code:

SELECT rolname FROM pg_roles;

Output:

How to remove PostgreSQL Roles?

Consider the following DROP ROLE  statement which explains how to remove a group role or user role.

Code:

DROP ROLE role_name;

Drop a role named ‘David’ for demonstration by using the following statement::

Code:

DROP ROLE David;

After executing the above statement now, execute the following statement and snapshot which will illustrate the all roles exist in cluster:

Code:

SELECT rolname FROM pg_roles;

Output:

Examples to Implement PostgreSQL Roles

Consider the following statement and snapshot which will illustrate the all roles exist in cluster:

Code:

SELECT rolname FROM pg_roles;

Output:

We can create roles with database attributes as follows:

1. login,
2. superuser,
3. database creation,
4. role creation,
5. password, etc.

1. Consider the following CREATE ROLE  statement which explains how to create a role which has

• login privilege,
• password, and
• valid date.

CREATE ROLE John WITH PASSWORD 'edCBA2020' VALID UNTIL '2021-01-01';

After executing above statement now, execute the following statement and snapshot which will illustrates the all roles exist in cluster:

Code:

SELECT rolname FROM pg_roles;

Output:

2. Consider the following CREATE ROLE  statement which explains how to create a role that has superuser status. The superuser ignores all authorization checks:

CREATE ROLE eduCBASuperUser SUPERUSER;

After executing the above statement now, execute the following statement and snapshot which will illustrate the all roles exist in cluster:

Code:

SELECT rolname FROM pg_roles;

Output:

3. Consider the following CREATE ROLE statement which explains how to create a role which has database creation privilege:

CREATE ROLE eduCBADB CREATEDB;

After executing the above statement now, execute the following statement and snapshot which will illustrate the all roles exist in cluster:

Code:

SELECT rolname FROM pg_roles;

Output:

4. Consider the following CREATE ROLE statement which explains how to create a role which has role creation privilege:

CREATE ROLE eduCBARole CREATEROLE;

After executing the above statement now, execute the following statement and snapshot which will illustrate the all roles exist in cluster:

Code:

SELECT rolname FROM pg_roles;
Output:

5. Consider the following CREATE ROLE statement which explains how to create a group role that does not have LOGIN privilege.

CREATE ROLE group_role;

Consider the following example which will create a  group role named ‘Support’:

CREATE ROLE Support;

After executing the above statement now, execute the following statement and snapshot which will illustrate the all roles exist in cluster:

Code:

SELECT rolname FROM pg_roles;

6. Consider the following GRANT statement which illustrates how to add a user role to a group role:

GRANT group_role to user_role;

Consider the following example which illustrates how to add a user role named ‘John’ to a group role named ‘Support’:

GRANT Support TO John;

7. Consider the following REVOKE statement which illustrates how to remove a user role from a group role:

REVOKE group_role FROM user_role;

Consider the following example which illustrates how to remove a user role named ‘John’ from a group role named ‘Support’:

REVOKE Support FROM John;

Conclusion

We hope from the above article you have understood how to use the PostgreSQL Roles and how the PostgreSQL Roles works. Also, we have added several examples of PostgreSQL Roles to understand it in detail.

Recommended Articles

This is a guide to PostgreSQL Roles. Here we discuss an introduction to PostgreSQL Roles, syntax, hoe to create and remove with examples to implement. You can also go through our other related articles to learn more –

1. Cursors In PostgreSQL
2. PostgreSQL UNION ALL
3. Indexes In PostgreSQL
4. PostgreSQL DISTINCT