Re: Additional role attributes && superuser review

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.

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?

So we would have only the "positive" actions in create:

CREATE USER foo REPLICATION EXCLUSIVE_BACKUP
(if you want no EXCLUSIVE_BACKUP privilege, just leave it out. It isn't
any more complicated than saying NOEXCLUSIVEBACKUP)

You could turn these off in ALTER:

ALTER USER foo NO EXCLUSIVE_BACKUP;
or perhaps
ALTER USER foo DROP EXCLUSIVE_BACKUP;
or some such.

(REVOKE would be nicer, I guess, but IIRC that conflicts with other
stuff. I guess another option, in line with the current optional WITH,
would be to have WITHOUT:
ALTER USER foo WITHOUT EXCLUSIVE_BACKUP;
But that looks a bit silly to me.)

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

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

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'

(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 wedded to any of this, but I think it ought to be at least given
some consideration.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services