PostgreSQL + PostgREST — Configuración Fase 2

Base de datos: iris · Host: api.iris-lib.click · PostgREST 14.5 · PostgreSQL 16.12

---

1. Base de datos

Creada desde psql como superuser pgadmin:

CREATE DATABASE iris
    WITH OWNER = pgadmin
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    TEMPLATE = template0;

---

2. Roles de Postgres

-- Rol de conexión para PostgREST (no hereda permisos)
CREATE ROLE authenticator WITH LOGIN PASSWORD '<password>' NOINHERIT;

-- Roles de negocio (sin login)
CREATE ROLE anon NOLOGIN;
CREATE ROLE iris_admin NOLOGIN;
CREATE ROLE bioinformatician NOLOGIN;
CREATE ROLE employee NOLOGIN;
CREATE ROLE org_member NOLOGIN;
CREATE ROLE patient NOLOGIN;

-- authenticator puede asumir cualquier rol de negocio
GRANT anon TO authenticator;
GRANT iris_admin TO authenticator;
GRANT bioinformatician TO authenticator;
GRANT employee TO authenticator;
GRANT org_member TO authenticator;
GRANT patient TO authenticator;

---

3. Schemas

CREATE SCHEMA api;      -- expuesto por PostgREST
CREATE SCHEMA private;  -- lógica interna, no expuesta

-- authenticator accede a ambos schemas
GRANT USAGE ON SCHEMA api TO authenticator;
GRANT USAGE ON SCHEMA private TO authenticator;

-- roles de negocio acceden solo al schema api
GRANT USAGE ON SCHEMA api TO anon;
GRANT USAGE ON SCHEMA api TO iris_admin;
GRANT USAGE ON SCHEMA api TO bioinformatician;
GRANT USAGE ON SCHEMA api TO employee;
GRANT USAGE ON SCHEMA api TO org_member;
GRANT USAGE ON SCHEMA api TO patient;

---

4. Función helper para claims JWT

CREATE OR REPLACE FUNCTION private.jwt_claim(claim text)
RETURNS text AS $$
  SELECT current_setting('request.jwt.claims', true)::jsonb ->> claim;
$$ LANGUAGE sql STABLE;

PostgREST setea request.jwt.claims con el payload completo del JWT en cada request. Esta función simplifica la lectura de claims en las RLS policies.

Uso en policies:

private.jwt_claim('role')      -- 'org_member'
private.jwt_claim('org_id')    -- '1'
private.jwt_claim('sub_role')  -- 'clinician'
private.jwt_claim('all_orgs')  -- 'false'
private.jwt_claim('patient_id') -- null si no aplica

---

5. Patrones de RLS policies

org_member — acceso por org

ALTER TABLE api.<tabla> ENABLE ROW LEVEL SECURITY;

CREATE POLICY "org_member acceso por org"
    ON api.<tabla> FOR SELECT
    TO org_member
    USING (
        org_id = (private.jwt_claim('org_id'))::int
    );

org_member con sub_role admin — ve toda su org

CREATE POLICY "org_member acceso por org con admin"
    ON api.<tabla> FOR SELECT
    TO org_member
    USING (
        org_id = (private.jwt_claim('org_id'))::int
        AND (
            private.jwt_claim('sub_role') = 'admin'
            OR assigned_to = (private.jwt_claim('user_id'))::int
        )
    );

bioinformatician — su org o todas si all_orgs=true

CREATE POLICY "bioinformatician acceso por org"
    ON api.<tabla> FOR SELECT
    TO bioinformatician
    USING (
        (private.jwt_claim('all_orgs'))::boolean = true
        OR org_id = (private.jwt_claim('org_id'))::int
    );

patient — solo sus propios datos

CREATE POLICY "patient acceso propio"
    ON api.<tabla> FOR SELECT
    TO patient
    USING (
        id = (private.jwt_claim('patient_id'))::int
    );

---

6. Configuración de PostgREST

Archivo de entorno

/etc/containers/secrets/postgrest-iris.env:

PGRST_DB_URI=postgres://authenticator:<password>@postgres:5432/iris
PGRST_DB_SCHEMA=api
PGRST_DB_ANON_ROLE=anon
PGRST_JWT_SECRET={"kid":"pWuGql2EWibPvC_F4CdWG8GO5j058Q-DnUqDFDH_4Ss","kty":"RSA","alg":"RS256","use":"sig","n":"uJCdqFuRg00_oDLyxl1Z5C41f0yAy9tG1NdPr-gdJA7lSHd7GCLU_KTjGpweSndpQRPO1VcJxAjz8HSKksa1BqPk_zqar6Y8jWQ56_BEaZWsiicGb8Zqe2OH47P0GcPltUSRKSN2uS6oWwVThNakxahAGBhTmG-eYVJGE-h7NSgG48-KRvtqAXxVHJGDrnsEOv-MoIAbAwEqx_PzbywX9raRWMHXkGoL34fiYgwHCaC1eYJ7ZfCoN-LbXYIi2AjsDdAWntk4dqzzSGgt4KWLfL-uW4b3Em3S8YSxqe4zDIzNvfY63oudKpj-8vOxYx1eSeNKZkJdhNeR8zEtDbayAQ","e":"AQAB"}
PGRST_JWT_AUD=iris-api
PGRST_SERVER_PORT=3001
PGRST_OPENAPI_SERVER_PROXY_URI=https://api.iris-lib.click

