Re: Open 7.3 items - Mailing list pgsql-hackers

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