Proposed TODO: CREATE .... WITH OWNER;

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