Re: CREATE ROLE IF NOT EXISTS

On Tue, Oct 19, 2021 at 4:29 PM Isaac Morland <isaac(dot)morland(at)gmail(dot)com>
wrote:

> On Tue, 19 Oct 2021 at 16:12, David Christensen <
> david(dot)christensen(at)crunchydata(dot)com> wrote:
>
>> Greetings -hackers,
>>
>> Enclosed is a patch that implements CREATE ROLE IF NOT EXISTS (along with
>> the same support for USER/GROUP). This is a fairly straightforward
>> approach in that we do no validation of anything other than existence, with
>> the user needing to ensure that permissions/grants are set up in the proper
>> way.
>>
>
> One little tricky aspect that occurs to me is the ALTER ROLE to set the
> role flag options: it really needs to mention *all* the available options
> if it is to leave the role in a specific state regardless of how it started
> out. For example, if the existing role has BYPASSRLS but you want the
> default NOBYPASSRLS you have to say so explicitly.
>
> Because of this, I think my preference, based just on thinking about
> setting the flag options, would be for CREATE OR REPLACE.
>
> However, I'm wondering about the role name options: IN ROLE, ROLE, ADMIN.
> With OR REPLACE should they replace the set of memberships or augment it?
> Either seems potentially problematic to me. By contrast it’s absolutely
> clear what IF NOT EXISTS should do with these.
>
> So I’m not sure what I think overall.
>

Sure, the ambiguity here for merging options was exactly the reason I went
with the IF NOT EXISTS route. Whatever concerns with merging already exist
with ALTER ROLE, so nothing new is introduced by this functionality, at
least that was my original thought.

David