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

> I think this is wrong, primarily because it's gonna be seriously
> incompatible with existing dump files.  The existing technique is
> that each TOC entry says who owns the object.  You should use that
> information and not have to rely on new additions to the file format.

Hrm.  OK, i might be able to do that, but constructing the ALTER OWNER 
commands will be difficult I think.  Each TOC entry seems to have the 
'OPERATOR (int4, none)' or 'TABLE blah' string in it from memory, so I 
assume I can pull that out.  I had failed to consider restoring from 
existing dump files actually.

>>* I fix ALTER OWNER to allow it to work if you are NOT a superuser, but 
>>ARE the existing owner.
> 
> 
> No, you don't.  That allows non-superusers to give away object
> ownership, which is well-established as a security hole; Unix
> filesystems stopped doing it years ago.

I worded that badly.  I meant "allow a user to change the owner of 
something to what it already is".  ie. Just make the no-op allowed by 
everyone.  session_auth already does this.  This means I can make text 
mode dumps that have ALTER OWNER in them and then even if you are not a 
superuser, so long as you own everything, you're ok.

>>  - 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.
> 
> This is why GRANT/REVOKE has to be postponed to the end.  I think it
> would be a lot simpler and more reliable if you also postponed ALTER
> OWNER.

Or we just always run the COPYs as the person executing the script, ie. 
remove session_auth from the COPY commands.

> Then you have fundamentally failed to grok pg_dump, and you should
> rethink everything you've done to date.  The way things work is that
> EVERYTHING effectively goes through a custom dump.  pg_dump in text
> mode is really pg_dump followed by pg_restore with the intermediate
> TOC just kept in memory temporarily.  Therefore, any time you have done
> something that you don't know how to convert into pg_restore behavior,
> it's because you were hacking the wrong place.  Everything you need to
> know about an object *must* go through the TOC representation and then
> be converted to text at the restore side.

I'm well aware of how it works - but compared to text format, i don't 
have as much experience.  I have done a fair bit of pg_dump hacking in 
my time...  All my changes work perfectly with pg_restore and the binary 
dump format.  I can pg_dump my production db using custom and plain text 
and there is no diff between the plain text and the plain text extracted 
from the binary dump.  I can also reload that dump and dump it again, 
and keep cycling it with no diff - without moving the grants/owners to 
the end.

I will have to spend some time investigating how to collect up the 
grants and stuff and move them to the end, if you still feel it is 
necessary.

>>  - 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.
> 
> 
> I think there needs to be a restore-side switch that chooses whether
> to emit ALTER OWNER or SET SESSION AUTH commands.  This is probably
> just for pro-forma SQL compliance, unless Peter has some brilliant
> insight about how to avoid ALTER OWNER.

Yes, well if I change it how you suggested in your first paragraph 
(which has to happen for backwards compatibility), then this wouldn't 
seem too hard.

Chris