Re: role self-revocation

On Mon, Mar 7, 2022 at 1:58 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > I'm not quite following this bit. Where would SET ROLE come into play
> > when we're talking about old dump scripts and how the commands in those
> > scripts might be interpreted by newer versions of PG..?
>
> No, the concern there is the other way around: what if you take a
> script made by newer pg_dump and try to load it into an older server
> that doesn't have the GRANTED BY option?
>
> We're accustomed to saying that that doesn't work if you use a
> database feature that didn't exist in the old server, but
> privilege grants are hardly that. I don't want us to change the
> pg_dump output in such a way that the grants can't be restored at all
> to an older server, just because of a syntax choice that we could
> make backwards-compatibly instead of not-backwards-compatibly.

Are you absolutely positive that it's that simple? I mean, what if the
SET ROLE command has other side effects, or if the GRANT command
behaves differently in some way as a result of the SET ROLE having
been done? I feel like a solution that involves explicitly specifying
the behavior that we want (i.e. GRANTED BY) is likely to be more
reliable and more secure than a solution which involves absorbing a
key value from a session property (i.e. the role established by SET
ROLE). Even if we decide that SET ROLE is the way to go for
compatibility reasons, I would personally say that it's an inferior
hack only worth accepting for that reason than a truly desirable
design.

See CVE-2018-1058 for an example of what I'm talking about. The
prevailing search_path turned out to affect not only the creation
schema, as intended, but also the resolution of references to other
objects mentioned in the CREATE COMMAND, as not intended. I don't see
a similar hazard here, but I'm worried that there might be one.
Declarative syntax is a very powerful tool for avoiding those kinds of
mishaps, and I think we should make as much use of it as we can.

--
Robert Haas
EDB: http://www.enterprisedb.com