Postgres - Row Level Security

La seguretat en l'àmbit de fila (RLS) és una característica que et permet restringir les files retornades per una consulta en funció de l'usuari que executa la consulta.

• Introducció
• Exemple: department
• Task: Clinic
• Pending
• TODO

Introducció

El RLS et permet controlar l’accés a files individuals en taules segons l’usuari actual i condicions específiques definides per les polítiques.

Els passos bàsics per implementar la seguretat en l’àmbit de fila són els següents:

Primer, habilita la seguretat en l’àmbit de fila en una taula utilitzant la declaració ALTER TABLE:

ALTER TABLE table_name
ENABLE ROW LEVEL SECURITY;

En segon lloc, crea una nova política de seguretat en l’àmbit de fila per a una taula utilitzant la declaració CREATE POLICY:

CREATE POLICY name ON table_name
USING (condition);

A la política, es defineix una condició que determina quines files són visibles.

Tingues en compte que els superusuaris i rols amb l’atribut BYPASSRLS poden ometre el sistema de seguretat de files quan accedeixen a una taula.

A més, els propietaris de taules també passen per alt la seguretat en l’àmbit de fila.

Per aplicar la seguretat en l’àmbit de fila als propietaris de taules, pots modificar la taula utilitzant l’opció FORCE ROW LEVEL SECURITY:

ALTER TABLE table_name
FORCE ROW LEVEL SECURITY;

Exemple: department

Inicia una sessió psql en una base de dades.

Per exemple:

connect-wsl postgres
docker exec -it postgres psql -U postgres

A continuació crea una taula i uns rols on els rols poden recuperar dades de la taula on la columna manager coincideixi amb el rol actual.

Crea una base de dades hr:

create database hr;

Canvia la base de dades actual a la base de dades hr:

\c hr
You are now connected to database "hr" as user "postgres".

Crea una nova taula department per emmagatzemar dades del departament:

create table department (
    id serial primary key,
    name text not null unique,
    manager name not null
);

Insereix algunes files a la taula department:

insert into department(name, manager)
values('Sales', 'alice'), ('Marketing', 'bob'), ('IT', 'carol');

Crea un rol de grup anomenat manager:

create role manager;

Atorga el privilegi select de totes les taules de l’esquema public al rol de grup manager:

grant select on all tables in schema public to manager;

Crea tres nous rols alice, bob i carol, i assigna’ls com a membres del rol de grup manager:

create role alice with login password 'password' in role manager;
create role bob with login password 'password' in role manager;
create role carol with login password 'password' in role manager;

Els rols alice, bob i carol heretaran implícitament els privilegis dels administradors de rols de grup.

En altres paraules, poden recuperar dades de totes les taules de l’esquema public.

Canvia al rol alice:

set role alice;

Si l’usuari alice només vol accedir als seus registres pot utilitzar un where:

select * from department where manager = 'alice';
 id | name  | manager
----+-------+---------
  1 | Sales | alice
(1 row)

Però la base de dades no impedeix a l’usuari alice accedir a tots els registres de la taula department:

 select * from department;
 id |   name    | manager
----+-----------+---------
  1 | Sales     | alice
  2 | Marketing | bob
  3 | IT        | carol
(3 rows)

Torna al rol postgres:

set role postgres;

Habilita la seguretat en l’àmbit de fila a la taula department:

alter table department enable row level security;

Crea una política que permeti que l’usuari actual pugui accedir a les files on el valor de la columna manager de la taula department coincideixi amb el nom del rol actual:

create policy department_manager on department to manager
using (manager = current_user);

Tanca la connexió i torna a iniciar sessió com l’usuari alice:

quit
docker exec -it postgres psql -U alice -d hr

Recuperar dades de la taula department:

 select * from department;
 id | name  | manager
----+-------+---------
  1 | Sales | alice
(1 row)

La consulta retorna les files on la columna d’administrador és alice.

Task: Clinic

Design a database named clinic with the tables patient, doctor, and diagnosis according to the diagram above, and implement Row Level Security (RLS) to control access to medical data.

Proposed schema

Roles

Create the following roles (you can adapt them if needed):

• admin: can view and modify all data (not restricted by RLS).
• doctor: application role for doctors.
• patient: application role for patients.
• anonymous: role for unauthenticated users (cannot see clinical data).

Individual doctor/patient users would inherit from these application roles.

Security requirements (RLS)

Patients

