CODEWITHSUNDEEP
postgresql
chopper draw lion4
chopper draw lion4

Reputation: 13487

What is the difference between a user and a role?

I know there are other threads that are similar, but I am not sure if they are relevant to Postgres.

I am reading the PostgreSQL documentation which it reads as follows:

Note: As explained in Chapter 20, PostgreSQL actually does privilege management in terms of "roles". In this chapter, we consistently use database user to mean "role with the LOGIN privilege".

Does this basically mean a role is a database user? Or is there a difference between a role and a user? Do users have the potential to not have full privileges while roles are users who always do have full privileges?

Upvotes: 114

Views: 61227

Answers (2)

Sharad Biradar
Sharad Biradar

Reputation: 2074

As explained in AWS documentation,

Users, groups, and roles are the same thing in PostgreSQL, with the only difference being that users have permission to log in by default. The CREATE USER and CREATE GROUP statements are actually aliases for the CREATE ROLE statement.

User and Role relationship diagram

In other relational database management systems (RDBMS) like Oracle, users and roles are two different entities. In Oracle, a role cannot be used to log in to the database. The roles are used only to group grants and other roles. This role can then be assigned to one or more users to grant them all the permissions. For more details with a focus on how to migrate users, roles, and grants from Oracle to PostgreSQL, see the AWS blog post Use SQL to map users, roles, and grants from Oracle to PostgreSQL.

Upvotes: 5

IMSoP
IMSoP

Reputation: 97718

Previous versions of Postgres, and some other DB systems, have separate concepts of "groups" (which are granted access to database objects) and "users" (who can login, and are members of one or more groups).

In modern versions of Postgres, the two concepts have been merged: a "role" can have the ability to login, the ability to "inherit" from other roles (like a user being a member of a group, or a group being a member of another group), and access to database objects.

For convenience, many tools and manuals refer to any user with login permission as a "user" or "login role", and any without as a "group" or "group role", since it is useful and common practice to keep roughly to that structure. This is entirely a convention of terminology, and to understand the permissions, you need only understand the options available when creating roles and granting them access.

Again purely for convenience, Postgres still accepts commands using the old terminology, such as CREATE USER and CREATE GROUP which are both aliases for CREATE ROLE. If you write CREATE USER, the LOGIN permission will be added to the new role by default, to emulate the old behaviour when that was a separate command.

Upvotes: 138

Related Questions