Who can see what: roles and row-level security

By the end you'll know

What a role is, and why connecting as the superuser is a risk
How GRANT gives a role exactly the access it needs, down to single columns
How group roles share access, and how to read \du and \dp
How row-level security limits which rows a role can see and change
Why the superuser and table owners skip those limits, and what that means for testing

Before you start

You need: Lessons 1 to 10 done, with the larkspur container running.
Where commands run: inside psql as usual: docker exec -it larkspur psql -U postgres -d larkspur. Two tests at the end run from your terminal.
Heads up: roles belong to the whole Postgres server, not one database, so reloading the county doesn't remove them. The lesson ends with a cleanup; run it before trying again.

The keys to everything

Ask the database who you are:

SQL

SELECT current_user, session_user;
\du

OUTPUT

 current_user | session_user 
--------------+--------------
 postgres     | postgres
(1 row)

                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

postgres is a superuser: it can read, change and drop anything, create and delete other users, and skip every security rule (Bypass RLS, which this lesson gets to). That's fine for you, learning, in a container on your own laptop. It's a bad idea for a program. An API connected as the superuser that has one bug, one injected query (Lesson 12), or one leaked password gives away the whole database, not just the part it needed.

The fix is least privilege: every program gets its own role, and that role gets exactly the access its job needs. Think of keys in an office building. The cleaner has a key to every floor but not the safe. The accountant has a key to the finance office. Only the building manager has the master key, and keeps it in a drawer. If someone steals the cleaner's key, the safe is still safe.

A role for the API

Say Larkspur County has a website, and its API needs to show towns, leaders and meetings, and let staff correct a town's phone number or website. Nothing else. Make a role for each part of that:

SQL

CREATE ROLE reader NOLOGIN;
GRANT SELECT ON towns, leaders, meetings TO reader;
CREATE ROLE larkspur_api LOGIN PASSWORD 'practice-only' IN ROLE reader;
GRANT UPDATE (phone, website) ON towns TO larkspur_api;

OUTPUT

CREATE ROLE
GRANT
CREATE ROLE
GRANT

In Postgres, users and groups are the same thing: a role. What differs is whether it can log in.

reader is a group role: NOLOGIN, so nobody connects as it. It exists to hold a set of permissions, here SELECT (read) on the three tables.
larkspur_api can log in, with a password, and IN ROLE reader makes it a member of reader, so it gets everything reader has. Add a second program that only reads, and it joins reader too: one place to change what "reading" means.
GRANT UPDATE (phone, website) is a column privilege: the API may change those two columns of towns, and no others.

\du now shows three roles:

OUTPUT

                               List of roles
  Role name   |                         Attributes                         
--------------+------------------------------------------------------------
 larkspur_api | 
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS
 reader       | Cannot login

larkspur_api has no special attributes, which is exactly what you want. And \dp towns shows who may do what to the table:

OUTPUT

                                     Access privileges
 Schema | Name  | Type  |     Access privileges     |     Column privileges     | Policies 
--------+-------+-------+---------------------------+---------------------------+----------
 public | towns | table | postgres=arwdDxt/postgres+| website:                 +| 
        |       |       | reader=r/postgres         |   larkspur_api=w/postgres+| 
        |       |       |                           | phone:                   +| 
        |       |       |                           |   larkspur_api=w/postgres | 
(1 row)

Each entry reads who = what / granted by whom, with one letter per privilege: r read (SELECT), w write (UPDATE), a append (INSERT), d delete, and a few more. The owner, postgres, has them all (arwdDxt). reader has r. larkspur_api has w, on two columns only. (The + just means the cell continues on the next line.)

Being the API

A superuser can switch to another role with SET ROLE, to test exactly what it can do:

SQL

SET ROLE larkspur_api;
SELECT current_user, session_user;

OUTPUT

SET
 current_user | session_user 
--------------+--------------
 larkspur_api | postgres
(1 row)

session_user is who connected; current_user is whose permissions apply now. What it's allowed:

SQL

SELECT count(*) FROM towns;
UPDATE towns SET website = 'https://cedarhollow.larkspur.us' WHERE id = 4 RETURNING id, name, website;

OUTPUT

 count 
-------
    12
(1 row)

 id |     name     |             website             
----+--------------+---------------------------------
  4 | Cedar Hollow | https://cedarhollow.larkspur.us
