Client Authentication
From PostgreSQL Wiki
Who is allowed to connect to the database is controlled by a file in the root of your database directory named. pg_hba.conf. A default file is created when you run initdb to create a database cluster.
What permissions exist by default depends on how initdb was called. By default, new clusters are created with the 'trust' scheme, where any local user is allowed to connect to the database. However, some PostgreSQL packagers may change this. For example, if you use the RedHat 'service initdb' to create your cluster, it calls initdb like this:
initdb --pgdata='$PGDATA' --auth='ident sameuser'
Which uses the not particularly popular ident scheme to figure out if a user is allowed to connect, much frustrating those who aren't aware of this.
A typical recommended setup for network access to the database takes the local LAN address and only allows clients who authenticate using a secure MD5 password. The entry in pg_hba.conf will look like this:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 192.168.1.0/24 md5
This only allows clients with IP addresses from 192.168.1.0-192.168.1.255 to connect, and only then if they provide the correct password for the user. Database user's passwords are set when you create the user with CREATE ROLE and can be modified with ALTER ROLE.
Note that network access like this is only allowed at all if the postgresql.conf setting for listen_addresses allows it.
LDAP authentication
To pull off ldap authentication, you need to replace 'md5' with
:ldap "ldap://server/dc=domain,dc=local;DOMAIN\"
Where server and domain are both pretty self explanatory. This is all one user reporting on this feature had to do to accomplish ldap authentication. They use this method for all network addresses in the 192.168.x.x range, and md5 for localhost, so that it can use a username that doesn't exist in ldap for backups. This way only the server itself can initiate backups on the superuser account.
Related articles
- PostgreSQL and pam_ldap by Adrian Nida
- NSS Authentication with libnss_pgsql by David Ford
- Authenticating PostgreSQL Clients by SecurityProNews (2002-05-21)
- LDAP Authentication against AD by Joey Wang (2007-04-13)
