Re: Proposed TODO: CREATE .... WITH OWNER;

On Sat, 23 Oct 2004, Tom Lane wrote:

> Josh Berkus <josh(at)agliodbs(dot)com> 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