(1 row)

UPDATE 1

Reading works through reader, and the website update is one of its two columns. Now everything else. Another column:

SQL

UPDATE towns SET population = 9000 WHERE id = 4;

OUTPUT

ERROR:  permission denied for table towns

Deleting:

SQL

DELETE FROM meetings WHERE id = 1;

OUTPUT

ERROR:  permission denied for table meetings

Dropping a table:

SQL

DROP TABLE meetings;

OUTPUT

ERROR:  must be owner of table meetings

Dropping isn't something you can grant at all. Only a table's owner, the role that created it, can drop or change its shape.

Making a new table:

SQL

CREATE TABLE sneaky (x integer);

OUTPUT

ERROR:  permission denied for schema public
LINE 1: CREATE TABLE sneaky (x integer);
                     ^

A schema is a folder of tables inside a database; public is the one everything so far has gone in. Since Postgres 15, ordinary roles can't create things in it unless they're given that right. Older versions let anyone, which is worth knowing if you meet one.

And the checks view from Lesson 10:

SQL

SELECT count(*) FROM problems;

OUTPUT

ERROR:  permission denied for view problems

Nobody granted it, so the API can't read it. Nothing is allowed unless it's granted. Go back to being yourself:

SQL

RESET ROLE;

Row-level security

Grants decide which tables and columns a role can touch. Sometimes that's not enough. Say each town has a clerk who records its meetings. Every clerk needs to read and add meetings, so they all need SELECT and INSERT on meetings. But Ashford's clerk shouldn't see, or add, Fairview's.

Row-level security (RLS) adds a filter to a table that the database applies to every query, for the roles it covers. It's the difference between a key to the filing room and a key to one drawer.

SQL

CREATE ROLE clerk NOLOGIN;
GRANT SELECT, INSERT ON meetings TO clerk;
ALTER TABLE meetings ENABLE ROW LEVEL SECURITY;
CREATE POLICY own_town ON meetings FOR ALL TO clerk
    USING (town_id = nullif(current_setting('app.town_id', true), '')::integer)
    WITH CHECK (town_id = nullif(current_setting('app.town_id', true), '')::integer);

OUTPUT

CREATE ROLE
GRANT
ALTER TABLE
CREATE POLICY

ENABLE ROW LEVEL SECURITY switches it on for the table. From now on, a role with no policy that applies to it sees no rows at all.
CREATE POLICY own_town ... TO clerk is the rule for clerks. USING decides which existing rows they can see (and update or delete, if they could); WITH CHECK decides which new or changed rows they're allowed to write.
current_setting('app.town_id', true) reads a setting the application sets for each connection: which town this clerk works for. The true means "NULL if it isn't set", instead of an error. nullif(..., '') is Lesson 8's guard: a setting that's been reset comes back as empty text, not NULL.

This is how real apps do it. One role for all clerks, and the app tells the database who's asking. Be a clerk who hasn't said which town yet:

SQL

SET ROLE clerk;
SELECT count(*) FROM meetings;

OUTPUT

SET
 count 
-------
     0
(1 row)

Nothing. With no town set, town_id = NULL is unknown for every row, and the policy shows none. That's the right way to fail: closed, showing nothing, rather than everything. Now set the town to Brennan Falls:

SQL

SET app.town_id = '3';
SELECT id, town_id, held_on, body FROM meetings ORDER BY held_on;

OUTPUT

SET
 id | town_id |  held_on   |   body   
----+---------+------------+----------
  7 |       3 | 2026-01-06 | council
  8 |       3 | 2026-02-03 | council
  9 |       3 | 2026-02-17 | planning
 10 |       3 | 2026-03-03 | council
(4 rows)

Brennan Falls' four meetings, and nothing else. The query asked for every meeting; it didn't say WHERE town_id = 3. The database added that itself. Add a meeting for this town:

SQL

INSERT INTO meetings (id, town_id, held_on, body) VALUES (31, 3, '2026-04-07', 'council')
RETURNING id, town_id, held_on;

OUTPUT

 id | town_id |  held_on   
----+---------+------------
 31 |       3 | 2026-04-07
(1 row)

INSERT 0 1

And one for Fairview:

SQL

INSERT INTO meetings (id, town_id, held_on, body) VALUES (32, 7, '2026-04-07', 'council');

