3

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.

Improve this question

2 Answers 2

Reset to default
3

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...

Improve this answer
3
  • 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, 2019 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, 2019 at 15:28
  • 1
    But returning to a previous role could still be problematic! If the connection starts out with an authenticator role that can either switch to a restricted or unrestricted role, and this user was switched to the restricted role, could then reset and issue a set role. It could do so by abusing the application logic (sql injection perhaps).
    – svenema
    Feb 26 at 9:08
2

PostgreSQL set_user Extension Module with a token could be an option.

You could make PgBouncer reset the role back using server_reset_query.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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