IDENT authentication failed for user

PostgreSQL under Debian by default uses IDENT authentication to authenticate a user connecting to the database. In english this means that you can only connect to the database with the same username as your unix login. This can be edited in /etc/postgres/pg_hba.conf (Host Based Authentication) by changing ident sameuser with trust to let anyone connect to the database with any username without a password, or crypt etc. The file is well commented, refer to it for more details.

If you change Debian's default setup, then you may get error messages every day from cron, as the postgres user connects to every database and runs some optimisations.

The Unix SuperUser can change to the postgres user to run the shell commands createdb and createuser -- if you create a database user with the same name as a Unix account, then that Unix account user can connect (when using ident sameuser). If you don't want to use the shell command, the postgres user could run psql template1 to connect via the interactive shell, and then do create database name; or create user name;.

Upgrading from 7.2 to 7.3

Check your pg_hba.conf file. Previously "password" authentication covered all forms of stored password. Now it is strict. If you are using MD5 passwords, it must be MD5.

Also, in SQL-land, LIMIT used to be fairly forgiving about its arguments. It would accept MySQL format without blinking. Now it's strict.

Finally, if you are a Debian user, don't believe the instructions in the update README! psql -e < dumpfile will not restore your database... you need to do psql template1 -e dumpfile.

You should create PGDATA directory first in Gentoo after an emerge install

If you get the following error:

# /etc/init.d/postgresql start
 * directory not found: /var/lib/postgresql/data
 * You should create PGDATA directory first.

then you need to issue ebuild /var/db/pkg/dev-db/postgresql-$VERSION/postgresql-$VERSION.ebuild config

Connecting over TCP/IP

A default server will only listen for connections on a socket. If you want to connect to it via a host name then you need to start the server with the -i flag

You will also need to edit your pg_hba.conf file to set access permissions.

Determining Table Primary Keys

Within a procedure I was writing I needed a way to determine which fields of the table where the primary keys. The following views can help to provide this information.

CREATE VIEW relid_pkeys AS SELECT indrelid AS relid, attname AS column_name
    FROM pg_index, pg_class, pg_attribute WHERE indisprimary='t' AND
    indexrelid=pg_class.oid AND pg_attribute.attrelid=pg_class.relfilenode;

CREATE VIEW table_pkeys AS SELECT c.relname AS table_name, p.column_name FROM
    relid_pkeys p, pg_class c WHERE p.relid=c.relfilenode AND c.relowner!=1 AND
    c.relkind='r';

Now you can go SELECT column_name FROM table_pkeys WHERE table_name='foo' and get back a list of the columns in the table foo that are primary keys. No records will be returned if foo does not have a primary key defined.

I added the restrictions on the relowner and relkind fields so that queries such as SELECT * FROM table_pkeys are a bit cleaner. The restrictions aim to filter the list so that it only contains user created tables. I chose the values based on trial and error, they work for me, but your milage may vary. If you are having problems simply remove those to restrictions.