Re: Open 7.3 items - Mailing list pgsql-hackers

On Wednesday 14 August 2002 03:04 pm, Tom Lane wrote:
> Lamar Owen <lamar.owen@wgcr.org> writes:
> > Appending '@template1' to unadorned usernames, and giving inherited
> > rights across the installation to users with template1 rights?  Then you
> > have the unadorned 'lowen' becomes 'lowen@template1' -- but lowen@pari
> > wouldn't have access to template1, right?

> If not, standard things like "psql -l" won't work for lowen@pari.  I don't
> think we can get away with a scheme that depends on disallowing access
> to template1 for most people.

Ok, maybe I'm really off base, but if I connect to database pari as 
lowen@pari, isn't pg_database present there?  I just tried here:
createdb pari
psql pari
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms      \h for help with SQL commands      \? for help on internal slash commands
    \g or terminate with semicolon to execute query      \q to quit
 

pari=# select datname from pg_database; datname
------------acs-testmaillabelstesting2template1template0pari
(6 rows)

So AFAICT if I were psql I would parse the unadorned lowen as 
'lowen@template1' and connect to template1 if not otherwise specified.  If 
the fully qualified database user (FQDU) is present, parse the database name 
out and connect to that database, then issue the SQL to do the -l or 
whatever.  The @pari would just override the normal default of template1, 
right?  So a 'psql -U lowen@pari -l '  would connect to database pari 
(subject to permissions) and select datname from pg_database there.

What else am I missing, Tom?  ISTM I don't need access to template1 -- 
although I wasn't necessarily suggesting eliminating that.  I was more 
suggesting:
lowen@pari has read access to those parts of template1 necessary for normal 
functioning, full access (subject ot GRANT/REVOKE) of pari, and no access to 
other databases;
lowen@template1 has access across the install (subject to GRANT/REVOKE, of 
course). lowen@template1 = lowen (unadorned).  That was the answer, I 
thought, to the question Bruce had.  There would be NO unadorned usernames 
then, and no special handling EXCEPT of the template1 database, which is 
already a special case.

Now, can we support the idea of 'postgres@pari' being a superuser for pari but 
not for the rest of the install?  Meaning no CREATE DATABASE right, as that 
would require write access to template1?  That's OK I believe, as I would 
assume a 'tied to a database' superuser shouldn't be allowed to create a new 
database to which he isn't going to have access..... The full ramifications 
of this structure could prove interesting.

The supersuperuser 'postgres' becomes postgres@template1 -- template1 becoming 
the consistent default database (for connecting as well as user membership).  
As anything added to template1 becomes part of any subsequently added 
databases, being a user in template1 becomes an installation-wide user.

And the user never really has to explicitly state @template1 -- they could 
just leave off the @template1 and everything works as it does now.

Yes, there are complications, but not great ones, no?
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11