Summary: in this tutorial, we will introduce you to PostgreSQL roles concept and show you how to create user roles and group roles.
PostgreSQL uses the roles concept to manage database access permissions. A role can be a user or a group, depending on how you setup the role. A role that has login right is called user. A role may be a member of other roles, which are known as groups.
Creating PostgreSQL roles
From version 8.1, PostgreSQL uses the roles concept to incorporate the users and groups concepts. To create a new role, you use the
CREATE ROLE statement as follows:
CREATE ROLE role_name;
To get all available roles in the cluster, you query from the pg_roles system catalog as the following statement:
If you use the psql tool, you can use the \du command to list all existing roles.
The attributes of a database role define role’s privileges including login, superuser, database creation, role creation, password, etc.
The following statement creates a role that has login privilege, password, and valid date.
CREATE ROLE doe WITH PASSWORD 'pgSecpas1970' VALID UNTIL '2020-01-01';
The following statement creates a role that has superuser status, which means this role can bypass all authorization checks:
CREATE ROLE bigboss SUPERUSER;
Notice that you must be a superuser in order to create another superuser.
If you want a role to have database creation privilege, you use the following statement:
CREATE ROLE admin CREATEDB;
Use the following statement to create a role that has creation privilege:
CREATE ROLE security CREATEROLE;
It is easier to manage roles as a group so that you can grant or revoke privileges from a group as a whole. In PostgreSQL, you create a role that represents a group, and then grant membership in the group role to individual user roles.
By convention, a group role does not have
To create a group role, you use the
CREATE ROLE statement as follows:
CREATE ROLE group_role;
For example, the following statement creates sales group role:
CREATE ROLE sales;
Now, you can add a user role to a group role by using the
GRANT group_role to user_role;
For example, to add the doe user role to the sales group role, you use the following statement:
GRANT sales TO doe;
To remove a user role from a group role, you use
REVOKE group_role FROM user_role;
For example, to remove doe user role from the sales group role, you use the following statement:
REVOKE sales FROM doe;
Notice that PostgreSQL does not allow you to have circular membership loops, in which a role is the member of another role and vice versa.
Group and user role inheritance
A user role can use privileges of the group role in the following ways:
- First, a user role can use the
SET ROLEstatement to temporarily become the group role, which means the user role use privileges of the group role rather than the original privileges. In addition, any database objects created in the session are owned by the group role, instead of the user role.
- Second, a user role that has the
INHERITattribute will automatically have the privileges of the group roles of which it is a member, including all privileges inherited by the group roles.
See the following example:
CREATE ROLE doe LOGIN INHERIT;
CREATE ROLE sales NOINHERIT;
CREATE ROLE marketing NOINHERIT;
GRANT sales to doe;
GRANT marketing to sales;
If you connect to PostgreSQL as doe, you will have privileges of doe plus privileges granted to sales, because doe user role has the
INHERIT attribute. However, you do not have privileges of marketing because the
NOINHERIT attribute is defined for the sales user role.
After executing the following statement:
SET ROLE sales;
You will have only privileges granted to sales, not the ones that granted to doe.
And after executing the following statement:
SET ROLE marketing;
You only have privileges granted to marketing, not the ones that granted to admin and doe.
To restore the original privilege, you can use the following statement:
Notice that only privileges on the database object are inheritable. The
LOGIN, SUPERUSER, CREATEROLE, and
CREATEDB are the special role that cannot be inherited as an ordinary privilege.
You can use the
DROP ROLE statement to remove a group role or user role.
DROP ROLE role_name;
Before removing a role, you must reassign or remove all objects it owns and revoke its privileges.
If you remove a group role, PostgreSQL revokes all memberships in a group automatically. The user roles of the group are not affected.