PostgreSQL privileges for read-only connection user in pg_ro_user

1

TL;TR: What are the required privileges for the read-only connection user?

Here is how I’m setting up the kongro user which I’m configuring the Kong with via KONG_PG_RO_USER=kongro:

CREATE USER kongro WITH PASSWORD 'secret';
GRANT CONNECT ON DATABASE kong TO kongro;
GRANT USAGE ON SCHEMA public TO kongro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO kongro;
GRANT REFERENCES ON ALL TABLES IN SCHEMA public TO kongro;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO kongro;

However, I still see in the logs the messages like this:

could not rebuild router: could not load routes:
[postgres] ERROR: permission denied for relation routes (stale router will be used)

If I replace the kongro with username for the read-write connection, those disappear from the logs and all seems working fine.

How to configure the read-only connection user?

2

Hello,

You can use feat(postgres) create read-only user 'kong_ro' · thibaultcha/kong-tests-compose@32d7dec · GitHub as a reference when we created a readonly user for the Kong’s CI environment.

1 Like
3

This is helpful, thank you!