On Sat, 23 Oct 2004, Tom Lane wrote: > Josh Berkus <[EMAIL PROTECTED]> writes: > > Dennis and I are hashing this out on IRC. The second option would be to > > simply put SET SESSION AUTHORIZATION statements before each and every > > statement in the pg_dump. This would make each statement "atomic" as far as > > user ownership is concerned, with less changes than "WITH OWNER" would > > entail. > > Uh, isn't that how we did it before? Why is that better?
I havn't looked at what pg_dump do in the code. Josh showed some code generated by pg_dump that contains SET SESSSION ... and then some statement and a RESET SESSION AUTHORIZATION. When I saw that I simply asked; why do it issue the RESET at all? Wouldn't it be enough to just set the user whenever needed? Especially since Josh said that pg_dump got the resets wrong. In the extreme one could set the user before every statement but a better way is that pg_dump keeps track of who is the current user and then just issue a SET SESSION AUTH when needed. This sounds like what I though pg_dump were doing already, but probably wasn't since it got it wrong and Josh had a database where the owners after restore was messed up. Another observation is that SET SESSION AUTHORIZATION postgres; and RESET SESSION AUTHORIZATION; would be the same when postgres is the superuser. By not using the name of the superuser one get the benefit that one can restore as another superuser (but see the part about acl's below). Well, hopefully this is not a problem in 8.0 as you say. When discussing this, _another issue_ came up that made me thinking. Let me ask about that: When you alter the owner of an table with ALTER TABLE ... OWNER TO ... then it looks like it just sets the owner but does not alter the acl string at all (at least in 7.4 where I tested). So after one have altered the owner it's possible that the new owner does not have any rights set for the object. and (worse) that the old owner still have rights set. It's also more complicated since in some cases the acl is set to NULL which means that it has the default priviledges. And the default privileges always include all privileges for the owner. So if the acl is NULL then the old owner looses its privileges and the new gets them. Here we have a different semantics based on an implementation detail that's not very visible to the user. -- /Dennis Björklund ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend