Hands-on with PostgreSQL Authorization - Part 2 - Row-Level Security

Welcome to another exploration of PostgreSQL’s authorization tools! If you’re interested in why you might want to learn about or use PostgreSQL authorization, check out [the first couple sections of the first article](https://www.tangramvision.com/blog/hands-on-with-postgresql-authorization-part-1-roles-and-grants). Here’s where we are in the series:

1. [Roles and Grants](https://www.tangramvision.com/blog/hands-on-with-postgresql-authorization-part-1-roles-and-grants)
2. Row-Level Security (this article)
3. Performance of Row-Level Security (coming soon!)

In the [first article](https://www.tangramvision.com/blog/hands-on-with-postgresql-authorization-part-1-roles-and-grants), we looked into how database roles and granted privileges can be used to control what database objects (e.g. tables, views, functions) can be accessed and modified (with SELECT, INSERT, UPDATE, and DELETE queries) by different users. That article ended with a bit of a cliffhanger: if you build a multi-user application using only roles and GRANTs for authorization, then your users will be able to delete each others’ data or even delete each other! We need another mechanism to limit users to reading and mutating only their own data — that mechanism is [row-level security (RLS) policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html).

In this article, we’ll use an example-driven approach to dive into the basics of understanding and using RLS policies.

## Hands-On With an Example Schema

The best way to understand RLS is to use it! We’ll continue using the previous article’s database schema, roles, and privilege grants (with the addition of a new “songs” table) — we’re modeling an example app akin to [Bandcamp](https://bandcamp.com/), where musical artists can publish albums and songs and fans can discover and follow artists.

*Sample schema relating musical artists, albums, songs, and followers (fans).*

You can follow along using Docker and the [schema](https://gitlab.com/tangram-vision/oss/tangram-visions-blog/-/blob/main/2022.03.16_PostgreSQLAuthorizationRowLevelSecurity/schema.sql) at: [https://gitlab.com/tangram-vision/oss/tangram-visions-blog/-/tree/main/2022.03.16_PostgreSQLAuthorizationRowLevelSecurity](https://gitlab.com/tangram-vision/oss/tangram-visions-blog/-/tree/main/2022.03.16_PostgreSQLAuthorizationRowLevelSecurity)

As described in the repo's README, you can run the command below, which uses the [official Postgres Docker image](https://hub.docker.com/_/postgres) to run a PostgreSQL database locally. The first volume mount will load the `schema.sql` file, which will populate your database with the tables diagrammed above.

```bash
docker run --name=postgres \
   --rm \
   --volume=$(pwd)/schema.sql:/docker-entrypoint-initdb.d/schema.sql \
   --volume=$(pwd):/repo \
   --env=PSQLRC=/repo/.psqlrc \
   --env=POSTGRES_PASSWORD=foo \
   postgres:latest -c log_statement=all
```

To open a `psql` prompt in that container, run the following in another terminal:

```bash
docker exec --interactive --tty postgres \
   psql --username=postgres
```

## Row-Level Security

What is [Row-Level Security](https://www.postgresql.org/docs/current/ddl-rowsecurity.html) (RLS)? It’s a way for PostgreSQL to limit what *rows* of a table are visible to a query. Typically, if you `SELECT * FROM mytable` then PostgreSQL will return all the columns and all the rows from the table. With Row-Level Security enabled on the table, PostgreSQL won’t return *any* rows (unless you’re querying from a database role that is a superuser, the owner of the table, or has the [BYPASSRLS option](https://www.postgresql.org/docs/current/sql-createrole.html)).

### Basic Policies

In order to return rows from a table with RLS enabled, we need to write a [policy](https://www.postgresql.org/docs/current/sql-createpolicy.html). Policies use an SQL expression (returning a boolean result) that is evaluated for every row in the table, which determines which rows are accessible to the user submitting the query. Policies can apply to specific roles and/or specific commands (e.g. SELECT, INSERT, ...).

As a first, simple example, let’s add some data to our database and enable row-level security on a table:

```sql
-- Add 3 musical artists
=# INSERT INTO artists (name)
    VALUES ('Tupper Ware Remix Party'), ('Steely Dan'), ('Missy Elliott');

-- Switch to the artist role (so we're not querying from a superuser role, which
-- bypasses RLS)
=# SET ROLE artist;

=> SELECT * FROM artists;
--  artist_id |     name      
-- -----------+---------------
--          1 | Tupper Ware Remix Party
--          2 | Steely Dan
--          3 | Missy Elliott
-- (3 rows)

-- Switch to the postgres superuser to enable RLS on the artists table
=> RESET ROLE;
=# ALTER TABLE artists ENABLE ROW LEVEL SECURITY;

-- Now we don't see any rows! RLS hides all rows if no policies are declared on
-- the table.
=# SET ROLE artist;
=> SELECT * FROM artists;
--  artist_id | name
-- -----------+------
-- (0 rows)
```

Now, let’s try out a couple basic RLS policies:

```sql
-- Let's create a simple RLS policy that applies to all roles and commands and
-- allows access to all rows.
=> RESET ROLE;
=# CREATE POLICY testing ON artists
   USING (true);

-- The expression "true" is true for all rows, so all rows are visible.
=# SET ROLE artist;
=> SELECT * FROM artists;
--  artist_id |          name
-- -----------+-------------------------
--          1 | Tupper Ware Remix Party
--          2 | Steely Dan
--          3 | Missy Elliott
-- (3 rows)

-- Let's change the policy to use an expression that depends on a value in the
-- row.
=> RESET ROLE;
=# ALTER POLICY testing ON artists
   USING (name = 'Steely Dan');

-- Now, we see that only 1 row passes the policy's test.
=# SET ROLE artist;
=> SELECT * FROM artists;
--  artist_id |    name
-- -----------+------------
--          2 | Steely Dan
-- (1 row)
```

### Policies Based on the Querying User

Let’s get a little more realistic: we want artists to be able to change their own name and be unable to change other artists’ names. To model this situation, we need to know the artist identity that is querying the database — issuing queries from a generic “artist” role (as in the above examples) doesn’t give us enough information. One way to assume a specific identity within the “artist” role/group is to create another database role and make it a member of the “artist” role:

```sql
=> RESET ROLE;
-- Create a login/role for a specific artist. We'll design the role name to be
-- "artist:N" where N is the artist_id. So, "artist:1" will be the account for
-- Tupper Ware Remix Party.
-- NOTE: We have to quote the role name because it contains a colon.
=# CREATE ROLE "artist:1" LOGIN;
=# GRANT artist TO "artist:1";
```

Now, if you log into the database as “artist:1”, then you’ll have all the same privileges as the “artist” group. By using specific, single-user logins (and corresponding usernames), we can write a policy that uses the user name to identify which rows in the database belong to that user.

```sql
-- Let's make all artists visible to all users again
=# DROP POLICY testing;
=# CREATE POLICY viewable_by_all ON artists
   FOR SELECT
   USING (true);

-- We create an RLS policy specific to the "artist" role/group and the UPDATE
-- command. The policy makes rows from the "artists" table available if the
-- row's artist_id matches the number in the current user's name (i.e.
-- a db role name of "artist:1" makes the row with artist_id=1 available).
=# CREATE POLICY update_self ON artists
   FOR UPDATE
   TO artist
   USING (artist_id = substr(current_user, 8)::int);

=# SET ROLE "artist:1";
-- Even though we try to update the name for all artists in the table, the RLS
-- policy limits our update to only the row we "own" (i.e. that has an artist_id
-- matching our db role name).
=> UPDATE artists SET name = 'TWRP';
-- UPDATE 1
=> SELECT * FROM artists;
--  artist_id |     name
-- -----------+---------------
--          2 | Steely Dan
--          3 | Missy Elliott
--          1 | TWRP
-- (3 rows)

-- Trying to update a row that no policy gives us access to simply results in no
-- rows updating.
=> UPDATE artists SET name = 'Ella Fitzgerald' WHERE name = 'Steely Dan';
-- UPDATE 0
```

We successfully implemented permissions that allow an artist to update their name and nobody else’s! This example also introduced:

- Limiting a policy to a specific command (e.g. SELECT, UPDATE)
- Limiting a policy to a specific role/group (e.g. artist)

By default, policies apply to all commands and to all roles. If a query is issued and there is no policy with matching command and role, then no policies apply and no rows will be visible or affected by the query.

> 💡 You may see that `artist_id = substr(current_user, 8)::int` expression in the update policy and frown a bit or raise an eyebrow. If so, good! This example was written to be (hopefully) easy to follow, but in a real app you probably wouldn’t want to use usernames that are a string-concatenation of the role/group name with an ID, because then you have tightly-coupled pieces of data that require string operations to extract every time you need them! A better approach might be to log in with a UUID username, which keys into a “users” table that “fans” and “artists” tables join to. There are a few different ways to design database usernames and tie them into RLS policies, which we may explore in a future article.

### Policies That Span Tables

Let’s take it one step further! We want artists to be able to create/edit/delete albums (but only their own!) and create/edit/delete songs on those albums. The USING expression in RLS policies can contain any SQL expression, so we can use foreign-key relationships in our data model to span tables when checking permissions.

```sql
=> RESET ROLE;
-- Enable RLS on albums and songs, and make them viewable by everyone.
=# ALTER TABLE albums ENABLE ROW LEVEL SECURITY;
=# ALTER TABLE songs ENABLE ROW LEVEL SECURITY;
=# CREATE POLICY viewable_by_all ON albums
   FOR SELECT
   USING (true);
=# CREATE POLICY viewable_by_all ON songs
   FOR SELECT
   USING (true);

-- Limit create/edit/delete of albums to the "owning" artist.
=# CREATE POLICY affect_own_albums ON albums
   FOR ALL
   TO artist
   USING (artist_id = substr(current_user, 8)::int);
-- Limit create/edit/delete of songs to the "owning" artist of the album.
=# CREATE POLICY affect_own_songs ON songs
   FOR ALL
   TO artist
   USING (
       EXISTS (
           SELECT 1 FROM albums
           WHERE albums.album_id = songs.album_id
            AND albums.artist_id = substr(current_user, 8)::int
       )
   );

-- Add a Missy Elliott (artist_id=3) album (album_id=1) for testing below
=# INSERT INTO albums (artist_id, title, released)
   VALUES (3, 'Under Construction', '2002-11-12');

-- Change to the user account corresponding to the artist TWRP (artist_id=1)
=# SET ROLE "artist:1";
-- Add an album and a song to that album
=> INSERT INTO albums (artist_id, title, released)
   VALUES (1, 'Return to Wherever', '2019-07-11');
=> INSERT INTO songs (album_id, title)
   VALUES (2, 'Hidden Potential');

-- Trying to add an album to another artist fails the RLS policy
=> INSERT INTO albums (artist_id, title, released)
   VALUES (2, 'Pretzel Logic', '1974-02-20');
-- ERROR:  42501: new row violates row-level security policy for table "albums"
-- LOCATION:  ExecWithCheckOptions, execMain.c:2058

-- Trying to add a song to Missy Elliott's album fails the RLS policy
=> INSERT INTO songs (album_id, title)
   VALUES (1, 'Work It');
-- ERROR:  42501: new row violates row-level security policy for table "songs"
-- LOCATION:  ExecWithCheckOptions, execMain.c:2058
```

The notable piece of the above is the policy on the “songs” table. When we issue an INSERT, UPDATE, or DELETE command on that table, the RLS policy ensures that we can only insert, modify, or delete songs in an album associated with the artist issuing the query.

We used an EXISTS subquery for the USING expression in the “songs” table’s policy above, but there are a variety of ways that permission could be implemented. Which way is most performant? Great question! We’ll dig deeper into that in the “Performance of Row-Level Security” article later in this series (make sure to [subscribe to our newsletter](https://buildwith.tangramrobotics.com/newsletter) or [blog RSS](https://www.tangramvision.com/blog/rss.xml) to get notified).

### Interactions of Multiple Policies

We’ve seen that policies can apply:

- to all commands (the default) or specific commands (SELECT, INSERT, UPDATE, DELETE)
- to all users (the default) or to specific users (e.g. artist, fan)

but one more critical aspect of how RLS policies work is how multiple policies can be combined. Policies can interact in two main ways:

- A table can have multiple policies
- A policy can query another table that has its own policy

**Tables with Multiple Policies**

When a table has multiple policies, pay attention to whether the policies are declared as PERMISSIVE (the default) or RESTRICTIVE. A PERMISSIVE policy must exist on a table for any rows to be accessible. If there are multiple PERMISSIVE policies, a row is accessible if *any* PERMISSIVE policy evaluates to true. On the other hand, RESTRICTIVE policies must *all* evaluate to true for a row to be accessible. We can explore this behavior by adding a new feature to our sample app — we want to allow artists to create albums with a future release date, but only the owning artist should be able to see these not-yet-released albums.

```sql
=> RESET ROLE;
-- Reminder: We previously created a viewable_by_all policy on albums that shows
-- all rows to SELECT queries issued by all roles. We re-create that policy here
-- for reference:
=# DROP POLICY viewable_by_all ON albums;
=# CREATE POLICY viewable_by_all ON albums
   FOR SELECT
   USING (true);

-- For fans: restrict visibility to albums with a release date in the past.
=# CREATE POLICY hide_unreleased_from_fans ON albums
   AS RESTRICTIVE
   FOR SELECT
   TO fan
   USING (released <= now());

-- For artists: restrict visibility to albums with a release date in the past,
-- unless the role issuing the query is the owning artist.
=# CREATE POLICY hide_unreleased_from_other_artists ON albums
   AS RESTRICTIVE
   FOR SELECT
   TO artist
   USING (released <= now() or (artist_id = substr(current_user, 8)::int));
```

By combining a PERMISSIVE policy and RESTRICTIVE policies targeting different roles (fans and artists), we’ve made future-release albums visible only to the owning artist. Arguably, a better way to represent this logic would be to use only PERMISSIVE policies, as follows:

```sql
-- Alternate implementation using only PERMISSIVE (rather than RESTRICTIVE)
-- policies.
=# DROP POLICY viewable_by_all ON albums;
=# DROP POLICY hide_unreleased_from_fans ON albums;
=# DROP POLICY hide_unreleased_from_other_artists ON albums;
=# CREATE POLICY viewable_by_all ON albums
   FOR SELECT
   USING (released <= now());

-- Reminder: We previously created an affect_own_albums policy on albums that
-- already allows the artist to see their own albums. We re-create that policy
-- here for reference:
=# DROP POLICY affect_own_albums ON albums;
=# CREATE POLICY affect_own_albums ON albums
   -- FOR ALL
   TO artist
   USING (artist_id = substr(current_user, 8)::int);
```

Now, the `viewable_by_all` policy lets everyone see albums with a release date in the past, and the `affect_own_albums` policy lets artists do anything (SELECT, INSERT, etc.) to albums they own.

**Querying Other Tables with Their Own Policies**

The other way that multiple policies can interact is when a policy’s USING expression queries another table that has its own policy. In our sample app, we can use the policy on the albums table to determine whether the songs in that album should be visible:

```sql
-- To control visibility of songs, we simply query for the corresponding album
-- and the RLS policy on the albums table will determine if we can see the
-- album. If we see the album, we'll see the songs.
=# DROP POLICY viewable_by_all ON songs;
=# CREATE POLICY viewable_by_all ON songs
   FOR SELECT
   USING (
       EXISTS (
           SELECT 1 FROM albums
           WHERE albums.album_id = songs.album_id
       )
   );
```

Finally, let’s test out our new policies to make sure that the correct roles/groups see (or cannot see!) albums with future release dates and their songs.

```sql
-- Create another artist role for testing
=# CREATE ROLE "artist:2";
=# GRANT artist TO "artist:2";

-- Test that the owning artist (artist:1) can see future albums and songs, but
-- other artists and fans cannot see them.
=# SET ROLE "artist:1";
=> SELECT * FROM albums;
--  album_id | artist_id |       title        |  released  
-- ----------+-----------+--------------------+------------
--         1 |         3 | Under Construction | 2002-11-12
--         2 |         1 | Return to Wherever | 2019-07-11
--         4 |         1 | Future Album       | 2050-01-01
-- (3 rows)

=> SELECT * FROM songs;
--  song_id | album_id |      title      
-- ---------+----------+------------------
--        1 |        2 | Hidden Potential
--        3 |        4 | Future Song 1
-- (2 rows)

=> SET ROLE fan;
=> SELECT * FROM albums;
--  album_id | artist_id |       title        |  released
-- ----------+-----------+--------------------+------------
--         1 |         3 | Under Construction | 2002-11-12
--         2 |         1 | Return to Wherever | 2019-07-11
-- (2 rows)

=> SELECT * FROM songs;
--  song_id | album_id |      title
-- ---------+----------+------------------
--        1 |        2 | Hidden Potential
-- (1 row)

=> SET ROLE "artist:2";
=> SELECT * FROM albums;
--  album_id | artist_id |       title        |  released
-- ----------+-----------+--------------------+------------
--         1 |         3 | Under Construction | 2002-11-12
--         2 |         1 | Return to Wherever | 2019-07-11
-- (2 rows)

=> SELECT * FROM songs;
--  song_id | album_id |      title
-- ---------+----------+------------------
--        1 |        2 | Hidden Potential
-- (1 row)
```

Success! We’ll conclude this overview of Row-Level Security in PostgreSQL here; there’s lots more to learn (check out the docs on [Row Security Policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html) and the [CREATE POLICY](https://www.postgresql.org/docs/current/sql-createpolicy.html) command), but we’ve covered the basics. So far, I’ve left a big question unanswered: what are the performance implications of using policies, especially when they contain complex USING expressions (e.g. querying other tables, using joins, calling functions)? We’ll dig into that question in the next article!

---

Thanks for reading! I hope you learned something new and will return for the next article in the series. If you have any corrections to this article or suggestions/questions for future articles, please [let me know](mailto:greg.schafer@tangramvision.com) or [send us a tweet](https://www.twitter.com/tangramvision), and if you’re curious to learn more about how we improve perception sensors, visit us at [Tangram Vision](https://www.tangramvision.com/).

‍