Skip to content

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 AP 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 with the following assumptions:

  • DHIS2 database name: dhis2
  • DHIS2 database main/owner user: dhis
  • DHIS2 read-only user: dhis_ro
  • DHIS2 schema: public

Adjust the script to match the environment as necessary.

Connect with psql to database dhis2 as postgres superuser. 

psql -d dhis2 -U postgres

As the postgres superuser, create the read-only dhis_ro user with the SQL statements below.

--
-- Create the read-only user
--

create user dhis_ro with password 'your_secure_password';

--
-- Grant `connect` to database and `usage` on schema
--

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

--
-- Grant `select` on current tables, views and sequences
--

grant select on all tables in schema public to dhis_ro;
grant select on all sequences in schema public to dhis_ro;

--
-- Grant `select` on future tables, views and sequences
--

alter default privileges for role dhis in schema public 
grant select on tables to dhis_ro;

alter default privileges for role dhis in schema public 
grant select on sequences to dhis_ro;

Note that the default privileges is altered for the user which creates future tables, which is the dhis user, to grant read access to the read-only user, which is the dhis_ro user.

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