Обсуждение: Preventing in-session 'set role' commands
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? Any suggestions thoughts are welcome, however tangentialOn 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> 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