Hands-on with PostgreSQL Authorization, Part 1: Roles and Grants

While it's controversial to put business logic in your database, I suspect it is not controversial to claim that it's important to understand the permissions and security of your database. If you neglect learning how your database handles authorization, then you probably aren't following the principle of least privilege — your database might be accessed by coworkers (e.g. developers, data scientists, marketers, accountants), contractors, continuous integration processes, or deployed services that have more privileges than they should, which increases the risk of data leaks, improper data access (e.g. of personal identifiable information), and accidental or malicious data corruption and data loss.

Despite its importance, database authorization was a weak area of knowledge for me early in my career. NoSQL was the cool kid on the block and the web development world was being seduced by frameworks like Rails, which provided a more shiny and polished developer experience than wrangling SQL scripts. But, the cycle of change continues and — with SQL and relational databases back in the limelight — it's important to learn how to use them securely and effectively. In this article series, I'll dive into the major areas of database authorization with a focus on PostgreSQL, because it is one of the most mature and capable open-source relational databases that currently exists. We'll investigate the following:

1. Roles and Grants (this article)

2. Row-Level Security

3. Performance of Row-Level Security

Nearly all the information I'll cover in this series exists in the PostgreSQL documentation, which is a fantastic resource but can be overwhelming in its detail and reference-like organization. I'll try to provide a more streamlined guide so you can quickly gain the know-how to build a project using PostgreSQL authorization.

Why Use PostgreSQL Authorization

Before diving into the how, let's step back and address why it can make sense to use PostgreSQL authorization.

For any application or website where users authenticate and can see different content or perform different actions than one another, you need authorization. When using frameworks like Rails or Django, the project documentation and community will generally lead you toward the simple solution of using a single database user with superuser (or read/write-anything) privileges. Authorization is then implemented as logic and rules in the Rails/Django codebase. If you add adjacent services that act on the same data (e.g. queues, background workers, cronjobs, data warehouses), you might need to duplicate some authorization logic in those services, or those services can reuse the authorization logic via shared libraries or direct inclusion in the original codebase (which can complicate development, deployment, and architectural and security boundaries). Furthermore, if a variety of services all access the database with a superuser account, the surface area for attacks and>Zanzibar instead. Building authorization with PostgreSQL is not a silver bullet, but it's an approach worth considering in the context of your particular project and team.

Hands-on With an Example Schema

Now that we understand why we might want to use PostgreSQL for authorization, how do we go about implementing it? Let's dig in by working on an example app akin to Bandcamp, where musical artists can publish albums and fans can discover and follow artists. We'll partially reuse the "musical artists and albums" schema from a previous PostgreSQL article on loading test data, but we'll adjust that schema to add another user type (fans) and remove "genre" tables to keep the schema simple and approachable.

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

You can follow along using Docker and the schema at: https://gitlab.com/tangram-vision/oss/tangram-visions-blog/-/tree/main/2021.12.13_PostgreSQLAuthorizationRolesAndGrants

As described in the repo's README, you can run the command below, which uses the official Postgres Docker image 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.

docker run --name=postgres \
    --rm \
    --volume=$(pwd)/schema.sql:/docker-entrypoint-initdb.d/schema.sql \<br> &nbsp; &nbsp;--volume=$(pwd):/repo \
    --env=PSQLRC=/repo/.psqlrc \
    --env=POSTGRES_PASSWORD=foo \
    postgres:latest -c log_statement

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

docker exec --interactive --tty postgres \
    psql --username

Roles

The first layer of any PostgreSQL authorization project is roles. Database roles can represent users and/or groups. A PostgreSQL database will typically start with a single superuser role named "postgres".

Let's run both the docker commands from the previous section in separate terminal windows to start a PostgreSQL database in a container and connect to it with psql. Notice in the second "docker exec" command, that we're connecting with the username "postgres"! To view the roles that exist in the database, run \du as below:

-- SQL comments (like this one) start with 2 hyphens (--).
-- I'll represent the psql prompt as
--   =# (when the current role is a superuser, i.e. "postgres") or
--   => (when the current role is not a superuser).
-- This corresponds to a psql PROMPT1 setting of '%R%# '. For more info about
-- psql prompts, see https://www.postgresql.org/docs/current/app-psql.html.

=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

To see the role we're using:

=# SELECT current_user, session_user;
 current_user | session_user 
--------------+--------------

💡 When you're testing privileges and policies, you'll probably be changing roles frequently. Unfortunately, you can't show the "current_user" in your psql prompt as far as I know (just the "session_user"). However, you can make it easier to run the above query by setting an alias in your .psqlrc file like \set whoami 'SELECT current_user, session_user;'. Then, you only need to type :whoami in psql to run the query. Thanks to this StackOverflow answer from wjv for the idea. For more psqlrc tips, check out this article.

