Granular access with Row Level Security in Postgres

On my current side project I need user based granular access to provide access to 3 layers. I will not show everything here, but in short there are three layers where Layer 1 has children from layer 2, and Layer 2 has children from layer 3.

The access model I chose was to provide access by granting access explicit to each resource. So if you have layer 1 access you do not inherit access to the children, you have to explicitly get access.

Roughly like this: Access mappings

In my initial design I noticed that there wre a lot of repetitive WHERE statements which was might be easy to forget or prone to error (i.e WHERE user_id = X). So I explored other methods of doing the type of authorization I wanted and settled to use row level security which change the responsibility to database level.

I'm not really sure what I think about moving database logic to the database and not have it the data access layer (DAL). But in this case, the database and the DAL is strong coupled in a monorepo where I have full control over both.

Components

Example schema

The example schema will only have layer 1, since the complexity does not increase with more layers.

1-- Table to store users
2CREATE TABLE users (
3    user_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
4    is_admin BOOLEAN NOT NULL DEFAULT FALSE
5);
6
7-- Table to store device information
8CREATE TABLE devices (
9    device_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
10    device_name TEXT NOT NULL,
11);
12
13-- Table to store the relationship between users and devices,
14-- allowing each user to have access to multiple devices
15-- Access level (0 = blocked, 1 = read, 2 = read-write, 3 = admin)
16CREATE TABLE user_device (
17    user_id INTEGER REFERENCES users(user_id),
18    device_id INTEGER REFERENCES devices(device_id),
19    access_level INTEGER NOT NULL DEFAULT 0,
20    PRIMARY KEY (user_id, device_id)
21);

Functions

1-- The user who is executing the query
2CREATE
3  FUNCTION current_app_user() RETURNS INTEGER AS $$ SELECT
4    NULLIF(
5      current_setting(
6        'app.current_user_id',
7        TRUE
8      ),
9      ''
10    ) $$ LANGUAGE SQL SECURITY DEFINER;

1-- Checks if a user is an admin
2 CREATE
3  FUNCTION is_user_admin(
4    _user_id INTEGER
5  ) RETURNS BOOLEAN AS $$ SELECT
6    EXISTS(
7      SELECT
8        1
9      FROM
10        users
11      WHERE
12        user_id = _user_id
13        AND is_admin = TRUE
14    ) $$ LANGUAGE SQL SECURITY DEFINER;

1-- The function returns true if the user has a matching
2-- record in the user_device table with access_level > _access_level
3CREATE FUNCTION has_direct_access_to_device(_user_id INT, _access_level INT, _device_id INT)
4RETURNS BOOLEAN AS $$
5  BEGIN
6  RETURN (
7    EXISTS (
8      SELECT 1 FROM user_device
9      WHERE user_device.user_id = _user_id
10        AND user_device.access_level >= _access_level
11        AND user_device.device_id = _device_id
12    )
13  );
14  END
15$$ LANGUAGE plpgsql;

Make the table use RLS

1-- DEVICE ROW LEVEL SECURITY
2
3ALTER TABLE devices ENABLE ROW LEVEL SECURITY;
4
5-- Allow access to the device table if the user has the
6-- required access level, as determined by the has_access function
7CREATE POLICY device_access ON devices
8FOR SELECT
9USING (
10  is_user_admin(current_app_user()) OR has_direct_access_to_device(current_app_user(), 1, device_id)
11);

Queries

I'm writing my DAL in typescript and I use Slonik, a Node.js PostgreSQL client with strict types, detailed logging and assertions. A great library if you are comfortable with writing raw sql and do not want to use a query builder (like Knex.js) or an ORM (like prisma). Personally I've found that the complexity of learning different ORMs and query builders are about the same as just learning SQL but you get the advantage of it being language agnostic.

So back to the queries. The structure is simple:

Start a transaction
Set current user id localized to the current query
Execute the query/queries

So the code looks something like this

1-- app.current_user_id resets to original when the transaction ends
2SET LOCAL app.current_user_id = X

1await connection.transaction(async (transaction) => {
2     // extracted out to a helper function for consistency
3     await set_current_user_id(transaction, user_id)
4
5     devices = await transaction.many(
6          sql.type(deviceObject)`
7        SELECT DISTINCT d.device_id, device_name FROM devices as d
8        INNER JOIN user_device as ud
9          ON d.device_id = ud.device_id
10          WHERE device_id = ${device_id} 
11          `
12     )
13})

There is two major advantages to this structure

I do not need to write WHERE ud.access_level > X
I do not need to write JOINS on the access tables in the query, the result will always be what you have access to get.

References

I came across some great articles while implementing this that you can read for better context and explore other solutions.

Amazon AWS

Tangram vision

Official docs for RLS