Learn

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, alice, bob, and 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 employees, alice, bob, and charlie would also be able to SELECT on this table because they’re “members” of employees.

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, alice and bob are both members of employees. This grants them all the permissions granted to employees. alice (marketing) and bob (marketing and 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.

A venn diagram showing Alice as part of employees and marketing, and Bob as a part of employees, marketing, and sales

A venn diagram showing that users can have permissions specific to themselves as well as permissions associated with the roles that they belong to

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 LOGIN, SUPERUSER, CREATEDB, and 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:

  • Using CREATE ROLE and the WITH ROLE option when creating a role — this automatically adds the listed names to the role.
CREATE ROLE marketing WITH NOLOGIN ROLE alice, bob;
  • Using CREATE ROLE and a GRANT statement — 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;

Instructions

1.

Create a group role called pgdba (Postgres Database Administrator) with SUPERUSER, CREATEDB, and NOLOGIN permissions. For the moment, do not include any roles within the group role.

2.

Create a user named david with LOGIN. david should be added as a member of two (already existing) groups.

  1. pgdba - The group you created in the previous exercise
  2. employees - A group that already exists on the DB.
3.

Using the PostgreSQL internal tables, confirm that david doesn’t have superuser permissions. Write a SELECT statement that returns rolname and rolsuper for david from pg_catalog.pg_roles.

4.

Let’s confirm that david has inherited permissions from employees. 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;

Take this course for free

Mini Info Outline Icon
By signing up for Codecademy, you agree to Codecademy's Terms of Service & Privacy Policy.

Or sign up using:

Already have an account?