From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Stephen Frost <sfrost(at)snowman(dot)net>
Cc: Daniel Gustafsson <daniel(at)yesql(dot)se>, David Christensen <david(dot)christensen(at)crunchydata(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Date: 2021-11-08 18:59:12
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> I don't quite follow this. The entire point of Alice writing a script
> that uses IF NOT EXISTS is to have that command not fail if, indeed,
> that role already exists, but for the rest of the script to be run.
> That there's some potential attacker with CREATEROLE running around
> creating roles that they think someone *else* might create is really
> stretching things to a very questionable level- especially with
> CREATEROLE where Charlie could just CREATE a new role which is a member
> of Bob anyway after the fact and then GRANT that role to themselves.

I agree that as things stand, CREATEROLE is powerful enough that Charlie
doesn't need any subterfuge to become a member of the Bob role. However,
in view of other work that's going on, I think we shouldn't design the
system on the assumption that it'll always be that way. As soon as
there exist roles that can create roles but cannot make arbitrary
privilege grants, this becomes an interesting security question.
Do you really think that's never going to happen?

My concern here is basically that the semantics of CINE --- ie, that
you don't really know the initial properties of the target object ---
seem far more dangerous for a role than for any other sort of object.
The possibility of unexpected grants on or to that role means
that you may be giving away privileges unintentionally.

> The reason this thread was started is that it's a pretty clearly useful
> thing to be able to use IF NOT EXISTS for CREATE ROLE and I don't agree
> with the justification that we shouldn't allow it because someone might
> use it carelessly.

I'm not buying the argument that it's a "clearly useful thing".
I think it's a foot-gun, and I repeat the point that nobody's
actually provided a concrete use-case.

regards, tom lane

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Stephen Frost 2021-11-08 19:04:05 Re: Allow root ownership of client certificate key
Previous Message Stephen Frost 2021-11-08 18:38:53 Re: CREATE ROLE IF NOT EXISTS