Re: CREATE ROLE IF NOT EXISTS

On Tue, Nov 9, 2021 at 10:22 AM Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * David Christensen (david(dot)christensen(at)crunchydata(dot)com) wrote:
> > Well, the CREATE OR REPLACE via just setting the role's attributes
> > explicitly based on what you passed it could work (not strictly DROP +
> > CREATE, in that you're keeping existing ownerships, etc, and can avoid
> > cross-db permissions/ownership checks). Seems like some sort of merge
> > logic could be in order, as you wouldn't really want to lose existing
> > permissions granted to a role, but you want to ensure that /at least/ the
> > permissions granted exist for this role.
>
> What happens with role attributes that aren't explicitly mentioned
> though? Do those get reset to 'default' or are they left as-is?
>

Since we have the ability to specify explicit negative options
(NOCREATEDB vs CREATEDB, etc), I'd say leave as-is if not specified,
otherwise ensure it matches what you included in the command. Would also
ensure forward compatibility if new permissions/attributes were introduced,
as we don't want to explicitly require that all permissions be itemized to
utilize.

> I suspect that most implementations will end up just explicitly setting
> all of the role attributes, of course, because they want the role to
> look like how it is defined to in whatever manifest is declaring the
> role, but we should still think about how we want this to work if we're
> going in this direction.
>

Agreed.

> In terms of least-surprise, I do tend to think that the answer is "only
> care about what is explicitly put into the command"- that is, if it
> isn't in the CREATE ROLE statement then it gets left as-is. Not sure
> how others feel about that though.
>

This is also what would make the most sense to me.

David