Re: CREATE ROLE IF NOT EXISTS

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.