Postgres Row Level Security Using JWT

row-level-security-jwt.sql

-- Row Level Security Using JWT
-- Based on https://www.enterprisedb.com/blog/application-users-vs-row-level-security
-- and https://docs.postgrest.org/en/v12/explanations/db_authz.html

-- as admin user

-- create test tables
DROP TABLE IF EXISTS chat CASCADE;
CREATE TABLE chat
(
    id              SERIAL PRIMARY KEY,
    message_from    NAME        NOT NULL,
    message_to      NAME        NOT NULL,
    message_subject VARCHAR(64) NOT NULL
);

DROP TABLE IF EXISTS chat_body CASCADE;
CREATE TABLE chat_body
(
    chat_id      INT NOT NULL REFERENCES chat (id),
    message_body TEXT
);

-- enable row level security
ALTER TABLE chat ENABLE ROW LEVEL SECURITY;

-- RLS policy
-- USING part means read rows only when message_from and/or message_to matches username in JWT
-- WITH CHECK part means only allow writes when message_from equals username in JWT
CREATE POLICY chat_policy ON chat
USING (current_setting('app.jwt', true)::json->>'username' IN (message_from, message_to))
WITH CHECK (message_from = current_setting('app.jwt', true)::json->>'username');

-- create non-admin role
REASSIGN OWNED BY serviceaccount1 TO postgres;
DROP OWNED BY serviceaccount1;
DROP ROLE IF EXISTS serviceaccount1;

CREATE ROLE serviceaccount1 NOLOGIN;
GRANT ALL ON public.chat TO serviceaccount1;
GRANT ALL ON public.chat_body TO serviceaccount1;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO serviceaccount1;

-- insert test data that serviceaccount1 can not read
WITH row AS (
    INSERT INTO chat (message_from, message_to, message_subject)
        VALUES ('otheruser1', 'otheruser2', 'test subject1') RETURNING id
)
INSERT INTO chat_body(chat_id, message_body) VALUES ((SELECT id FROM row), 'test body1');

BEGIN;

-- switch to non-admin user for RLS to take effect
SET LOCAL ROLE serviceaccount1;

-- This is key; set JWT as session variable
SET LOCAL app.jwt = '{"username": "user1"}';

-- can only insert when message_from matches the username in the JWT
WITH row AS (
    INSERT INTO chat (message_from, message_to, message_subject)
        VALUES ('user1', 'user2', 'test subject2')  RETURNING id
)
INSERT INTO chat_body(chat_id, message_body) VALUES ((SELECT id FROM row), 'test body2');

-- only read rows where message_from and/or message_to matches the username in the JWT, hence not include test data above
SELECT 'serviceaccount1: Should only return 1 row' AS debug;
SELECT * FROM chat JOIN chat_body ON chat.id = chat_body.chat_id;

COMMIT;

-- back to admin user

-- read all as admin user, e.g. 'postgres'
SELECT 'admin: should return 2 rows' AS debug;
SELECT * FROM chat JOIN chat_body ON chat.id = chat_body.chat_id;