The pg_hba.conf file - Mailing list pgsql-novice

Hi all,
  I recently upgraded to 7.3 on my Debian system, and found that
the format of this file has changed slightly.  So, since I need to
revisit the file, I figured I might as well send a longish note.

  I've never found host based access with Postgres to be
intuitive.  Maybe this is good, maybe bad.  I think other people
have problems with it as well.  I'm picking some examples from
stuff on my system (which is Debian), not to complain about them,
just as a source of examples.

1 ----------------------------------
First off, we have 3 different kinds of records: local, host and hostssl.

local    DBASE  USER                   METHOD  [OPTION]
host     DBASE  USER  IP_ADD  IP_MASK  METHOD  [OPTION]
hostssl  DBASE  USER  IP_ADD  IP_MASK  METHOD  [OPTION]

To me, it would seem "more pleasing" if the local record looked
a little more like the host and hostssl records, with something
there for the IP_ADD and IP_MASK fields.  Ideas I've had are:
"x.x.x.x", "NotApplicable" and "NA".  Something.  That way field
#0 is always the access method, field #1 is the DBASE name, field
#2 is the user name, ....  For local access, the 4th and 5th fields
would just be ignored.

It's an idea anyway.  I can't say I've seen anything  on the mailing
list talking about the assymmetry of local versus host/hostssl.

2 --------------------------------
Next, authenication involving ident.  From what I've been told by
more security concious people than myself, that ident can not be
trusted off of the local machine.  And, I've seen some ident daemons
which apparently can be configured to lie.  Which leads me to ask
the question, is ident trustworthy for local connections?  Certainly
these lying ident daemons could lie on a network connection, but
are they also involved in local (socket) connections?  Or is it
the kernel which is responding with the UID of the other end of
the socket?

3 -----------------------------
It's not unusual for maintainence to be done via cron jobs on
the database(s).  I gather this probably needs to be done by
the postgres user in all cases (are there exceptions?).  Debian
currently suggests in do.maintainence
 local  all      ident sameuser
(this looks like a pre 7.3 line which should read?)
 local  all  all     ident sameuser

Another "superuser" type access to PostgreSQL is
PHPPGAdmin.  From the Debian README, I have a choice of
 local  all      password
and
 host  all  127.0.0.1  255.255.255.255  crypt
(Oh, neither line looks like a 7.3 line.)

Webmin?  I can't find any file under /usr/share/doc/webmin*
which talks about pg_hba.conf settings.  Surely it makes
assumptions on what is in pg_hba.conf for it to work.

To me, it sure looks like to use PHP PGAdmin I need to
make sure that
  $cfgServers[1]['local'] = false
so that I access PostgreSQL via the host 127.0.01 as I
can't think of any method which allows the do.maintainence
cron job to connect using ident and PGAdmin to connect using
password.  They are both working with all databases as the postgres
user, so the first line used (either ident or password) would get used
and the other ignored.

Anyway, above I have 2 differing needs just from a setup point
of view.  I also may have other local needs.  I don't have any
problems with the idea of local connections from the UNIX postgres
user being trusted to all databases.  But why do these
recommendations have such huge scope?  It's not unusual for a user
to have a database with the same name as his/her UID, so I guess
we could allow that by policy.  Pg_hba.conf allows for access to
databases owned by some group.  And I have some programs which do
not involve a UNIX UID, and would like to allow access via
passwords of some kind.  Wouldn't something like:
 local  all       postgres       ident sameuser
 local  sameuser  all            ident sameuser
 local  samegroup all            ident sameuser
 local  @l_other  all            md5
be a good, generic setup for pg_hba.conf involving local
connections?  (Where the file l_other, contained the names of
other local databases I wanted to let people access by password.)

3.1 ---------------------------------------------
In the above local ... example, I suspect having "ident sameuser"
as the authentication allowing access to the database "sameuser"
restricts completely, but the "all" wildcard for the user seems out
of place.  Something like:
 local sameuser sameuser  ident sameuser
seems to better describe the situation, that I only want these
connections by UNIX UIDs to databases with the same name as
the UNIX UID.

And I am only guessing that the line involving samegroup
works as written.

4 -------------------------------------------------
It's not unusual to see sample pg_hba.conf files, which have
a
  host  all all  0.0.0.0   0.0.0.0   reject
line at the end.  Should a person have similar lines for hostssl
and local connections?  I.e.:
 local  all   all            reject
 host  all  all   0.0.0.0 0.0.0.0  reject
 hostssl all  all  0.0.0.0 0.0.0.0  reject

Sorry for the too-longish note.  But, I'm a little unsure about
this, and I think others are as well.  Maybe it's just me.  :-)

Gord
--
Matter Realisations     http://www.materialisations.com/
Gordon Haverland, B.Sc. M.Eng. President
101  9504 182 St. NW    Edmonton, AB, CA  T5T 3A7
780/481-8019            ghaverla @ freenet.edmonton.ab.ca
780/993-1274 (alt.)