Re: Additional role attributes && superuser review

Alvaro,

* Alvaro Herrera (alvherre(at)2ndquadrant(dot)com) wrote:
> Stephen Frost wrote:
> > So you'd advocate EXCLUSIVE_BACKUP and NOEXCLUSIVE_BACKUP? Or
> > NO_EXCLUSIVE_BACKUP? Or..? If this was a green field, I think we might
> > actually use spaces instead, but I'm really not sure we want to go
> > through and redo everything that way at this point.. We'd end up
> > breaking a lot of scripts that currently work today and I'm really not
> > convinced it's better enough to justify that.
>
> Well, all things considered, we actually are in a green field here,
> aren't we. We don't have to break old scripts, but no existing script
> is using
> ALTER USER foo NOEXCLUSIVEBACKUP
> because that option doesn't currently exist.

Sorry for not being clear, I was talking about how we'd deal with the
existing ones (to try and have a consistent approach across all of the
options).

> That being so, I would consider the idea that the NO bit is a separate
> word rather than run together with the actual privilege name. And given
> that CREATE has all the options default to "NO", there is no need to
> have these options at all in CREATE, is there?

That's a good point, except that INHERIT is actually on by default, and
LOGIN defaults to 'on' if you use CREATE USER, and 'off' if you use
CREATE ROLE. If they were actually all 'no' by default then this
simplication would work but it's not and therefore I don't think we want
to have some which are allowed at CREATE time with 'on' and some with
'off' depending on whatever the default is. Today, you can write a
script to easily duplicate an existing role by just looking at what is
on and off and using X and NOX. This approach would require that script
to know what's valid at CREATE time and what isn't.

> Then things such as
> ALTER USER foo NOREPLICATION
> become synonyms for
> ALTER USER foo NO REPLICATION (or whatever)

I'm not against supporting this and if it's a synonym then it won't
break existing scripts. I can look into this, but it ends up being an
independent discussion from actually adding these new options.

> Something like that would be my choice for UI, anyway. The existing
> stuff seems to clutter it overly, and while it works sorta OK for half a
> dozen privs, it becomes clunkier as you have more of them. From the
> perspective of docs, I think this whole thing becomes simpler if you
> split out the NO from each privilege name; currently we have

I agree that it does get clunkier and this is certainly an interesting
discussion as we might be able to do something better, which would
certainly be great.

> alvherre=# \h alter user
> Command: ALTER USER
> Description: change a database role
> Syntax:
> ALTER USER name [ [ WITH ] option [ ... ] ]
>
> where option can be:
>
> SUPERUSER | NOSUPERUSER
> | CREATEDB | NOCREATEDB
> | CREATEROLE | NOCREATEROLE
> | CREATEUSER | NOCREATEUSER
> | INHERIT | NOINHERIT
> | LOGIN | NOLOGIN
> | REPLICATION | NOREPLICATION
> | CONNECTION LIMIT connlimit
> | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
> | VALID UNTIL 'timestamp'
>
> And it would become something like
>
> ALTER USER name [ WITH ] { [ NO ] privilege | option } [ ... ]
>
> where privilege can be:
> SUPERUSER | CREATEDB | CREATEROLE
> | CREATEUSER | INHERIT | LOGIN
> | REPLICATION | EXCLUSIVE_BACKUP | ...
> and option can be:
> CONNECTION LIMIT connlimit
> | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
> | VALID UNTIL 'timestamp'

With the 'NO' distinct, as discussed above, it seems like we should be
able to support this.. I certainly like it more too.

> (the NOSUPERUSER etc forms of the old options would continue to work for
> the sake of backwards compatibility, but we wouldn't provide NO-
> prefixed forms of the new privileges.)

I'm not sure that I like that.. My initial reaction is that it'd
complicate the code and confuse people coming to this later- and to what
purpose..? To force everyone to use 'NO<space>' instead? Maybe in some
far off future we could say that the old 'NOX' format was deprecated and
remove it, but I have a hard time seeing the need to.

> I'm not wedded to any of this, but I think it ought to be at least given
> some consideration.

Certainly, and I like where you're going with this, just seems like
there's a couple wrinkles to deal with.

Thanks!

Stephen