Re: OWNER TO on all objects - Mailing list pgsql-hackers

> That's a fair point, but you have to admit that it's a bit abstract
> while Chris has a real problem he needs to solve.  Our dumps are awfully
> low on the SQL-compliance scale anyway :-(

We could keep around an option for dumping the auth statements instead 
of alter statements perhaps.

> Sure, but we're not fixing the privilege system this time round (unless
> you have work in progress you haven't mentioned ;-)). 

I don't think any fix to privs system which creates a situation where 
you cannot drop CREATE privilege from someone who owns a table is jsut 
silly.

> This brings up a question for Chris, which is whether he's implemented
> this in a way that forces the decision at pg_dump time, or whether
> it is made during pg_restore.  I would definitely agree that we need
> to postpone the choice of which to do till pg_restore.  In other words,
> a dump archive should only show object ownerships and not prejudge
> how those ownerships will get set during the restore session.

I've implented it exactly like comments are implemented.  I just created 
a dumpOwner() function that adds an archive entry to the current object.   It appears in the pg_dump.c basically
wherevera dumpComment() 
 
appears, but always before the dumpAcls() if there is one.

What we need to do is decide on the exact semantics of how this is going 
to work, and then I can make all the (hopefully small) changes required 
to make it work.

OK, are these the requirements?  Please comment.

* I fix ALTER OWNER to allow it to work if you are NOT a superuser, but 
ARE the existing owner.  This makes it work just like set session auth 
and means that if your dump includes only stuff you own, it will still work.

* Text mode dumps
 - I issue alter owner after every object creation, suppressing ALL 
session auths, including COPY
 - I keep a switch to disable alter owner and dump set session instead.  Is this really necessary?
 - The -S option only affects enabling and disabling triggers and i 
don't have to worry about it
 - The only difference is data-only dumps - we still need set session 
auth?  Actually, no - read the next point.
 - How does the above point affect full dumps that include schema and 
data?  In my proposal, the copy commands will run as the user running 
the script, not the table owner anymore.  Presumably, the user running 
the script is a superuser.  Given that it is possible for a table owner 
to revoke their own INSERT privilege on their table, the existing 
behaviour is broken anyway.
 - The --no-owner option means no alter owner or session auth 
statements are dumped.
 - pg_dump currently in the case when the owner of a table no longer 
exists, dumps SET SESSION AUTHORIZATION DEFAULT.  I will simply omit the 
ALTER OWNER command.

* Custom format dumps
 - OK, I admit I have little experience with this format.
 - The alter owner objects will be stored as toc entries just like 
comment on objects.
 - They should pop back out of the archive when creating a text dump 
from a binary one, identical to the text format.
 - With respect to Tom's question about restore-time option - how is it 
different to now?? A that moment, we have the pg_restore -O option to 
not restore the session auth commands - what needs to change?  I just 
won't output the ALTER OWNER commands so everything will be owned by 
whoever runs pg_restore.

Does that seem like the way to go?

Chris