Re: pg_hba.conf file review

Hi dan

IF possible i suggest to put a restriction from where your users come with
netwotk and mask.
I think that your solution is to complicate and suggest that all database is
owned by postgres with strong password and give your user full grant for a
single database.
so you haven't to manage a multiples users-passwords files

What do you think about that?


Fouad

-----Message d'origine-----
De : pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]De la part de Dan MacNeil
Envoyé : jeudi 21 novembre 2002 19:17
À : pgsql-admin@postgresql.org
Objet : [ADMIN] pg_hba.conf file review


It would be great if somebody could review our pg_hha.conf file below
to see if it does what we want it to, no more and no less.


The goals for our pg_hba.conf file are:

1) In an emergency allow somebody with operatating
   system root privs access to all the databases with full privs.

2) Allow local shell users to connect to databases named after
   themselves wit no authentification.
            a) As we run suexec, this helps novice cgi scripters avoid
putting
                passwords in the script.
            b)  We also set the environment variable PGDATABASE to
LOGNAME
                  so people can just run psql w/o prarams and get to
their database

3) Allow remote users to connect to a particular database with a
password

4) Allow superuser (postgres) to attach to any database

5) Deny everything else.

A related question:

Right now we give a user full privledges to the database, named after
the user by

1) creating the user with CREATEDB
2) Give the user the right to connect to the database dummy
3) creating a database as that user
4) taking away the users rights to CREATEDB

Is there a less kludgy way?

GRANTing ALL on a database?

Changing ownership of a database?



######################################################################

# line below removes almost all security any local user can connect as
# any user including the postgres database superuser UNCOMMENT WITH
# CAUTION

#local          all             trust

# kludge to allow users to connect to server to create database
# w/o giving everyone access to postgres account
local           dummy           password        dummy_users.list

# Users can locally connect to databases named after their OS login
# names For example, felicia can connect to a database called felicia
# but not to a database called john. Requires apt-get install ident2
# configure

#connect    database    IP number    mask       auth meth auth arg
local       sameuser                            ident     sameuser
host        sameuser    127.0.0.1   255.0.0.0   ident     sameuser

# If above method does not apply (felicia tries to connect
# to database john) method below will be tried and user
# will be asked for a password that doesn't exist and
# there will be FAILURE (a good thing)

#connect   database     security      password file
local      utec         md5           utec_local_users.list

# Give admin types ability to connect to ALL databases locally
# Get list of admins from FILE: admin_users.list in DIR $PGDATA
#
# Admin user must have password in database system catalog
# table pg_shadow. See docs for ALTER USER to set password
# entry in pg_shadow
#
# Only super user postgres has automatic privs (grants) to
# objects in database.


#connect        database        security method   file with users
local           all             password          admin_users.list

#LOCAL ACCESS WITH PASSWORD
# ---if user is in password file for database
# ---and has password in file (bad)
#    or in pg_shadow table via ALTER USER


#REMOTE_ACCESS

# It is possible to allow access only from certain IP numbers

#connect  database      IP num    mask to xor
host      acarvalh      0.0.0.0   0.0.0.0     md5 acarvalh_users.list
host      dsiegal       0.0.0.0   0.0.0.0     md5 dsiegal_users.list
host      omacneil      0.0.0.0   0.0.0.0     md5 omacneil_users.list
host      felicia       0.0.0.0   0.0.0.0     md5 felicia_users.list
host      john          0.0.0.0   0.0.0.0     md5 john_users.list
host      calendarlctc  0.0.0.0   0.0.0.0     md5
calendarlctc_users.list
host      utec          0.0.0.0   0.0.0.0     md5
utec_remote_users.list

# reject all connections from all hosts not granted above
host    all             0.0.0.0       0.0.0.0      reject


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org