Earlier in this lesson, we mentioned the fact that all roles in PostgreSQL are either login or group roles. In PostgreSQL, any role can be assigned to be a member of another role. For example,
charlie can each be login roles, and they can also all be members of a group role called
employees. As members of a group role, these accounts can inherit certain permissions from
employees. If a superuser granted
SELECT on a table to
charlie would also be able to
SELECT on this table because they’re “members” of
This is a useful feature for maintaining databases with many users, but only a few “types” of users. You could have a DB with hundreds of users, but if their permissions are managed through just a few group roles, the job of maintaining permissions is far simpler.
However, members in a group don’t necessarily all share the exact same permissions. Consider the example below,
bob are both members of
employees. This grants them all the permissions granted to
sales) are also both members of additional group roles. Because they’re members of these group roles, they also have all the permissions granted to these roles. Finally, they also have permissions granted only to their login role.
You can see a larger version of that second image here.
There are some “gotchas” with role inheritance that you should be aware of. For security reasons, PostgreSQL disallows the inheritance of certain powerful permissions such as
CREATEROLE. This prevents a developer from accidentally granting high-level permissions to a wide group of users.
There are several ways to create a new group role:
CREATE ROLEand the
WITH ROLEoption when creating a role — this automatically adds the listed names to the role.
CREATE ROLE marketing WITH NOLOGIN ROLE alice, bob;
CREATE ROLEand a
GRANTstatement — this grants all the permissions of the newly created role to the listed names.
CREATE ROLE finance WITH NOLOGIN; GRANT finance TO charlie;
You can also add users to group(s) on creation by specifying
IN ROLE along with the
CREATE ROLE statement. For example:
CREATE ROLE fran WITH LOGIN IN ROLE employees, managers;
Create a group role called
pgdba (Postgres Database Administrator) with
NOLOGIN permissions. For the moment, do not include any roles within the group role.
Create a user named
david should be added as a member of two (already existing) groups.
pgdba- The group you created in the previous exercise
employees- A group that already exists on the DB.
Using the PostgreSQL internal tables, confirm that
david doesn’t have superuser permissions. Write a
SELECT statement that returns
Let’s confirm that
david has inherited permissions from
employees has access to
SELECT from a table named
company_startdates. Do you expect
david to have access to this table? Run the SQL below to test your hypothesis.
SET ROLE david; SELECT * FROM cc_user.company_startdates;