From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | VO Ipfix <ipfix5101(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Preventing in-session 'set role' commands |
Date: | 2019-08-06 13:47:54 |
Message-ID: | 19556.1565099274@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
VO Ipfix <ipfix5101(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-08-06 13:50:37 | Re: vacuum & free space map |
Previous Message | Tony Shelver | 2019-08-06 12:55:48 | Re: Does pgadmin4 work with postgresql 8.4? |