Thread: 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
On Thu, Nov 21, 2002 at 13:16:50 -0500, Dan MacNeil <dan@ltc.org> wrote: > > 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? In 7.3: CREATEDB DATABASE dbname OWNER username
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
Hi, I don't see command to rename database. Yes, i can drop/create/restore database but is there any problems to develop such command ? Fouad Fezzi