OUTPUT

ERROR:  new row violates row-level security policy for table "meetings"

Refused by WITH CHECK. Switch to Fairview's clerk, and Brennan Falls' new meeting can't be seen at all:

SQL

SET app.town_id = '7';
SELECT count(*) FROM meetings;
SELECT * FROM meetings WHERE id = 31;

OUTPUT

SET
 count 
-------
     4
(1 row)

 id | town_id | held_on | body | attendance | minutes_url 
----+---------+---------+------+------------+-------------
(0 rows)

Fairview's four meetings, and meeting 31 might as well not exist. Not "permission denied": just no rows. Unset the town, and it's back to nothing:

SQL

RESET app.town_id;
SELECT count(*) FROM meetings;

OUTPUT

RESET
 count 
-------
     0
(1 row)

That's the nullif working: after RESET, the setting is empty text, nullif makes it NULL, and the policy fails closed again. Without it, ''::integer would have been an error, and a confusing one.

Who skips the rules

Back to postgres:

SQL

RESET ROLE;
SELECT count(*) FROM meetings;

OUTPUT

RESET
 count 
-------
    31
(1 row)

All 31, the 30 originals and the one Brennan Falls' clerk added. The superuser skips row-level security: that's the Bypass RLS in \du. So does the table's owner, unless you say otherwise with ALTER TABLE meetings FORCE ROW LEVEL SECURITY. Here, postgres is both.

That matters for testing. If you test your policies as the superuser or the owner, they'll look like they do nothing, because for you, they don't. Test as the role the application uses, with SET ROLE, the way this lesson did.

\d meetings now lists the policy:

OUTPUT

Policies:
    POLICY "own_town"
      TO clerk
      USING ((town_id = (NULLIF(current_setting('app.town_id'::text, true), ''::text))::integer))
      WITH CHECK ((town_id = (NULLIF(current_setting('app.town_id'::text, true), ''::text))::integer))

And here's every role's standing, from the pg_roles catalog:

SQL

SELECT rolname, rolsuper, rolcanlogin, rolbypassrls FROM pg_roles
WHERE rolname IN ('postgres', 'reader', 'larkspur_api', 'clerk') ORDER BY rolname;

OUTPUT

   rolname    | rolsuper | rolcanlogin | rolbypassrls 
--------------+----------+-------------+--------------
 clerk        | f        | f           | f
 larkspur_api | f        | t           | f
 postgres     | t        | t           | t
 reader       | f        | f           | f
(4 rows)

Connecting as the role

SET ROLE is for testing. A real program connects as its role, from the start. From your terminal:

BASH

docker exec larkspur psql -U larkspur_api -d larkspur -c "SELECT current_user, count(*) FROM towns;"
docker exec larkspur psql -U larkspur_api -d larkspur -c "DELETE FROM leaders WHERE id = 1;"

OUTPUT

 current_user | count 
--------------+-------
 larkspur_api |    12
(1 row)

ERROR:  permission denied for table leaders

Connected as the API, it reads, and it can't delete. It didn't ask for the password: the Postgres image trusts connections made from inside its own container. A connection from anywhere else, like an app in another container, has to give it. Which connections must give a password, and how, is set in Postgres's pg_hba.conf file.

And the clerk role can't connect at all, because it's NOLOGIN:

BASH

docker exec larkspur psql -U clerk -d larkspur -c "SELECT 1;"

OUTPUT

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "clerk" is not permitted to log in

In a real app, the login role would be a member of clerk, the same way larkspur_api is a member of reader.

Cleaning up

Roles outlive the database, so undo it all by hand. Back inside psql, as postgres:

SQL

DROP POLICY own_town ON meetings;
ALTER TABLE meetings DISABLE ROW LEVEL SECURITY;
DELETE FROM meetings WHERE id = 31;
UPDATE towns SET website = 'http://cedarhollow.larkspur.us' WHERE id = 4;
DROP OWNED BY larkspur_api, clerk, reader;
DROP ROLE larkspur_api, clerk, reader;

OUTPUT

DROP POLICY
ALTER TABLE
DELETE 1
UPDATE 1
DROP OWNED
DROP ROLE

A role can't be dropped while it still has privileges on anything. DROP OWNED BY removes everything the roles own and every privilege they hold in this database, so DROP ROLE can go ahead. \du is back to one role:

