I'd like to setup a service through which my users can submit SQL queries to a database wherein I store some data. Users are assigned to organizations, and I've setup views that provide read access only to rows that belong to a given organization, and roles that have SELECT on those views.

I'd like to not have to manage a ton of usernames and passwords. Instead, my plan was to connect to the DB as a more privileged role, and before executing a query, run SET SESSION ROLE <relevant_organization_role> and then execute the user's query.

However, in testing, it seems like it's possible for the user to just RESET ROLE and then run their query as the more privileged role, which is a non-starter.

I tried using SET SESSION AUTHORIZATION, but the docs say

The session user identifier can be changed only if the initial session user (the authenticated user) had the superuser privilege.

And I'd rather not use a superuser role for the initial connection. Plus, when I've tested this approach in my client, it lets me SET SESSION AUTHORIZATION DEFAULT afterwards and return to the superuser role -- and I definitely don't want to risk that.

The PostreSQL GRANT docs say

[A role] may grant or revoke membership in itself from a database session where the session user matches the role.

I take that to suggest that I should be able to do something like

-- from a connection to the "service" role

SET SESSION ROLE tenant_1; -- switch to a new role
REVOKE SESSION ROLE service; -- prevent subsequent use of the "service" role

-- use this connection to execute queries as "tenant_1" without being able to use RESET ROLE to restore the "service" role privileges

But the only information I can really find on session roles is SET ROLE which doesn't have any info on revoking a role from a session.

Is it possible to revoke a role from a session, such that the rest of the session has to run in a more restricted role?

Or do I just have to be prepared to manage a whole bunch of username + password pairs?

Or is there some other way to accomplish what I'm trying?


Your Answer

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

Browse other questions tagged or ask your own question.