• A patient can only see their own diagnoses.
• They cannot see diagnoses of other patients.
• They can see their own basic data (patient) and the doctors who treated them (doctor), but only as needed to display their diagnoses.

Doctors

• A doctor can only see diagnoses that they created (rows in diagnosis where doctor_id matches their doctor id).
• They can see the basic data of patients they have diagnosed.
• They cannot see diagnoses made by other doctors for other patients.

Admin

• Can see all rows in all tables (no restrictions).
• You can achieve this by not applying RLS to the admin role or by using a role with BYPASSRLS.

Anonymous

• Cannot see any data from diagnosis.
• Optionally, may see a very limited list of doctors (e.g. only name and specialty) to display on a public page.

Concrete tasks

Create schema and roles

• Create the clinic database.
• Create the patient, doctor, and diagnosis tables.
• Create the roles admin, doctor, patient, and anonymous.

Enable RLS

• Enable RLS on the patient and diagnosis tables (and on doctor if you consider it necessary).
• Optional: use FORCE ROW LEVEL SECURITY to ensure that even the table owner respects the policies.

Define RLS policies*

Write policies so that:

• Patients (role patient) only see:

  • Rows in diagnosis where diagnosis.patient_id is their own patient id.
  • Rows in patient that correspond to themselves.
  • Rows in doctor that are related only to their diagnoses.

• Doctors (role doctor) only see:

  • Rows in diagnosis where diagnosis.doctor_id is their own doctor id.
  • The patients associated with those diagnoses.

• The admin user is not restricted by RLS.

• The anonymous role cannot read any diagnoses.

Seed data

Insert some test data:

• 3 patients.
• 2 doctors.
• 5–6 diagnoses

Testing

Connect as each role and verify:

• As doctor A, you only see diagnoses made by A.
• As doctor B, you only see diagnoses made by B.
• As patient X, you only see your own diagnoses.
• As anonymous, you cannot see diagnoses.
• As admin, you can see everything.
• Write down the select queries you use and the expected results.

(Optional) Partial dump

• Design a restricted read-only role, for example report, that can only see:

  • Diagnoses for a subset of patients (for example, patients in a certain age range or from a particular clinic).

• Use this role to run pg_dump so that the exported data respects the RLS rules.

Pending

Row Level Security

set role postgres;

-- DROP
drop table if exists patient;
drop role if exists patient;
drop role if exists mary;

-- CREATE

create role patient;
create role mary in role patient;


create table if not exists patients
(
  username text unique not null,
  name     text
);
alter table patients
  enable row level security;

create table if not exists doctors
(
  username text unique not null,
  name     text
);

create table diagnoses
(
  id         integer primary key,
  patient_id text references patients (username),
  username   text not null,
  diagnosis  text,
  doctor_id text references doctors (username)
);


insert into patients
values (1, 'john', 'John Smith');
insert into patients
values (2, 'mary', 'Mary Sullivan');


--select * from patients;

grant select on patients to patient;
create policy select_patient on patients to patient using (username = current_user);

set role mary;
select * from patients;

Activitat: sales

A continuació tens el disseny de la base de dades sales:

La base de dades té tres rols: manager, product i customer.

A continuació tens parcialment implementada la seguretat de la base de dades:

Has de:

• Finalitzar de dissenyar i escriure el codi SQL corresponent.
• Inserir algunes dades de prova.
• Provar que la base de dades funciona correctament i és segura.

Abocaments parcials de dades

Quan treballem amb bases de dades, és habitual crear un fitxer seed.sql que conté un subconjunt de dades de producció per a fer proves.

Durant el desenvolupament inicial, és acceptable abocar tota la base de dades i restaurar-la a la màquina de desenvolupament. No obstant això, quan ja tens usuaris en producció això esdevé un problema de seguretat - realment vols abocar les dades dels teus usuaris a les teves màquines locals?

Hi ha moltes maneres de resoldre això, però una manera elegant de fer-ho utilitzant la Seguretat en l’àmbit de Fila (Row Level Security, RLS) de PostgreSQL.

El concepte és simple:

1. Crear un usuari de base de dades amb accés restringit.
2. Definir algunes regles RLS per a aquest usuari, limitant a quines dades pot accedir.
3. Executar pg_dump com aquest usuari.

Per a aquest escenari, imagina’t que tens una taula anomenada profiles a la teva base de dades::

…

Partial data dumps using Postgres Row Level Security

TODO

• Supabase - RLS
• Postgres - RLS
• RLS Strategy
• SQL Grant