Re: CREATE DATABASE WITH OWNER '??';

The Hermit Hacker writes:

> Huh? Since when are passwords setup in pg_hba.conf?

The fact that password authenticaton is to be used for a given (set
of) databases is communicated through pg_hba.conf. Furthermore, the use of
a separate password file can be specified in pg_hba.conf (which is
currently the only way to disallow access to certain users when using
passwords).

> If a database has password set,

A database doesn't "have a password set", a user does. A database may be
accessible through password authentication (see above).

> and user doesn't have password to make the initial connect, they
> should be refused at the postmaster end of things, and never get to
> the backend.

But this is not what you said earlier. You said superusers or database
owners should get through either way. I said that doesn't work under this
model because you must pass password authentication first to be allowed to
pose as a particular user.

> If they have a passwd and its right, then they should get into the
> backend, where the first check is to see if they are allowed to access
> the database itself ...

That makes a lot of sense but as I mentioned earlier you would have to
start up backends for a much higher fraction of potential connections, to
which you objected. Tom pointed out that trying to avoid this might be
pretty pointless though. I concur.

On the implementation side this would probably mean some shared system
relation pg_dbaccess ( dbid, userid ). Then you could overload GRANT and
REVOKE to act on this table. We could replace the database name field in
pg_hba.conf with some special character (e.g., `-') to indicate "look in
pg_dbaccess".

I think I'd like that. It cleanly separates host-based authentication from
database-based authorization...

(On the other hand, what it doesn't accomplish is `use this auth. method
only for these users' or `use this method, or failing that this other
method'-- as people occasionally request -- but IMHO this is better left
to something like PAM.)

--
Peter Eisentraut Sernanders väg 10:115
peter_e(at)gmx(dot)net 75262 Uppsala
http://yi.org/peter-e/ Sweden