People:
Having today spent 3.5 hours correcting a pg_dump file with permissions
problems, I've come to the inescapable realization that the "SESSION
AUTHORIZATION" concept is WAY too fragile.
Instead, we should have a "CREATE .... WITH OWNER username" extension to all
of our CREATE <object> statements. Then any backup, or fragment of a
backup, could be run by the superuser without fear that a bunch of objects
could end up owned by a user with no permissions on them. (And if you think
such a fear does not exist, try using "CHANGE OWNER" on about 80 database
objects, some of them with dependancies owned by other users, and then
pg_dump and restore. Fun, fun!).
CREATE followed by ALTER ... CHANGE OWNER would not be an adequate substitute.
The orginal owner of the object (in the case of a restore, the superuser)
retains all of their permissions on the object, which causes a lot of messy
GRANT statements.
Hmmmm ... this would also require a GRANT .... AS USER name. But those two
changes should simplify dump and restore enormously.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco