{"id":2409,"date":"2025-03-11T17:03:40","date_gmt":"2025-03-11T10:03:40","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=2409"},"modified":"2025-03-11T17:05:17","modified_gmt":"2025-03-11T10:05:17","slug":"postgresql-role-membership","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-database-administration\/postgresql-role-membership\/","title":{"rendered":"PostgreSQL Role Membership"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you learn about role membership, how to create a user group and manage role membership by adding a user to a group and removing a user from a group.<\/p>\n\n\n\n

Creating a group #<\/a><\/h2>\n\n\n\n

You can use the\u00a0CREATE ROLE<\/a><\/code>\u00a0statement to create a user group. In PostgreSQL, a user group is called a group role.<\/p>\n\n\n\n

For example, the following CREATE ROLE<\/code> statement creates a new group called admin<\/code>:<\/p>\n\n\n

CREATE<\/span> ROLE<\/span> admin<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Typically, a group does not have LOGIN<\/code> privilege. But you can allow it to log in if necessary.<\/p>\n\n\n\n
Adding users to a group #<\/a><\/h2>\n\n\n\n
The following statements create two users (roles) with the LOGIN<\/code> privilege:<\/p>\n\n\n
CREATE<\/span> ROLE<\/span> bob WITH<\/span> LOGIN<\/span> PASSWORD<\/span> 'securepassword'<\/span>;\nCREATE<\/span> ROLE<\/span> alice WITH<\/span> LOGIN<\/span> PASSWORD<\/span> 'securepassword'<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following GRANT<\/code> statement adds the user bob<\/code> to the admin<\/code> group:<\/p>\n\n\n
GRANT<\/span> admin<\/span> TO<\/span> bob;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
A group can have multiple users. For example, the following statement grants the membership of the admin<\/code> to alice<\/code>:<\/p>\n\n\n
GRANT<\/span> admin<\/span> TO<\/span> alice;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The users bob<\/code> and alice<\/code> are now the members of the admin<\/code> group.<\/p>\n\n\n\n
A user may belong to multiple groups. The relationship between users and groups is many-to-many.<\/p>\n\n\n\n
Inheritance of privileges #<\/a><\/h2>\n\n\n\n
Since bob<\/code> and alice<\/code> are members of the admin<\/code> group, they inherit the privileges granted to the admin<\/code> group by default.<\/p>\n\n\n\n
It means that bob<\/code> and alice<\/code> can use privileges granted to the admin<\/code>.<\/p>\n\n\n\n
For example, if you grant the SELECT<\/code> privilege on the products<\/code> table to the admin<\/code> as follows:<\/p>\n\n\n
GRANT<\/span> SELECT<\/span> ON<\/span> TABLE<\/span> products TO<\/span> admin<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Both bob<\/code> and alice<\/code> can select data from the products<\/code> table, even though we do not directly grant the SELECT<\/code> privilege on the products<\/code> table to bob<\/code> and alice<\/code>.<\/p>\n\n\n\n
If you log in to the inventory<\/code> database using bob<\/code> or alice<\/code>, you should be able to select data from the products<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> products;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you don’t want bob<\/code> and alice<\/code> to inherit privileges in the admin<\/code> group, you can set the INHERIT<\/code> to FALSE<\/code>. Both users will not inherit privileges from the admin<\/code> group:<\/p>\n\n\n
GRANT<\/span> admin<\/span> TO<\/span> alice WITH<\/span> INHERIT<\/span> FALSE<\/span>;\nGRANT<\/span> admin<\/span> TO<\/span> bob WITH<\/span> INHERIT<\/span> FALSE<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Removing users from groups #<\/a><\/h2>\n\n\n\n
To remove a user from a group, you use the REVOKE<\/code> statement:<\/p>\n\n\n
REVOKE<\/span> group_role FROM<\/span> role1, role2;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement removes alice<\/code> and bob<\/code> from the admin<\/code> group:<\/p>\n\n\n
REVOKE<\/span> admin<\/span> FROM<\/span> alice, bob;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the CREATE ROLE<\/code> statement to create a group.<\/li>\n\n\n\n
  • Use the GRANT<\/code> statement to add a user to a group.<\/li>\n\n\n\n
  • A user inherits the privileges of the group by default.<\/li>\n\n\n\n
  • Use the REVOKE<\/code> statement to remove a user from a group.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
You can use the\u00a0GRANT<\/a><\/code>\u00a0statement to add a user (a role) to a group (group role).<\/p>\n\n\n\n