ACL via Row Level Security in PostgreSQL stage

Row Level Security beperkt toegang tot rijen op basis van een controle die kan bestaan uit onder andere een of meer SQL queries.

PostgreSQL's Row Level Security (RLS) maakt het mogelijk om rijen in een tabel effectief onzichtbaar te maken voor een user, op basis van zijn naam, rol, sessie-variabelen of data in een andere tabel. In eerste instantie klinkt dit heel erg als wat we allemaal al jaren doen met views, maar RLS gaat een stap verder door op de tabel zelf te werken en dus ook actief te zijn in JOINs. Views werken prima om data te verstoppen zolang men de view driect aanspreekt en de view geen JOINs bevat en het wordt lastig als het om een INSERT of UPDATE gaat.

RLS lost dat op doordat de policy op de tabel zelf staat en dus altijd wordt aangeroepen ongeacht of de tabel direct wordt benaderd of via een view of een function of wat dan ook.

Proof of concept

Stel een applicatie heeft een sectie met artikelen en elk artikel heeft een status 'draft', 'published', 'archived' of 'deleted'. Users kunnen een rol 'USER', 'EDITOR' of 'ADMIN' hebben en afhankelijk van die rol kunnen ze sommige records wel of niet zien en kunnen ze wel of niet updaten, inserten of deleten.

code

CREATE TYPE article_status_type AS ENUM ('draft', 'published', 'archived', 'deleted');
CREATE TABLE articles (id INT, title TEXT, date_published timestamptz, status article_status_type);
INSERT INTO articles VALUES
                            (1, 'How to not get murdered.', '2020-01-01', 'published'),
                            (2, 'World peace ion three easy steps.', '2020-01-01', 'draft'),
                            (3, 'Secrets about Rossewell.', '2020-01-01', 'deleted');

-- Enable and enforce RLS
ALTER TABLE articles ENABLE ROW LEVEL SECURITY;
ALTER TABLE articles FORCE ROW LEVEL SECURITY;

User 'mister_nobody' wordt de user waarmee de rechten getest gaan worden. De user moet sowieso via GRANT al toegang hebben tot de tabel.

Let op dat superusers altijd door alle RLS heen prikken (anders zou je jezelf kunnen buitensluiten), dus je moet echt op de database aanloggen als een andere user om te zien of het werkt.

RLS is onderdeel van de tabelstructuur dus als het privileges systeem (GRANT) de toegang tot een tabel weigert dan kan RLS daar niets meer aan doen, de query wordt al geblokkeerd op basis van het feit dat de tabel er in wordt genoemd.
Het gaat een beetje tegen je gevoel in maar user misternobody moet via GRANT alle rechten hebben die je via RLS wilt gaan toestaan en het is RLS die dan uiteindelijk bepaalt of het per record mag of niet.

code

CREATE USER mister_nobody WITH ENCRYPTED PASSWORD 'misternobody';
GRANT ALL ON articles TO misternobody;

De rechten worden per rol uitgedeeld en opgeslagen in een tabel.

Hier gebeurt iets dat heel erg tegen de haren in gaat; user misternobody moet leesrechten hebben op de rechtentabel. Het is immers misternbody die de RLS query draait en dus moet hij de rechtentabel kunnen lezen. In de praktijk zou je dit vervangen door een function zodat je via SECURITY DEFINER kunt regelen dat misternobody hier nier meer direcrt bij kan. Voor ditr voorbeeld ga ik er vanuit dat het om een web-app gaat waar misternobody niet zelf queries in kan injecteren.

code

-- A table to store the rights per role.
CREATE TABLE auth_role_permission
(
    role       text,
    permission text
);

-- The queries are executed by misternobody so he too needs access to this table.
GRANT SELECT ON auth_role_permission to misternobody;

INSERT INTO auth_role_permission (role, permission) VALUES ('USER', 'read_articles');
INSERT INTO auth_role_permission (role, permission) VALUES ('EDITOR', 'read_articles');
INSERT INTO auth_role_permission (role, permission) VALUES ('EDITOR', 'edit_articles');
INSERT INTO auth_role_permission (role, permission) VALUES ('EDITOR', 'create_articles');
INSERT INTO auth_role_permission (role, permission) VALUES ('ADMIN', 'read_articles');
INSERT INTO auth_role_permission (role, permission) VALUES ('ADMIN', 'edit_articles');
INSERT INTO auth_role_permission (role, permission) VALUES ('ADMIN', 'delete_articles');

Maak RLS regels aan. Een regel kan gelden voor ALL als hij altijd moet gelden of apart voor INSERT, SELECT, UPDATE of DELETE. Meestal wil je aparte regels voor wie mag lezen en wie mag muteren dus in dit voorbeeld maak ik aparte regels.
Per regel wordt gekeken naar twee dingen: de status van het artikel en de rechten die de huidige gebruiker heeft in een rechtentabel.

code

-- Users who have only read access can read only articles that are published.
-- Users who have edit or admin richts can see all.
CREATE POLICY articles_policy_select
    ON public.articles
    FOR SELECT
    USING (
        (EXISTS(SELECT null
                FROM auth_role_permission
                WHERE role = current_setting('myapp.role')
                  AND permission = 'read_articles')
            AND
         status = 'published'::article_status_type
            )
        OR EXISTS(SELECT null
                  FROM auth_role_permission
                  WHERE role = current_setting('myapp.role')
                    AND permission IN ('edit_articles', 'admin_articles')
            )
    );

-- Only editors can insert
CREATE POLICY articles_policy_insert
    ON public.articles
    FOR INSERT
    WITH CHECK (
        EXISTS(SELECT null
                  FROM auth_role_permission
                  WHERE role = current_setting('myapp.role')
                    AND permission = 'create_articles')
    );

-- Only admins and editors can update
CREATE POLICY articles_policy_update
    ON public.articles
    FOR SELECT
    USING (
        EXISTS(SELECT null
                  FROM auth_role_permission
                  WHERE role = current_setting('myapp.role')
                    AND (permission IN('editor_articles', 'admin_articles')))
    );

-- Only admins can delete
CREATE POLICY articles_policy_delete
    ON public.articles
    FOR SELECT
    USING (EXISTS(SELECT null
                  FROM auth_role_permission
                  WHERE role = current_setting('myapp.role')
                    AND permission = 'delete_articles'));

Als je nu aanlogt als misternobody en probeert de articletabel te benaderen dan zie je dat wat je ermee kunt volledig afhangt van welke rol je in myapp.role zet.

code

-- Choose a role:
SET SESSION "myapp.role" = 'USER';
SET SESSION "myapp.role" = 'EDITOR';
SET SESSION "myapp.role" = 'ADMIN';

-- See what your role is.
SELECT(current_setting('myapp.role'));

-- USER can only see published roles:
SELECT * FROM articles;

-- Insert is not allowed by policy, this throws an error because the query fails.
INSERT INTO articles (id, title, date_published, status)
VALUES (4, 'How to become an evil overlord.', now(), 'published');

-- Update does not return an error because the query worked fine, but row id=1 is not visible to role USER
-- so it ends with '0 rows affected', which is correct.
UPDATE articles SET title = 'foo' WHERE id=1;

-- Delete does not return an error either, because as far as this user is concerned, the record does not exist.
DELETE FROM articles WHERE id=4;

Als je een INSERT uitvoert die via een unique-index een violation heeft met een bestaand record dat via RLS niet zichtbaar is, dan krijg je daar wel gewoon een error op. Je kunt niet kijken wat dat record precies is, maar je krijgt wel terug dat het niet gelukt is.