Skip site navigation (1) Skip section navigation (2)

Proposed TODO: CREATE .... WITH OWNER;

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Proposed TODO: CREATE .... WITH OWNER;
Date: 2004-10-23 23:59:38
Message-ID: 200410231659.38093.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2004-10-24 00:05:52
Subject: Re: Proposed TODO: CREATE .... WITH OWNER;
Previous:From: Gaetano MendolaDate: 2004-10-23 22:08:22
Subject: Re: Question on the 8.0Beta Version

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group