Hi all,
I just ran into a long-standing issue with the Pg privileges system.
It seems there is no way to restore a table's default privileges to what
they were directly after creating the table.
`REVOKE ALL ON table …` revokes not only privileges that were explicitly
granted using GRANT but also all regular privileges that were in effect
due to default privileges, so the owner can't SELECT from their own table
anymore. So this is not an option (although I *thought* it was and just
hosed my production system for an hour until I realized what had
happened).
Note that this is different from the "default privileges" managed through
`ALTER DEFAULT PRIVILEGES`. Tom Lane implemented a way to get rid of such
sets of modified default privileges back in April 2010:
http://archives.postgresql.org/pgsql-hackers/2010-04/msg00139.php
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=60bd2b1941c6211e973770e69cfa33367cc5db48
What *I'm* talking about here is this:
http://archives.postgresql.org/pgsql-sql/2010-06/msg00042.php
Whereas on Pg 8.3 it was at least possible to discern default privileges
(null, shown as an empty string) from an empty set of privileges (shown
as {}) in \z output, on Pg 9.0+ both are shown as an empty string, which
I think is particularly bad. Could \z and \dp be changed to show
"default" rather than an empty string when default privileges are in
effect for an object? Or, conversely, could we show "none" if there's
an empty set of privileges for an object?
Also, I would like to propose a new command for applying the current
default privileges as defined (or undefined) through `ALTER DEFAULT
PRIVILEGES` to one or more database objects:
=> APPLY DEFAULT PRIVILEGES TO TABLE foo;
=> APPLY DEFAULT PRIVILEGES TO ALL TABLES IN SCHEMA bar;
Alternatively, this could be made a variant on GRANT:
=> GRANT DEFAULT PRIVILEGES ON TABLE foo;
=> GRANT DEFAULT PRIVILEGES ON ALL TABLES IN SCHEMA bar;
But that might be misleading since it could effectively *remove*
privileges as well.
The important thing is: there should be a way to restore default
privileges on a database object.
What do you think?
-Julian
Attachment:
signature.asc
Description: This is a digitally signed message part.