PGRST_JWT_SECRET contiene la clave pública RSA del realm iris en formato JWK. Se obtiene de: https://auth.iris-lib.click/realms/iris/protocol/openid-connect/certs

Systemd container

/etc/containers/systemd/postgrest-iris.container:

[Unit]
Description=PostgREST tenant iris
Wants=network-online.target
After=network-online.target
Requires=postgres.service

[Container]
Image=docker.io/postgrest/postgrest:latest
ContainerName=postgrest-iris
Network=proxy
EnvironmentFile=/etc/containers/secrets/postgrest-iris.env
Exec=postgrest

[Service]
Restart=always
RestartSec=3

[Install]
WantedBy=multi-user.target

Comandos de gestión

# Iniciar
systemctl start postgrest-iris

# Reiniciar (necesario tras cambios de schema)
systemctl restart postgrest-iris

# Ver logs
journalctl -u postgrest-iris -f

# Recargar schema cache sin reiniciar
curl -s -X POST https://api.iris-lib.click/rpc/pgrst_watch

---

7. Caddy

/etc/caddy/tenants/iris.caddy:

auth.iris-lib.click {
  reverse_proxy keycloak-hg:8080 {
    header_up X-Forwarded-Proto {scheme}
    header_up X-Forwarded-Host {host}
    header_up X-Forwarded-Port {server_port}
  }
}

api.iris-lib.click {
  reverse_proxy postgrest-iris:3001
}

pgadmin.iris-lib.click {
  reverse_proxy pgadmin-hg:80
}

genomics.iris-lib.click {
  reverse_proxy localhost:8081
}

erm.iris-lib.click {
  reverse_proxy localhost:8082
}

crm.iris-lib.click {
  reverse_proxy localhost:8083
}

app.iris-lib.click {
  reverse_proxy localhost:8000
}

Recargar Caddy:

podman exec caddy caddy reload --config /etc/caddy/Caddyfile --adapter caddyfile

---

8. Tabla sandbox

Tabla de prueba para validar RLS — conservada en producción como sandbox:

CREATE TABLE api.test_files (
    id serial PRIMARY KEY,
    org_id int NOT NULL,
    filename text NOT NULL
);

INSERT INTO api.test_files (org_id, filename) VALUES
    (1, 'sample_org1_A.vcf'),
    (1, 'sample_org1_B.vcf'),
    (2, 'sample_org2_A.vcf');

GRANT SELECT ON api.test_files TO bioinformatician;
GRANT SELECT ON api.test_files TO org_member;

ALTER TABLE api.test_files ENABLE ROW LEVEL SECURITY;

CREATE POLICY "bioinformatician acceso por org"
    ON api.test_files FOR SELECT
    TO bioinformatician
    USING (
        (private.jwt_claim('all_orgs'))::boolean = true
        OR org_id = (private.jwt_claim('org_id'))::int
    );

CREATE POLICY "org_member acceso por org"
    ON api.test_files FOR SELECT
    TO org_member
    USING (
        org_id = (private.jwt_claim('org_id'))::int
    );

---

9. Verificación end-to-end

Obtener token via device flow

# Paso 1 — solicitar device code
curl -s -X POST \
  https://auth.iris-lib.click/realms/iris/protocol/openid-connect/auth/device \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "client_id=iris-genomics-cli" \
  -d "client_secret=<client_secret>" \
  -d "scope=openid"

# Abrir verification_uri_complete en el navegador y autenticarse

# Paso 2 — obtener token y hacer request a PostgREST en un solo comando
TOKEN=$(curl -s -X POST \
  https://auth.iris-lib.click/realms/iris/protocol/openid-connect/token \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "grant_type=urn:ietf:params:oauth:grant-type:device_code" \
  -d "device_code=<device_code>" \
  -d "client_id=iris-genomics-cli" \
  -d "client_secret=<client_secret>" \
  | python3 -c "import sys, json; print(json.load(sys.stdin)['access_token'])") \
&& curl -s https://api.iris-lib.click/test_files \
  -H "Authorization: Bearer $TOKEN" \
  -H "Accept: application/json"

Resultado esperado por rol

Usuario	role	org_id	Filas devueltas
org_member org 1	org_member	1	filas con org_id=1
org_member org 2	org_member	2	filas con org_id=2
bioinformatician con org	bioinformatician	{id}	filas de su org
bioinformatician-admin	bioinformatician	any	todas las filas
anon	—	—	0 filas

Resultado validado

Token de test-bio (org_member/clinician, org_id=1):

[
  {"id": 1, "org_id": 1, "filename": "sample_org1_A.vcf"},
  {"id": 2, "org_id": 1, "filename": "sample_org1_B.vcf"}
]

✅ Filas de org_id=2 correctamente ocultas por RLS.

---

10. Notas importantes

Schema cache — PostgREST cachea el schema al arrancar. Cualquier CREATE TABLE, CREATE FUNCTION o CREATE POLICY requiere systemctl restart postgrest-iris para ser visible.

Token expiry — los access tokens tienen 5 minutos de vida por defecto en Keycloak. Para pruebas, obtener el token y hacer el request en el mismo comando.

default-roles-iris — se removieron offline_access, uma_authorization, manage-account y view-profile de los roles por defecto del realm para evitar contaminación del claim role.

JWKS rotation — si se rotan las claves del realm en Keycloak, hay que actualizar PGRST_JWT_SECRET en /etc/containers/secrets/postgrest-iris.env y reiniciar PostgREST.