PostgreSQL Notes

List Databases:

   psql -l

List Users:

   psql> select * from pg_user;

List Databases:

   psql -l

Create Database:

Login as postgres (best done with su from root)

   $ su -
   # su - postgres
   # createdb [database_name]

Create User:

Login as postgres (best done with su from root)

   $ su -
   # su - postgres
   # createUser [user_name]

Change User Password:

   psql -l

Change User Parameters:

In Postgres, connections are accepted, denied, and configured in the pg_hba.conf file. On my installations, this has typically been found in the /var/lib/pgsql/data directory. The file is well documented. Basically, you can choose either a local or remote (host) connect, the database, the IP address and mask (host type only) and the authentication type (and possible argument).

# TYPE   DATABASE      IP_ADDRESS    MASK               AUTH_TYPE AUTH_ARGUMENT

# All remote connections to 'my_database' from 192.168.54.1 require passwords.
host     my_database   192.168.54.1  255.255.255.255    password

# All local connections to 'my_database' require passwords.
local    my_database        192.168.54.1   255.255.255.255    password

Order is important! If a trust AUTH_TYPE occurs before the password AUTH_TYPE, the password is not requested.

Pasword authentication failed to localhost

I ran into the following message when using authentication to localhost:

Notice the DSN: "dbname=mydatabase;host=localhost". Apparently, postgreSQL thinks this is a remote request. There are two choices to make the localhost conection work. One is to remove the ";host=localhost" alltogether. The other alternative to add an entry for host 127.0.0.1 to your pg_hba.conf file. Here is an example:

host       all         127.0.0.1   255.255.255.255      password

This can coexist with a "local / trust" entry so connections from "psql" will connect without a password and connections from perl or php require a password even if they are coming from localhost

local      all                                          trust