EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Roles

PostgreSQL Roles

Sohel Sayyad
Article bySohel Sayyad
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated May 18, 2023

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 does not have login rights. 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 a group, but it is 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, PostgreSQL removes all its memberships and its privileges.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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 that exist in the cluster:

Code:

SELECT rolname FROM pg_roles;

Output:

PostgreSQL Roles - 1

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 the cluster:

Code:

SELECT rolname FROM pg_roles;

Output:

PostgreSQL Roles - 2

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 the cluster:

Code:

SELECT rolname FROM pg_roles;

Output:

PostgreSQL Roles - 3

Examples to Implement PostgreSQL Roles

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

Code:

SELECT rolname FROM pg_roles;

Output:

PostgreSQL Roles - 4

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 the above statement now, execute the following statement and snapshot, which will illustrates the all roles exist in the cluster:

Code:

SELECT rolname FROM pg_roles;

Output:

PostgreSQL Roles - 5

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;
Note: The user must be a superuser in order to create another superuser.

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

Code:

SELECT rolname FROM pg_roles;

Output:

statement and snapshot

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 the cluster:

Code:

SELECT rolname FROM pg_roles;

Output:

illustrate

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 the cluster:

Code:

SELECT rolname FROM pg_roles;

Output:

roles exist

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 the cluster:

Code:

SELECT rolname FROM pg_roles;

LOGIN privilege

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;
Note: PostgreSQL does not allow circular membership loops while creating roles.

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

We hope that this EDUCBA information on “PostgreSQL Roles” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Cursors In PostgreSQL
  2. PostgreSQL UNION ALL
  3. Indexes In PostgreSQL
  4. PostgreSQL DISTINCT
PROGRAMMING LANGUAGES Course Bundle - 54 Courses in 1 | 4 Mock Tests
338+ Hours of HD Videos
54 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SELENIUM Course Bundle - 15 Courses in 1 | 9 Mock Tests
39+ Hours of HD Videos
15 Courses
9 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
IOT System Course Bundle - 7 Courses in 1
43+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
JENKINS Course Bundle - 6 Courses in 1
15+ Hour of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
POSTGRESQL Course Bundle - 5 Courses in 1 | 1 Mock Test
 15+ Hour of HD Videos
5 Courses
1 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more