Preventing in-session 'set role' commands

On Tue, Aug 6, 2019 at 10:26 AM VO Ipfix <ipfix5101@gmail.com> wrote:

Is this something that can be accomplished with PostgreSQL? Any suggestions thoughts are welcome, however tangential

Perhaps SET SESSION AUTHORIZATION?
<https://www.postgresql.org/docs/11/sql-set-session-authorization.html&gt;

Luca

Maybe check out the set_user extension:
https://github.com/pgaudit/set_user

Steve.

VO Ipfix <ipfix5101@gmail.com> writes:

Hello everyone! I am working on a multi-tenant (sigh) DB design using
schemas. I anticipate a bunch of junior developers coming in before we
fully mature our testing process, so SQLi is a concern. Basically, I want
to have a role for each tenant, and have a user/role that will est. a DB
session from a connection pool then perform a set role followed by a set
schema to the schema that the tenant role has grants to. So, my main
requirement is this: after these two (or more) commands are invoked, the
current role should not be able to do a set role to any other role (tenant)
other than itself. This is to prevent an attacker-controlled SQL query that
has set role as part of its payload.Is this something that can be
accomplished with PostgreSQL?

There's nothing built-in for that, but probably an event trigger could
be written to implement such a restriction.

As noted by another respondent, SET SESSION AUTHORIZATION might be a
better fit to your goals than SET ROLE. (I don't recall the exact
distinction between them -- ENOCAFFEINE -- but I think the former
gives up more privilege than the latter.) You'd still need a trigger.

regards, tom lane