Postgres

Postgres

install server

sudo apt-get install postgresql postgresql-contrib

install client

sudo apt-get install postgresql-client libpqxx-dev pgadmin3

configure server

As user postgres, run psql, connecting to database postgres

sudo -u postgres psql postgres

In the psql shell, set the postgres user’s password, and then press ctrl-d to exit the psql shell

\password postgres

create a new user, with an associated database

as user postgres, run createuser to create a new user dev

sudo -u postgres createuser -D -A -P dev

the options are:

• -D: no database creation rights
• -A: no add user rights
• -P: ask for password

as user postgres, run createdb to create a new database devbd owned by user dev

sudo -u postgres createdb -O dev devdb

set the new user’s password

sudo -u postgres psql

\password dev
\password prod

install server instrumentation

as user postgres, create the adminpack extension

sudo -u postgres psql
CREATE EXTENSION adminpack;

change server authentication method

default authentication is peer. peer authentication obtains the client’s operating system user name from the kernel and uses it as the allowed database user name. This only works for local connections.

change to md5, which is password-based authentication, with the password sent over the connection as a md5 hash.

sudo vi /etc/postgresql/9.3/main/pg_hba.conf

change the line

# Database administrative login by Unix domain socket
local   all             postgres                                peer

to

# Database administrative login by Unix domain socket
local   all             postgres                                md5    

get postgres to reload the config

sudo /etc/init.d/postgresql reload