0

Is there a way to prevent a user from running "reset role" in PostgreSQL, and thus gaining permission he may not have ?

I have an architecture with many databases on a single server. An API connects to several databases on a local PostgreSQL server. I had planned to use Pgbouncer but it sounds like I would have to map one database/user in pgbouncer to the same database/user in PostgreSQL which sounds counter productive. Instead I would like to be able to reuse connections but have different authorization for different user upon connection.

1

1 RESET ROLE would just "return" you back to the role you used to login. here also SET ROLE rolename

must be a role that the current session user is a member of

so " gaining permission he may not have" is not the case.

2 pgbouncer indeed will require such mapping.

3 to "reuse connections but have different authorization for different user upon connection." you can use set authorization which is very similar to SET ROLE.

There is no session pooler that would do it for you I believe...

  • There are contexts where it is useful for a proxy to be able to re-use connections for different users. This requires a proxy that connects as a powerful user, and temporarily restricts permissions. The "SET SESSION AUTHORIZATION" documentation says "The user name can be written as either an identifier or a string literal. Using this command, it is possible, for example, to temporarily become an unprivileged user and later switch back to being a superuser." – armb Sep 3 at 15:21
  • The problem is ensuring that the proxy can switch back to being a superuser when a new user connects, but that a connected user can't gain superuser permissions for themselves while connected. – armb Sep 4 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.