So I was thinking about various annoying admin/security issues
recently, so I came up with this: a new type of user called a
“superowner”. It’s somewhere between a superuser and a normal user.
Superowner would own all objects defined by users, so it would do
useful things in contexts where superuser is not available.
It seems easiest to make this a property at database level. There is no default.
ALTER DATABASE name
SUPEROWNER TO { new_owner | CURRENT_USER | SESSION_USER }
CREATE DATABASE name SUPEROWNER [=] new_owner
Changes to various commands highlighted by ** **, in existing text from docs.
Changes to GRANT - Roles having CREATEROLE privilege can grant or
revoke membership in any role that is not a superuser **or
superowner**.
It should be noted that database superusers can access all objects
regardless of object privilege settings. This is comparable to the
rights of root in a Unix system. As with root, it's unwise to operate
as a superuser except when absolutely necessary.
**Superowners for a database can access any table, view, materialized
view, index or sequence in that database regardless of object
privilege settings. Superowners cannot create untrusted procedures,
administer catalog tables or schemas or perform internal operations
reserved only for superusers .**
If a superuser **or superowner** chooses to issue a GRANT or REVOKE
command, the command is performed as though it were issued by the
owner of the affected object. In particular, privileges granted via
such a command will appear to have been granted by the object owner.
Changes to ALTER ROLE - Superusers can change anyone's session
defaults. Roles having CREATEROLE privilege can change defaults for
non-superuser roles. **Superowners of a database can change defaults
for that role on their database only.** Ordinary roles can only set
defaults for themselves. Certain configuration variables cannot be set
this way, or can only be set if a superuser issues the command. Only
superusers can change a setting for all roles in all databases.
Any issues, additions or changes?
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
