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