Read-only user for PostgreSQL

To adhere to the principle of least privilege, it may be desirable to use a PostgreSQL user with read-only permissions for the Analytics Platform connection.

The DHIS2 database contains both standard and dynamic tables. Dynamic tables are tables which are generated by DHIS2 at runtime and include resource tables and analytics tables. As a result, when creating a new read-only user it is important to grant access both to existing database tables and to tables created in the future.

This guide describes the commands to create a read-only user. In the examples, the database name is dhis2 and the database user username is dhis_ro. Adjust it to your environment if necessary.

Create the user.

create user dhis_ro with password 'your_secure_password';

Connect to the database.

\c dhis2

Grant connect and usage.

grant connect on database dhis2 to dhis_ro;
grant usage on schema public to dhis_ro;

Grant select on current tables and views.

grant select on all tables in schema public to dhis_ro;

Grant select on future tables and views and usage on sequences.

alter default privileges in schema public grant select on tables to dhis_ro;
alter default privileges in schema public grant usage on sequences to dhis_ro;

You can now use the dhis_ro user when connecting to PostgreSQL with an AP data pipeline.