Skip to main content

Cross-module database access

When one module needs to read another's data, one setting decides whether that means another schema or another database. You change the setting; you do not change any code.

Decided by the PO on 2026-08-19 as part of M11-002, and it applies platform-wide — reporting is simply the first module that needed it.

The two shapes​

uhp.core-data-access.standaloneWhat happens
false — the defaultThe module reads through the pool it already has. Everything lives in one database, each module in its own schema. No second credential exists, so there is nothing extra to manage, rotate or leak.
trueThe module opens a separate, read-only connection using credentials you supply. Use this once a schema has been moved out to its own database.

Moving a schema to its own database​

  1. Move the schema, by whatever means your database team prefers.

  2. Create a read-only role in the source database (recipe below).

  3. Set four environment variables:

    UHP_CORE_DATA_ACCESS_STANDALONE=true
    UHP_CORE_DATA_ACCESS_URL=jdbc:postgresql://core-db.internal:5432/uhp
    UHP_CORE_DATA_ACCESS_USERNAME=uhp_core_readonly
    UHP_CORE_DATA_ACCESS_PASSWORD=<from your secrets manager>

  4. Restart.

That is the whole migration, from the application's point of view.

⚠ If you set standalone=true and leave any of the three credentials blank, the application will not start. That is deliberate. A half-configured split deployment that starts is a deployment reading the wrong database, and it would look completely healthy while doing it.

Creating the read-only role​

⚠ GRANT SELECT alone does not make a role read-only. This was measured, not assumed: a role built with SELECT-only grants successfully created a table on the first attempt. PUBLIC may still hold CREATE — PostgreSQL 14 and earlier ship exactly that default on the public schema, PostgreSQL 15 removed it, and any schema where somebody granted it back has it again.

CREATE ROLE uhp_core_readonly LOGIN PASSWORD '<from your secrets manager>';

GRANT CONNECT ON DATABASE uhp TO uhp_core_readonly;
GRANT USAGE ON SCHEMA public TO uhp_core_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO uhp_core_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO uhp_core_readonly;

-- ⚠ The steps that actually make it read-only.
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE uhp FROM PUBLIC;

Repeat the USAGE/SELECT/ALTER DEFAULT PRIVILEGES lines for each schema the module must read.

Check that it worked​

Connect as the new role and confirm each of these is refused:

SELECT count(*) FROM patient;                  -- should succeed
INSERT INTO patient (patient_id) VALUES (...); -- must fail: permission denied
UPDATE patient SET voided = true;              -- must fail: permission denied
CREATE TABLE scratch (id int);                 -- must fail: permission denied

If any of the last three succeeds, the role is not read-only yet. ReadOnlyCredentialIntegrationTest runs this same check in CI against a purpose-built role.

What read-only does and does not protect​

It stops writes. It does not narrow reads. A read-only credential can read every table it has been granted, which is why what may be read is a separate control:

  • Reporting may only query a relation registered in its report_source allow-list.
  • Every query is filtered to the caller's tenant, and to their facility where the source supports it — applied automatically, so a query that cannot be scoped returns nothing rather than everything.
  • A source holding row-level patient data requires a named permission, and is never copied into a materialised view.

See Reporting & Analytics for how those work.