The "session_user" is typically going to be the user you connected to the database as (though it can be changed with SET SESSION AUTHORIZATION ). The "current_user" is the user you're acting as — it is the user that will be tested against when evaluating privileges and policies. The current_user is changed by using SET ROLE and RESET ROLE (or running SECURITY DEFINER functions, but that's a topic for later!).

Let's create "artist" and "fan" roles for our example project and practice changing roles:

=# CREATE ROLE fan LOGIN;
=# CREATE ROLE artist LOGIN;

-- Running \du will now show 3 users

=# SET ROLE fan;
=> SELECT current_user, session_user;
 current_user | session_user 
--------------+--------------
 fan          | postgres

=> RESET ROLE;
=# SELECT current_user, session_user;
 current_user | session_user 
--------------+--------------
 postgres     | postgres

-- We can run the psql connect command (\c) to connect as a different user
=# \c - artist
You are now connected to database "postgres" as user "artist".

=> SELECT current_user, session_user;
current_user | session_user 
--------------+--------------
 artist       | artist

=> SET ROLE fan;
ERROR:  42501: permission denied to set role "fan"

From the above, we can see that non-superusers like "artist" cannot change to other roles. The exception is if a role is granted to another role, which is how database roles can act as groups — a "user" role can be granted a "group" role, thereby gaining the privileges that belong to the group. We won't dig deeper into using roles as groups but, as always, the relevant PostgreSQL documentation is enlightening.

From the "artist" role, let's see what artists exist:

=> SET ROLE artist;

=> SELECT * from artists;
ERROR:  42501: permission denied for table

We may be the "artist" role, but we haven't done anything to give the "artist" role access to the artists table in the database yet! That brings us to...

Privileges and Grants

Permission to act on database objects is governed by privileges, which are primarily manipulated using GRANT and REVOKE commands. We can inspect privileges with the \dp psql command:

=> \dp
                                      Access privileges
 Schema |         Name          |   Type   | Access privileges | Column privileges | Policies 
--------+-----------------------+----------+-------------------+-------------------+----------
 public | albums                | table    |                   |                   | 
 public | albums_album_id_seq   | sequence |                   |                   | 
 public | artists               | table    |                   |                   | 
 public | artists_artist_id_seq | sequence |                   |                   | 
 public | fan_follows           | table    |                   |                   | 
 public | fans                  | table    |                   |                   | 
 public

No privileges appear in the privileges columns, so no actions are allowed on these database objects (tables and sequences) unless performed by a superuser or the database object's owner. By listing the tables with \dt , we can see that "postgres" is the owner of all the tables:

=> \dt
            List of relations
 Schema |    Name     | Type  |  Owner   
--------+-------------+-------+----------
 public | albums      | table | postgres
 public | artists     | table | postgres
 public | fan_follows | table | postgres
 public | fans        | table

Let's allow the "artist" role to select from the artists table, then inspect privileges again:

-- Reconnect as the postgres superuser
=> \c - postgres
You are now connected to database "postgres" as user "postgres".
=# GRANT SELECT ON artists TO artist;

=# SET ROLE artist;
=> SELECT * FROM artists;
 artist_id | name 
-----------+------
(0 rows)

=> \dp
                                          Access privileges
 Schema |         Name          |   Type   |     Access privileges     | Column privileges | Policies 
--------+-----------------------+----------+---------------------------+-------------------+----------
 public | albums                | table    |                           |                   | 
 public | albums_album_id_seq   | sequence |                           |                   | 
 public | artists               | table    | postgres=arwdDxt/postgres+|                   | 
        |                       |          | artist=r/postgres         |                   | 
 public | artists_artist_id_seq | sequence |                           |                   | 
 public | fan_follows           | table    |                           |                   | 
 public | fans                  | table    |                           |                   | 
 public | fans_fan_id_seq       | sequence |                           |                   | 
(7 rows)

Now we see some privileges! The select (read) privilege that we granted to the "artist" role from the "postgres" role appears, along with the full set of possible privileges that are implicitly granted to the table owner (postgres). What are all these privileges and letters? The PostgreSQL docs have a handy table to answer that:

The first 7 rows are the privileges that are applicable to "table"-type database objects.

Lets continue with adding the rest of the privileges for our example app. In English, those privileges are:

• We want fans to be able to see their own data and to delete their account.

• We want fans to be able to see which artists they follow, and to follow and unfollow artists.

• We want fans to be able to see artists and albums.

• We want artists to be able to see their own data and to edit their name.

• We want artists to be able to create, edit, and delete albums.

Translating those into SQL privileges looks like:

