Re: Open 7.3 items

On Wednesday 14 August 2002 03:04 pm, Tom Lane wrote:
> Lamar Owen <lamar(dot)owen(at)wgcr(dot)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(at)template1' -- but lowen(at)pari
> > wouldn't have access to template1, right?

> If not, standard things like "psql -l" won't work for lowen(at)pari(dot) 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(at)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-test
maillabels
testing2
template1
template0
pari
(6 rows)

So AFAICT if I were psql I would parse the unadorned lowen as
'lowen(at)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(at)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(at)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(at)template1 has access across the install (subject to GRANT/REVOKE, of
course). lowen(at)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(at)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(at)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