Re: allowing for control over SET ROLE

On Fri, 2022-12-30 at 22:16 -0800, Noah Misch wrote:
> create user unpriv;
> grant pg_maintain to unpriv with set false;
> create schema maint authorization pg_maintain
>   create table t (c int);
> create or replace function maint.f() returns int language sql as
> 'select 1';
> alter function maint.f() owner to pg_maintain;
> set session authorization unpriv;
> create or replace function maint.f() returns int language sql
> security definer as 'select 1';
> create index on maint.t(c);

I dug into this case, as well as some mirror-image risks associated
with SECURITY INVOKER. This goes on a bit of a tangent and I'm sure I'm
retreading what others already know.

The risks of SECURITY DEFINER are about ownership: by owning something
with code attached, you're responsible to make sure the code is safe
and can only be run by the right users. Additionally, there are a
number of ways someone might come to own some code other than by
defining it themselves. Robert addressed the SET ROLE, CREATE ... OWNER
and the OWNER TO paths; but that still leaves the replace-function path
and the create index paths that you illustrated. As I said earlier I'm
not 100% satisfied with SET ROLE as a privilege; but I'm more
comfortable that it has a defined scope: the SET ROLE privilege should
control paths that can "gift" code to that user.

The risks of SECURITY INVOKER are more serious. It inherently means
that one user is writing code, and another is executing it. And in the
SQL world of triggers, views, expression indexes and logical
replication, the invoker often doesn't know what they are invoking.
There are search path risks, risks associated with resolving the right
function/operator/cast, risks of concurrent DDL (i.e. changing a
function definition right before a superuser executes it), etc. It
severely limits the kinds of trust models you can use in logical
replication. And SECURITY INVOKER weirdly inverts the trust
relationship of a GRANT: if A grants to B, then B must *completely*
trust A in order to exercise that new privilege because A can inject
arbitrary SECURITY INVOKER code in front of the object.

UNIX basically operates on a SECURITY INVOKER model, so I guess that
means that it can work. But then again, grepping a file doesn't execute
arbitrary code from inside that file (though there are bugs
sometimes... see [1]). It just seems like the wrong model for SQL.

[ Aside: that probably explains why the SQL spec defaults to SECURITY
DEFINER. ]

Brainstorming, I think we can do more to mitigate the risks of SECURITY
INVOKER:

* If running a command that would invoke a SECURITY INVOKER function
that is not owned by superuser or a member of the invoker's role, throw
an error instead. We could control this with a GUC for compatibility.

* Have SECURITY PUBLIC which executes with minimal privileges, which
would be good for convenience functions that might be used in an index
expression or view.

* Another idea is to separate out read privileges -- a SECURITY INVOKER
that is read-only is sounds less dangerous (though not without some
risk).

* Prevent extension scripts from running SECURITY INVOKER functions.

[1]
https://lcamtuf.blogspot.com/2014/10/psa-dont-run-strings-on-untrusted-files.html

--
Jeff Davis
PostgreSQL Contributor Team - AWS