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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster