Proxy for Read-only access with query logging

[andy-g]

We have the need to offer read-only access to a set of PostgreSQL databases on multiple DB instances. We'd ideally like to do this without needing to create users on each of the destination databases, and ideally we'd like to use short lived credentials, so a user could request access to a specific database & a new set of credentials could be created for the requested access, and that'll be available for an hour or day etc.

So, I'm wondering if pgcat could handle this scenario by supporting the following:

• Securely store username / password configuration (possibly md5 based) for each Postgres DB instance, and configuring specific databases that we'd want to make available via pgcat
  • possibly overriding the db name (in case there are name conflicts with databases on different DB instances)
• Support API or SQL based ways of creating users / credentials for clients to connect to pgcat. We'd want to allow pgcat to manage the auth so we don't have to create these users on all of the source DB instances.
• We'd want to log each query run through pgcat for audit purposes, does pgcat allow logging of all queries?

Thanks so much for the help. I'm trying trying to determine what tools is best to handle this scenario (currently looking at https://github.com/pgbouncer/pgbouncer, https://github.com/supabase/supavisor, https://github.com/jeroenrinzema/psql-wire along with pgcat).

[levkk]

Good feature to have. We should build our own RBAC system and integrate with popular ones like Hashicorp Vault.

[andy-g]

I assume that means currently all access validation is based on users defined in the downstream databases, there's currently no functionality to create users in pgcat itself?

[levkk]

You can create users in pgcat, you just need to map them to real users in the database. See server_username and server_password for more (in pgcat.toml). It's currently config-driven, so no RBAC integration, you need to generate a new config and reload every time you add/remove "pgcat users".

[andy-g]

Thanks for the explanation @levkk. Having a look at the pgcat.toml, I assume that means I could re-use the same server_username for multiple pgcat users, so in essence create 1 read-only user on the source DB, and then create multiple users in pgcat that can all use that same username on the source DB.

Does pgcat allow for logging queries passed through it? I have the need to log each query against the authenticated user (it'd need to be the pgcat user, not the source DB user).

[levkk]

Yes, take a look at the plugins configuration in pgcat.toml. There is one plugin for logging queries.