Re: CREATE ROLE IF NOT EXISTS - Mailing list pgsql-hackers
| From | David Christensen |
|---|---|
| Subject | Re: CREATE ROLE IF NOT EXISTS |
| Date | |
| Msg-id | CAOxo6XKHfLYsXMS_MfU0o+3D_KRojCfg=H9eUJfLijAChZcyWw@mail.gmail.com Whole thread Raw |
| In response to | Re: CREATE ROLE IF NOT EXISTS (Mark Dilger <mark.dilger@enterprisedb.com>) |
| Responses |
Re: CREATE ROLE IF NOT EXISTS
Re: CREATE ROLE IF NOT EXISTS |
| List | pgsql-hackers |
On Mon, Nov 8, 2021 at 1:22 PM Mark Dilger <mark.dilger@enterprisedb.com> wrote:
> On Nov 8, 2021, at 10:38 AM, Stephen Frost <sfrost@snowman.net> wrote:
> 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 don't see why this is "stretching things to a very questionable level". It might help this discussion if you could provide pseudo-code or similar for adding roles which is well-written and secure, and which benefits from this syntax. I would expect the amount of locking and checking for pre-existing roles that such logic would require would make the IF NOT EXIST option useless. Perhaps I'm wrong?
The main motivator for me writing this was trying to increase idempotency for things like scripting, where you want to be able to minimize the effort required to get things into a particular state. I agree with Stephen that whether or not this is a best practices approach, this is something that is being done in the wild via DO blocks or similar, so providing a tool to handle this better seems useful on its own.
This originally came from me looking into the failures to load certain `pg_dump` or `pg_dumpall` output when generated with the `--clean` flag, which necessarily cannot work, as it fails with the error `current user cannot be dropped`. Not that I am promoting the use of `pg_dumpall --clean`, as there are clearly better solutions here, but something which generates unusable output does not seem that useful. Instead, you could generate `CREATE ROLE IF NOT EXISTS username` statements and emit `ALTER ROLE ...`, which is what it is already doing (modulo `IF NOT EXISTS`).
This seems to introduce no further security vectors compared to field work and increases utility in some cases, so seems generally useful to me.
If CINE semantics are at issue, what about the CREATE OR REPLACE semantics with some sort of merge into the existing role? I don't care strongly about which approach is taken, just think the overall "make this role exist in this form" without an error is useful in my own work, and CINE was easier to implement as a first pass.
Best,
David
pgsql-hackers by date: