Thread: Proposed TODO: CREATE .... WITH OWNER;
People: Having today spent 3.5 hours correcting a pg_dump file with permissions problems, I've come to the inescapable realization that the "SESSION AUTHORIZATION" concept is WAY too fragile. Instead, we should have a "CREATE .... WITH OWNER username" extension to all of our CREATE <object> statements. Then any backup, or fragment of a backup, could be run by the superuser without fear that a bunch of objects could end up owned by a user with no permissions on them. (And if you think such a fear does not exist, try using "CHANGE OWNER" on about 80 database objects, some of them with dependancies owned by other users, and then pg_dump and restore. Fun, fun!). CREATE followed by ALTER ... CHANGE OWNER would not be an adequate substitute. The orginal owner of the object (in the case of a restore, the superuser) retains all of their permissions on the object, which causes a lot of messy GRANT statements. Hmmmm ... this would also require a GRANT .... AS USER name. But those two changes should simplify dump and restore enormously. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> Instead, we should have a "CREATE .... WITH OWNER username" extension to all
> of our CREATE <object> statements.
The main objection to this is that it makes the dump completely
unportable.
> CREATE followed by ALTER ... CHANGE OWNER would not be an adequate substitute.
> The orginal owner of the object (in the case of a restore, the superuser)
> retains all of their permissions on the object, which causes a lot of messy
> GRANT statements.
Not in 8.0.
regards, tom lane
Tom, > The main objection to this is that it makes the dump completely > unportable. That's a powerful argument. 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. I can't imagine that it would slow down restoring much, and could even be helped by making SET SESSION AUTHORIZATION realize it didn't have to do anything if that was already the current user (does it now?). > Not in 8.0. Of course not. I'm talking for 8.1, or later. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
unsubscribe-digest
Josh Berkus <josh@agliodbs.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?
>> Not in 8.0.
> Of course not. I'm talking for 8.1, or later.
No, you misunderstood me. The bug of which you are complaining (namely,
ALTER OWNER not fixing the ACL list) is gone in 8.0; therefore you are
arguing from a faulty premise about whether this change is needed.
regards, tom lane
Tom, > No, you misunderstood me. The bug of which you are complaining (namely, > ALTER OWNER not fixing the ACL list) is gone in 8.0; therefore you are > arguing from a faulty premise about whether this change is needed. Aha, I misunderstood the terse phrasing ;-) Will have to try destruction test on 8.0. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On Sat, 23 Oct 2004, Tom Lane wrote: > Josh Berkus <josh@agliodbs.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
Dennis, > 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). <snip> > 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. <snip> Chris K-L came on IRC after you logged off last night. Apparently, he's already fixed all of the above for 8.0. Since I have a "destruction test" available, I'll see how it does. -- Josh Berkus Aglio Database Solutions San Francisco