Configuring PostgreSQL to use Database Stored Passwords

One of the neat features of PostgreSQL is it's flexible authentication. It allows use to map system logins to specific postgres users using pg_ident.conf and configure specific trust levels for any users, ip ranges/sockets, and databases you want in pg_hba.conf

However, what sometimes confuses a newcomer to Postgres (and in my experience MySQL folks) is that on on a number of distros the default installation does not really require them to enter the password when logining into "psql" or any other DB GUI. In fact many people continue to provide the password not knowing it is not actually having any effect.

Please note that I use Gentoo as a example disto, but only affects the defaults, such as file location and usename choices.

This happens because the default configuration of Postgres to automatically authenticate all localhost users. This is how it is done.

Locate and open Postgres pg_hba.conf file. (On Gentoo, it is in "/var/lib/postgresql/8.4/data/" where 8.4 is the current Postgres version.

Look at he following lines:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               trust

As you can see the authentication method for local access, for all uses and all database and local IPs is set to "trust" which means they will be logged in without any password.

Postgres comes with a large variety of authentication methods such as:
"trust", "reject", "md5", "password", "gss", "sspi", "krb5", "ident", "pam", "ldap" or "cert".  Note that "password" sends passwords in clear text; "md5" is preferred since it sends encrypted passwords.

STOP: If you change your method to "md5" right now and reload Postgres, you'll probably be stuck with no way to get in to the database. I suggest you first change the default admin password. On Gentoo the admin user is "postgres" though on FreeBSD and some other systems it is different.

Keep the method at "trust" for now.

Now login to psql" from console.

psql -U postgres

* Change the Postgre admin "username" according to your system.

On Gentoo, your user has to belong to "postgres" group to be able to login to postgres using a Unix socket. Alternatively you can use sudo or specify the localhost using -h. (Example: psql -U postgres -h localhost). This probably works on other distributions.

ALTER USER postgres WITH LOGIN ENCRYPTED PASSWORD 'yourpassword';

* Change the Postgre admin "username" and "yourpassword" according to your system.

As you might have guessed the "LOGIN" option makes sure the user can do interactive logins.

Now edit pg_hba.conf and replace "trust" with "md5" and reload Postgres.

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

All the options for configuring pg_hba.conf and Postgres authentication are available here:  http://www.postgresql.org/docs/8.4/static/auth-pg-hba-conf.html

INTERESTING NOTE:

By default Postgres users *are NOT* per-database but are instead server users. However that should not be confused with user-database permissions, as Postgres fully supports those.

If you want to look into configuring Postgres with per-database users look please read about "db_user_namespace" server options - http://www.postgresql.org/docs/8.4/interactive/runtime-config-connection.html