=> RESET ROLE;
=# GRANT SELECT, DELETE ON fans to fan;
=# GRANT SELECT, INSERT, DELETE ON fan_follows TO fan;
=# GRANT SELECT ON artists TO fan;
=# GRANT SELECT ON albums TO fan;
=# GRANT SELECT, UPDATE (name), DELETE ON artists to artist;
=# GRANT SELECT, INSERT, UPDATE (title, released), DELETE ON albums to artist;

-- I add the *s pattern to only match database objects with names ending in s,
-- so it'll show our tables (which have plural names) and hide the sequence
-- database objects that appeared in the output last time we ran \dp.
=# \dp *s
Access privileges
 Schema |    Name     | Type  |     Access privileges     |  Column privileges  | Policies 
--------+-------------+-------+---------------------------+---------------------+----------
 public | albums      | table | postgres=arwdDxt/postgres+| title:             +| 
        |             |       | fan=r/postgres           +|   artist=w/postgres+| 
        |             |       | artist=ard/postgres       | released:          +| 
        |             |       |                           |   artist=w/postgres | 
 public | artists     | table | postgres=arwdDxt/postgres+| name:              +| 
        |             |       | artist=rd/postgres       +|   artist=w/postgres | 
        |             |       | fan=r/postgres            |                     | 
 public | fan_follows | table | postgres=arwdDxt/postgres+|                     | 
        |             |       | fan=ard/postgres          |                     | 
 public | fans        | table | postgres=arwdDxt/postgres+|                     | 
        |             |       | fan=rd/postgres           |                     | 
(4 rows)

You may notice that we're not granting update privileges on any ID columns. If we instead allowed app users to edit IDs, then they could do things we don't want them to, like change the identity of a row or change relationships between rows (e.g. an artist could assign an album they created to a different artist). By using column-specific privileges, we can ensure that users are only able to change values that we allow. Another way to protect against users changing IDs that are used in relationships (foreign keys) is with row-level security policies, which we'll explore in the next article.

💡 We could also omit select privileges on ID columns, which you might want to do to hide internal info like surrogate keys or business intel like the rate at which artists create albums on your platform (if you're using auto-incrementing integer IDs). However, if users can't see IDs, they can't join between tables using those IDs (you would need to provide database views for any joined data you wanted users to see) and they can't run SELECT * on those tables (they would need to know and explicitly name all the columns in the table to select data from it).

Now that we have some privileges established, let's add some sample data and test that the privileges work correctly.

-- First, we insert one fan and 3 artists as the postgres superuser
=# INSERT INTO fans DEFAULT VALUES;
=# INSERT INTO artists (name)
     VALUES ('DJ Okawari'), ('Steely Dan'), ('Missy Elliott');

-- We change role to "fan" and follow some artists (DJ Okawari and Steely Dan)
=# SET ROLE fan;
=> INSERT INTO fan_follows (fan_id, artist_id)
     VALUES (1, 1), (1, 2);

-- We unfollow DJ Okawari
=> DELETE FROM fan_follows WHERE artist_id = 1;

-- Let's list what artists we're still following
=> SELECT * FROM fans
     INNER JOIN fan_follows USING (fan_id)
     INNER JOIN artists USING (artist_id);
 artist_id | fan_id |    name    
-----------+--------+------------
         2 |      1 | Steely Dan

-- Try to change an artist's name, which doesn't work from the "fan" role
=> UPDATE artists SET name = 'TWRP' WHERE artist_id = 2;
ERROR:  42501: permission denied for table artists

-- Change roles to "artist" and change an artist's name
=> SET ROLE artist;
=> UPDATE artists SET name = 'TWRP' WHERE artist_id = 2;

-- Add a new album
=> INSERT INTO albums (artist_id, title, released)
     VALUES (3, 'Under Construction', '2002-11-12');

-- Try to assign the album to a different artist, which doesn't work
=> UPDATE albums SET artist_id = 2;
ERROR:  42501: permission denied for table albums

=> DELETE FROM artists;
-- Deleting all artists in the database executes without error! *gulp*

Our privileges are looking good overall... except the part where any user logged in as the "artist" role can delete all artists from the database! We would prefer if artists could only delete their own artist account, but this kind of authorization logic is not expressible in terms of privileges, GRANT , and REVOKE . That's because the authorization decision ("should this action be allowed?") depends on the values in a particular database row. As you've already guessed, we need Row-Level Security Policies to make fine-grained decisions about which rows in a database can be acted upon by specific users. We'll tackle that in the next article!

Thanks for reading! I hope you learned something new and will return for future articles in the series. If you have any corrections to this article or suggestions/questions for future articles, please let me know or send us a tweet, and if you’re curious to learn more about how we improve perception sensors, visit us at Tangram Vision. To get notified when the next two posts of this series goes live, subscribe to our blog RSS feed!

‍

‍