Use of password/crypt authentication

I put together this readme on password and crypt authentication in
answer to a request from a user of the Debian package.

Please check it and point out any errors or missing information.

=========================================================================

How to use clear or encrypted passwords for PostgreSQL access:
=============================================================

Use lines such as

  local        all                password
  host        192.137.23    255.255.255.0    crypt

in /etc/postgresql/pg_hba.conf; then you can use

   CREATE USER user WITH PASSWORD password...

to create a new user with the specified password, or

   ALTER USER user WITH PASSWORD password...

to change the password of an existing user.  Any user with create-user
privilege can alter a password for any user, *INCLUDING* the postgres
super-user.

If connecting with psql, use the -u option; the user is prompted for username
and password.  If you don't use -u, the connection fails.

If using your own program with libpq, it is up to you to collect the user name
and password from the user and send them to the backend with PQsetdbLogin().
[How can one know, with libpq, whether this is necessary?]

Passwords are stored in pg_shadow in clear, but if `crypt' authentication is
specified, the frontend encrypts the password with a random salt and
the backend uses the same salt to encrypt the password in the database.
If the two encrypted passwords match, the user is allowed access. If the
authentication method is `password', the password is transmitted and
compared in clear.

If passwords are turned on, it becomes impossible to connect as
a user, if no password is defined for that user.  Neither can you use
\connect to change user within psql.

<Debian-specific>
If you turn on passwords for local, the default do.maintenance cron job
will stop working, because it will not supply a username or password.
In this case, you must alter /etc/cron.d/postgresql to supply the
user and password for the postgres superuser, with the -u and -p options.
It will then be necessary to change the permissions on /etc/cron.d/postgresql
to make it readable by root only.
</Debian-specific>


Problems with password authentication
=====================================

1. There is no easy and secure way to automate access when passwords are
   in use.  It would be good if the postgres super-user (as identified by
   Unix on a Unix sockets connection) could bypass the authentication.

2. pgaccess has no mechanism for specifying username and password. It cannot
   be used if password/crypt authentication is turned on for host
   connections from localhost.

3. In general, passwords are insecure, because they are held in clear
   in pg_shadow.  Anyone with create-user privilege can not only alter but
   also read them.  They ought to be stored with one-way encryption, as
   with the Unix password system.

4. The postgres super-user's password can be changed by anyone with
   create-user privilege.  It ought to be the case that people can
   only change their own passwords and that only the super-user can change
   other peoples' passwords.

5. If passwords are turned on, the -u option must be supplied to psql. If
   it is not, psql merely says "Connection to database 'xxxx' failed.".  A
   more helpful error message would be desirable.
=========================================================================

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "And Jesus answering said unto them, They that are
      whole need not a physician; but they that are sick. I
      come not to call the righteous, but sinners to
      repentance."                     Luke 5:31,32