Thread: pg_hba.conf file

pg_hba.conf file

From

"Dan MacNeil"

Date:

23 November 2002, 03:35:21

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