Postgres Row-Level Security (RLS) Demo

rls-demo.sql

-- Postgres Row Level Security (RLS) Demo
--
-- Inspriration:
--  https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/
--  However the AWS example is a bit more complicated because they want each user
--  to be identified by a UUID probably for management purposes. This demo
--  presents a much simpler alternative for low numbers of users or environments.
--
-- Assumptions:
--  * An empty Postgres 12 database (older versions up to 9 may work but I have not tested them)
--  * 'postgres' as the admin user
--
-- Demo execution:
--  * Create a PG12 Database:
--    docker run -d --name pg-dev-test -e POSTGRES_PASSWORD=postgres -p 5555:5432 postgres:12
--  * Run this script:
--    psql postgres://postgres:[email protected]:5555 -f rls-demo.sql --echo-queries
--
-- Demo start:
-- list existing tables (none)
\dt
-- add a uuid extension to generate random uuids with uuid_generate_v4() by default
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- a table to demonstrate row-level security
CREATE TABLE shared (
    id uuid DEFAULT uuid_generate_v4 () PRIMARY KEY,
    user_name name DEFAULT CURRENT_USER,
    val text
);

-- row-level security policy to restrict users to rows where user_name = current_user
-- NOTE: this is not enough, see 'IMPORTANT' note below
CREATE POLICY shared_policy ON shared USING (user_name = CURRENT_USER);

-- show the current user (should be postgres)
SELECT
    CURRENT_USER;

-- create a row
INSERT INTO shared (
    val)
VALUES (
    'hi from admin');

SELECT
    *
FROM
    shared;

CREATE ROLE foo;

GRANT SELECT, INSERT, UPDATE, DELETE ON shared TO foo;

SET ROLE foo;

SELECT
    *
FROM
    shared;

SET ROLE postgres;

-- 'foo' should be able to see the row because we did not enable row-level security on the table 'shared'
-- IMPORTANT: enable RLS on 'shared'
ALTER TABLE shared enable ROW level SECURITY;

SET ROLE foo;

-- 'foo' should now see an empty table
SELECT
    *
FROM
    shared;

INSERT INTO shared (
    val)
VALUES (
    'hi from foo');

-- 'foo' should see the rows where user_name = 'foo'
SELECT
    *
FROM
    shared;

-- ERROR:  new row violates row-level security policy for table "shared"
INSERT INTO shared (
    user_name,
    val)
VALUES (
    'bar',
    'this insert will fail');

SET ROLE postgres;

SELECT
    *
FROM
    shared;

-- should see all rows because 'postgres' is an admin and RLS is not 'forced' for admin users
-- We can configure RLS for admin users but it does not make sense and might even be problematic.
-- A better policy is to require all access to normal users except for administrative tasks like backups and migrations.
--
-- cleanup
DROP TABLE shared;

DROP ROLE foo;

-- NOTES:
-- * user_name column is always visible (minor annoyance)
-- * we need to make sure we properly maintain users with this:
--   * create separate roles:
--     - service_editor: select, insert, (optionally) update
--     - service_read_only: select
--   * assign users to roles defined above
-- * all services reading from the shared database will be affected by schema updates (probably a non-issue)
-- * migra supports RLS: https://github.com/djrobstep/migra/blob/master/docs/index.md