On Wednesday 14 August 2002 03:49 pm, Bruce Momjian wrote: > Lamar Owen wrote: > > On Wednesday 14 August 2002 03:29 pm, Vince Vielhaber wrote: > > > Hate to complicate things more, but back to a global username, say > > > you have user "lowen" that should have access to all databases. What
> > places. So I guess the solution is that wherever a user name is to be > > stored, the fully qualified form must be used and checked against, with > > @template1 being a 'this user is everywhere' shorthand. > Yes, Vince is on to something with his quote above. > If we have users with and without @, we get into the situation where > users without @ may become users with @ when their usernames collide > with existing user/db combinations already created. The point is that > those two namespaces do collide and will cause confusion. But that's the exact problem I was trying to address -- as far as the backend is concerned, there isn't a user without @ -- the incoming connection from a user without @ is translated into a connection coming from user@template1. > Then you start to get into the situation where you always add @ and make > @template1 a special case. However, remember that this flag can be > turned on and off after initdb, so you need to be able to get in to set > things up without great complexity _and_ the @template1 would not be > passed in from the client, if for no other reason that the username is > only 32 characters. It is the backend doing the flagging, and therefore > the user can't say 'I am dave@templatge1' vs 'I am dave@connectdb'. Ok, how do I as a client specify the @dbname for the user? By the database I'm connecting to? That IS a wrinkle. But it does make sense, as lowen@pari won't be able to connect to any other database, right? So, where's this new notation going to get used, again? I must have misunderstood something. So, if we have a namespace collision -- then we have to make the implementation have the restriction that a global username can't exist as a database-specific username -- but two or more database-specific usernames can be the same. So, have a trigger on insertion of a user that checks for an existing user attached to template1 (again, for consistency -- installation wide templates are in template1 -- installation-wide users should be too) -- and then aborts the CREATE USER if so. > This is how I got to the installuser hack in the first place. In fact, > even the install user, typically 'postgres' has a problem because if you > create 'postgres@db1', 'postgres' will have trouble connecing to db1 as > themselves. I think we can live with one user who is special/global, but > not more than one because of the confusion it would create. If you say CREATE USER lowen@pari for the syntax, the create user trips the trigger, which checks for lowen@template1 and aborts if so. CREATE USER lowen@template1 does the same, checking for ANY user lowen. Namespace collision averted? CREATE USER lowen would be the same as CREATE USER lowen@connecteddb, so that the subsuperuser for connecteddb can just CREATE USER without qualifying -- the command line createdb could take the @dbname argument, splitting it out and connecting to the proper database. This has ramifications, I admit. And just saying that unqualified CREATE USER's should create the user@template1 introduces its own problems. > I can change the way this works, but we need a solution without holes. Trigger on the holes. But if I can't (or shouldn't) be able to specify the @dbname from the client, there is GOING to be a namespace collision if installation-wide users of ANY name are allowed (which is what you've already said -- just repeating for emphasis). Or we will have to forbid the postgres user from being reused -- trigger on CREATE USER and abort if user=postgres, I guess. Now as to the toggling of the feature -- what happens when you have lowen@pari and lowen@wgcr coexisting, and you turn off the feature? Which password becomes valid for the resultant singular user lowen? IMHO, if two or more users of the same name occurs, then you shouldn't be able to turn the feature off. I know you've already put alot of work into this, Bruce. But what if the feature isn't toggled, but always there, just waiting to be exploited by CREATE USER user@db, with the default CREATE USER always putting the user into association with the currently connected database? Is there bad overhead involved? Is it something that could break installations not using the feature? Or should CREATE USER with an unqualified username default to @template1 (what I originally thought it should). -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