OUTPUT

                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

In real systems

Every program gets its own role, with only the grants it needs. The API reads and writes data; a migration tool changes tables; a backup job reads everything; a reporting tool reads a few views. If one is compromised, the damage stops at its grants. The superuser's password is for people, rarely, and never goes in an app's settings.
Supabase is built on row-level security. Its data API lets a browser query the database directly, and the only thing standing between one user and another's rows is a policy, usually comparing a column with auth.uid(), the signed-in user's id. A table without RLS there is readable by anyone with the public key, which is why Supabase warns loudly about it.
Grants are part of the schema. They belong in the same migration files as the tables, so a new copy of the database gets the same roles and rules, not whatever someone typed by hand once.

Spot the problem

Work out what's wrong before you read the explanation.

The policy that worked in testing. A developer adds row-level security to meetings, with a policy so each clerk sees only their town. They test it by logging into psql the way they always do, as postgres, and run SELECT count(*) FROM meetings;. It says 30, every meeting. Puzzled, they decide the policy must be fine and the count is a fluke, and ship it. What did they actually test? Nothing. postgres is a superuser and the table's owner, and both skip row-level security, so the policy never applied to them. It might work, or it might show clerks everything; this test couldn't tell. Test as the role the app uses: SET ROLE clerk, set the town, and count. (FORCE ROW LEVEL SECURITY makes the owner follow the policies too, though a superuser still skips them.)

Keep this

To	Write
Who am I?	`SELECT current_user, session_user;`
List roles, and a table's privileges	`\du`, `\dp towns`
A group role	`CREATE ROLE reader NOLOGIN;`
A login role in a group	`CREATE ROLE larkspur_api LOGIN PASSWORD '...' IN ROLE reader;`
Allow reading	`GRANT SELECT ON towns, leaders TO reader;`
Allow changing some columns	`GRANT UPDATE (phone, website) ON towns TO larkspur_api;`
Take it back	`REVOKE UPDATE ON towns FROM larkspur_api;`
Test as another role	`SET ROLE larkspur_api;`, then `RESET ROLE;`
Turn on row-level security	`ALTER TABLE meetings ENABLE ROW LEVEL SECURITY;`
A policy	`CREATE POLICY own_town ON meetings FOR ALL TO clerk USING (...) WITH CHECK (...);`
A per-connection setting	`SET app.town_id = '3';`, `current_setting('app.town_id', true)`
Make the owner follow policies too	`ALTER TABLE meetings FORCE ROW LEVEL SECURITY;`
Remove roles	`DROP OWNED BY larkspur_api; DROP ROLE larkspur_api;`

Where you are now

You can give a program exactly the access it needs, read who has what, and limit a role to its own rows, with a way to test it that actually tests it. Lesson 12 is about the programs themselves: sending SQL from code, and the attack that happens when you build it out of strings.

Check yourself

Why shouldn't an application connect as the postgres superuser?
larkspur_api is IN ROLE reader. What does that give it?
The API can UPDATE towns' website, but UPDATE towns SET population fails. Why?
With RLS on and no town set, the clerk's SELECT count(*) FROM meetings says 0. Why?
Fairview's clerk queries meeting 31, which belongs to Brennan Falls. What happens?
Testing a policy as postgres shows all 30 meetings. What does that prove?

Key terms

Role: a user or a group in Postgres. Roles that can log in are users; NOLOGIN roles are groups.
Superuser: a role that can do anything and skips every permission check.
Least privilege: giving each program only the access its job needs.
GRANT and REVOKE: give and take away privileges on tables, columns, views and schemas.
Owner: the role that created an object. Only the owner can drop it or change its shape.
Schema: a folder of tables inside a database. public is the default.
Row-level security: a filter the database adds to every query on a table, for the roles a policy covers.
Policy: the rule for row-level security: USING for rows a role can see, WITH CHECK for rows it can write.

Go deeper

The PostgreSQL manual: Privileges (opens in a new tab), and Row Security Policies (opens in a new tab).
Try: a report_reader role that can read only the problems view from Lesson 10, and none of the tables behind it. Does the view still work for it? (It does: a view runs with its owner's privileges by default.)
Try: a policy so clerks can only UPDATE meetings from the last 30 days, using FOR UPDATE and current_date.