POSTGRES.md

Database Overview

Here is the database service definition from docker-compose.yml:

  db:
    <<: *defaults
    image: postgres:15.3-alpine3.17
    ports:
      - '5432:5432'
    volumes:
      - tenancy_example_db_data:/var/lib/postgresql/data
      - type: bind
        source: ./db
        target: /docker-entrypoint-initdb.d
    environment:
      POSTGRES_PASSWORD: 07f019e661d8ca48c47bdffd255b12fe

The bind mount maps the db directory on the host to the docker entrypoint directory.

The two SQL files in ./db (1_schema.sql and 2_data.sql) are executed when the container is created, creating the schema and populating it with test data.

1_schema.sql

The tables:

create table if not exists public.tenants
(
    id bigserial primary key,
    display_name varchar,
    is_admin boolean default false
);

create table if not exists public.users
(
    id bigserial primary key,
    tenant_id bigint
        constraint users_tenants_id_fk
            references public.tenants
            on delete cascade,
    user_name varchar,
    password varchar
);

create table if not exists public.patients
(
    id bigserial primary key,
    tenant_id bigint
        constraint patients_tenants_id_fk
            references public.tenants
            on delete cascade,
    first_name varchar,
    last_name varchar,
    dob date
);

RLS function:

create or replace function fn.tenant_data_rls_check(row_tenant_id bigint) returns boolean
    language plpgsql
as
$$
BEGIN

IF current_setting('tenancy.bypass')::text = '1' THEN
    return true;
end if;

IF current_setting('tenancy.tenant_id')::integer = row_tenant_id THEN
    return true;
end if;

return false;
END;
$$;

tenant_data_rls_check takes a single argument, the value of 'tenant_id' (or 'id' for the tenants table) for the queried/mutated row.

Looking at the function body, you'll see that first it checks if the session value 'tenancy.bypass' is equal to '1', and if so it returns true, allowing the operation.

Next, it compares the session value 'tenancy.tenant_id' with the tenant_id value for the row. If equal, it allows the operation, otherwise the operation fails.

Policies:

create policy tenancy_policy on public.tenants
    as permissive
    for all
    using (fn.tenant_data_rls_check(id) = true)
    with check (fn.tenant_data_rls_check(id) = true);

create policy tenancy_policy on public.users
    as permissive
    for all
    using (fn.tenant_data_rls_check(tenant_id) = true)
    with check (fn.tenant_data_rls_check(tenant_id) = true);

create policy tenancy_policy on public.patients
    as permissive
    for all
    using (fn.tenant_data_rls_check(tenant_id) = true)
    with check (fn.tenant_data_rls_check(tenant_id) = true);

Note that 1_schema.sql enables these policies at the end of the script, so you don't need to do that yourself.

2_data.sql

This SQL file populates the database with the following test data:

Tenants Test Data

id	display_name	is_admin
1	user tenant 1	false
2	user tenant 2	false
3	user tenant 3	false
4	user tenant 4	false
5	user tenant 5	false
6	admin tenant	true

Users Test Data

id	tenant_id	user_name	password
1	1	t1 user1	$2b$10$gra37ECOljK.6udDxfwAOOTSyeQSbo9I0zS6l6NoMR1mbE.9T.jF2
2	1	t1 user2	$2b$10$gra37ECOljK.6udDxfwAOOTSyeQSbo9I0zS6l6NoMR1mbE.9T.jF2
3	2	t2 user1	$2b$10$gra37ECOljK.6udDxfwAOOTSyeQSbo9I0zS6l6NoMR1mbE.9T.jF2
4	2	t2 user2	$2b$10$gra37ECOljK.6udDxfwAOOTSyeQSbo9I0zS6l6NoMR1mbE.9T.jF2
5	3	t3 user1	$2b$10$gra37ECOljK.6udDxfwAOOTSyeQSbo9I0zS6l6NoMR1mbE.9T.jF2
6	3	t3 user2	$2b$10$gra37ECOljK.6udDxfwAOOTSyeQSbo9I0zS6l6NoMR1mbE.9T.jF2
7	4	t4 user1	$2b$10$gra37ECOljK.6udDxfwAOOTSyeQSbo9I0zS6l6NoMR1mbE.9T.jF2
8	4	t4 user2	$2b$10$gra37ECOljK.6udDxfwAOOTSyeQSbo9I0zS6l6NoMR1mbE.9T.jF2
9	5	t5 user1	$2b$10$gra37ECOljK.6udDxfwAOOTSyeQSbo9I0zS6l6NoMR1mbE.9T.jF2
10	5	t5 user2	$2b$10$gra37ECOljK.6udDxfwAOOTSyeQSbo9I0zS6l6NoMR1mbE.9T.jF2
11	6	t6 admin	$2b$10$YJ3paQsDvg7ykcUEB6kmQetsGcaRfPzTwvpOEQSc565epW.P82lMO

Patients Test Data

id	tenant_id	first_name	last_name	dob
1	1	John	Doe	1984-02-11
2	1	Jim	Doe	1984-02-11
3	1	Bob	Doe	1992-05-13
4	1	Jerry	Doe	1984-02-11
5	1	Fran	Doe	1984-02-11
6	2	John	Doe	1992-05-13
7	2	James	Doe	1984-02-11
8	2	Josh	Doe	1984-02-11
9	2	Harry	Doe	1984-02-11
10	2	Mary	Doe	1992-05-13
11	3	John	Doe	1984-02-11
12	3	Jeoffrey	Doe	1984-02-11
13	3	Max	Doe	1984-02-11
14	3	Min	Doe	1992-05-13
15	3	Patronius	Doe	1984-02-11
16	4	John	Doe	1992-05-13
17	4	Jane	Doe	1992-05-13
18	4	Homer	Doe	1992-05-13
19	4	Maggie	Doe	1992-05-13
20	4	Bart	Doe	1992-05-13
21	5	John	Doe	1984-02-11
22	5	Walker	Doe	1992-05-13
23	5	Yeezy	Doe	1984-02-11
24	5	Puff Daddy	Doe	1984-02-11
25	5	The Rock	Doe	1992-05-13