VO Ipfix <ipfix5101@xxxxxxxxx> 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
