Re: OWNER TO on all objects

> 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