Pebellslearn by building
0%
RoadmapsMatrixCoursesSandboxSign in
Security  ›  Lesson

Roles

Roles 12 minPostgreSQL uses roles for both users and groups; login roles identify people and programs, group roles hold privileges, and attributes are not inherited.

Roles

Everyone at Pedal used to connect as postgres, the superuser. Then an intern's cleanup script dropped the payments table, and the logs could not even say who ran it. Access control in PostgreSQL starts with one idea: give every person and program its own role, with only the power it needs.

The rule

Everything is a role: users log in, groups hold privileges, nobody is superuser.

PostgreSQL has a single concept, the role. A role with the LOGIN attribute behaves like a user; a role without it behaves like a group. CREATE USER is just CREATE ROLE … LOGIN.

-- groups: no login, they only hold privileges
CREATE ROLE pedal_owner NOLOGIN;
CREATE ROLE analysts NOLOGIN;

-- logins: one per program and one per person
CREATE ROLE pedal_app LOGIN PASSWORD 'from-the-secret-store';
CREATE ROLE asha LOGIN PASSWORD 'asha-sets-her-own';

GRANT analysts TO asha;   -- asha is now a member of analysts

Key facts:

  • Roles are cluster-wide: one role can be granted access to several databases.
  • Attributes are powers of the role itself: LOGIN, SUPERUSER, CREATEDB, CREATEROLE, REPLICATION, BYPASSRLS. Attributes are not passed on through group membership.
  • Membership passes on privileges. With the default INHERIT, asha automatically has whatever analysts has been granted. A member can also SET ROLE analysts to act as the group, for example so new objects are owned by it.
  • Superuser skips every permission check. Keep it for the postgres role and emergencies.
  • Predefined roles cover common jobs without superuser: pg_monitor to read monitoring views, pg_read_all_data to read every table, pg_signal_backend to cancel other sessions' queries.
  • Passwords are stored as SCRAM-SHA-256 hashes by default.

\du in psql lists roles, their attributes and memberships.

The shortcut

For every person or program ask "Who is it, and what must it do?" Give it its own login role (the who), and put the what into a group role it joins. Privileges attach to groups, never directly to people.

Setting up access for a new analystStep 1 of 4

  1. Who is it?

    Ravi, a person: CREATE ROLE ravi LOGIN PASSWORD '…';

The trap is making the application's login role own the tables. Then a bug or an attacker using that login can drop them. Let a NOLOGIN owner role own objects, and give the app only data privileges.

Drills

Answer fast — trust the rule.

quick drill

What is the difference between CREATE USER and CREATE ROLE?

quick drill

asha is a member of a group role that has CREATEDB. Can asha create databases as herself?

quick drill

A monitoring tool must read pg_stat_activity for all sessions. What should its role get?

quick drill

Which design follows the rule for Pedal's app?

Remember

  • Everything is a role: users log in, groups hold privileges, nobody is superuser.
  • One login per person or program; privileges go on group roles.
  • Membership passes privileges, not attributes; a NOLOGIN role should own objects.

Practice — level up

0/1 solved

Write your answer, then compare it with a rubric and a model answer.

ExplainDesign Pedal's roles

Design the roles for Pedal: an owner of the tables, the web app, two analysts and a monitoring tool. Explain which roles can log in, which are groups, and why nobody except the postgres role is a superuser.