PostgreSQL Role Membership

Summary: 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.

Creating a group #

You can use the CREATE ROLE statement to create a user group. In PostgreSQL, a user group is called a group role.

For example, the following CREATE ROLE statement creates a new group called admin:

CREATE ROLE admin;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Typically, a group does not have LOGIN privilege. But you can allow it to log in if necessary.

Adding users to a group #

The following statements create two users (roles) with the LOGIN privilege:

CREATE ROLE bob WITH LOGIN PASSWORD 'securepassword';
CREATE ROLE alice WITH LOGIN PASSWORD 'securepassword';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You can use the GRANT statement to add a user (a role) to a group (group role).

For example, the following GRANT statement adds the user bob to the admin group:

GRANT admin TO bob;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

A group can have multiple users. For example, the following statement grants the membership of the admin to alice:

GRANT admin TO alice;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The users bob and alice are now the members of the admin group.

A user may belong to multiple groups. The relationship between users and groups is many-to-many.

Inheritance of privileges #

Since bob and alice are members of the admin group, they inherit the privileges granted to the admin group by default.

It means that bob and alice can use privileges granted to the admin.

For example, if you grant the SELECT privilege on the products table to the admin as follows:

GRANT SELECT ON TABLE products TO admin;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Both bob and alice can select data from the products table, even though we do not directly grant the SELECT privilege on the products table to bob and alice.

If you log in to the inventory database using bob or alice, you should be able to select data from the products table:

SELECT * FROM products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you don’t want bob and alice to inherit privileges in the admin group, you can set the INHERIT to FALSE. Both users will not inherit privileges from the admin group:

GRANT admin TO alice WITH INHERIT FALSE;
GRANT admin TO bob WITH INHERIT FALSE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Removing users from groups #

To remove a user from a group, you use the REVOKE statement:

REVOKE group_role FROM role1, role2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement removes alice and bob from the admin group:

REVOKE admin FROM alice, bob;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the CREATE ROLE statement to create a group.
• Use the GRANT statement to add a user to a group.
• A user inherits the privileges of the group by default.
• Use the REVOKE statement to remove a user from a group.