One post tagged with "security"

PostgreSQL Row Level Security

April 4, 2024 · 4 min read

Founder, Edgeflare BV

Let's consider the below tables for a simple blog application.

CREATE TABLE IF NOT EXISTS authors (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS posts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    author_id UUID NOT NULL REFERENCES authors(id),
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

We want to restrict the access to the authors and posts tables based on the user's role and the ownership of the data.

GRANT command grants

membership in a role, or
privileges on a database object (table, etc).

In addition to GRANT, tables can have row security policies that restrict, on a per-user basis, which rows can be returned by normal queries or inserted, updated, or deleted by data modification commands. This feature is known as Row-Level Security (RLS).

We will use the webui role for the authenticated web users and the anon role for the anonymous users. We'll further restrict the access to the authors table based on the user's id from the JWT token issued by the authentication server, Keycloak.

Grant privileges on the tables to the anon (anonymous) role and webui role. The webui role will be used for the authenticated web users.

GRANT SELECT ON posts TO anon;
GRANT SELECT ON authors TO anon;
GRANT ALL PRIVILEGES ON authors TO webui;
GRANT ALL PRIVILEGES ON posts TO webui;
-- Or, grant specific privileges
-- GRANT SELECT (id, username) ON authors TO anon;
-- GRANT SELECT, INSERT, UPDATE, DELETE ON posts TO webui;
-- GRANT SELECT, INSERT, UPDATE, DELETE ON authors TO webui;

Insert some mock data into the tables.

INSERT INTO authors (username, email) VALUES
('john_doe', '[email protected]'),
('jane_smith', '[email protected]'),
('alex_jones', '[email protected]');

-- Insert 2 posts for each author
DO $$
DECLARE
    author_record RECORD;
BEGIN
    FOR author_record IN SELECT id, username FROM authors LOOP
        INSERT INTO posts (author_id, title, body) VALUES
            (author_record.id, 'First Post Title by ' || author_record.username, 'First post body by ' || author_record.username),
            (author_record.id, 'Second Post Title by ' || author_record.username, 'Second post body by ' || author_record.username);
    END LOOP;
END $$;

Row Level Security Policies

Allow authors to SELECT their own profile. policy name select_self is arbitrary.

CREATE POLICY select_self ON authors
    FOR SELECT
    USING (id = auth.user_jwt_sub());
ALTER POLICY select_self ON authors TO webui;

USING (id = auth.user_jwt_sub()): A user can only attempt to update rows where their id matches the id decoded from their JWT token (auth.user_jwt_sub()).

Allow users to UPDATE their own profile, except for username

CREATE POLICY update_self ON authors
    FOR UPDATE
    USING (id = auth.user_jwt_sub())
    WITH CHECK (id = auth.user_jwt_sub());
ALTER POLICY update_self ON authors TO webui;

WITH CHECK (id = auth.user_jwt_sub()): After the update, the id of the row must still match the user's id from their JWT token. This prevents users from updating records that do not belong to them.

Allow everyone to SELECT all posts

CREATE POLICY select_anon ON posts
    FOR SELECT
    USING (true);
ALTER POLICY select_anon ON posts TO anon, webui;

Allow authenticated users to INSERT posts

CREATE POLICY insert_authn ON posts
    FOR INSERT
    WITH CHECK (author_id = auth.user_jwt_sub());
ALTER POLICY insert_authn ON posts TO webui;

Allow the post owner to UPDATE their own posts

CREATE POLICY update_own ON posts
    FOR UPDATE
    USING (author_id = auth.user_jwt_sub())
    WITH CHECK (author_id = auth.user_jwt_sub());
ALTER POLICY update_own ON posts TO webui;

Allow the post owner to DELETE their own posts

CREATE POLICY delete_own ON posts
    FOR DELETE
    USING (author_id = auth.user_jwt_sub());
ALTER POLICY delete_own ON posts TO webui;

For authenticated requests, PostgREST automatically sets the request.jwt.claims configuration variable based on the decoded JWT provided in the request. This can be used to implement RLS policies, as in auth.user_jwt_sub() utility which returns the sub claim from the JWT token as a UUID.

CREATE OR REPLACE FUNCTION auth.user_jwt_sub() RETURNS UUID AS $$
DECLARE
    jwt_claim_sub UUID;
BEGIN
    SELECT (current_setting('request.jwt.claims', true)::json->>'sub')::UUID INTO jwt_claim_sub;
    RETURN jwt_claim_sub;
END;
$$ LANGUAGE plpgsql STABLE;

For debugging etc purposes one can manually set request.jwt.claims configuration variable to the decoded JWT claims.

SET request.jwt.claims TO 'jwt.claims';

Row Level Security Policies​

Row Level Security Policies