PostgreSQL

Installation

# Postgresql binaries
apt-get install postgresql postgresql-doc

# Additional features
apt-get install postgresql-contrib libdbd-pg-perl

Server configuration

By default only the postgres user can access database and configure it.

Access rights

Edit the server configuration file:

vim /etc/postgresql/9.3/main/pg_hba.conf

Adjust the access rights, at the end:

######## 
# USER access
########
# Allow postgresql super administration connection !! only from the Linux user 'postgres' !!
local   all         postgres                          peer
# Allow other users connection
local   all         all                               trust


########
# IP@ filtering 
#######
### No password required
# Enable localhost
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust
# Enable specific host(s)
host    all         all         172.16.100.62/32      trust

### Forbid remote connection for non-trusted locations
host    all         postgres    0.0.0.0/0             reject

### Password required 
# Local network 
host    all         all         172.16.100.0/24        md5
# Specific host(s)
host    all         all         5.39.81.23/32          md5

### Reject other incoming connections
host    all         all         0.0.0.0/0             reject

Restart server to apply changes

service postgresql restart

Port number

Edit the server configuration file:

vim /etc/postgresql/9.3/main/postgresql.conf

Search and adjust port number:

port = 5432

Restart server to apply changes

service postgresql restart

Create user + DB

Log-in as postgres

sudo -i -u postgres

Create a new user

createuser -P <username>

Create a database for that user

createdb -O <username> -E UTF8 <newDB>

Leave shell

exit

postgresql client

You should use pgadmin.

apt-get install pgadmin3

Technical note

If you use PgAdmin you might have a warning saying that you need to be root to FIX the DB settings.

You need to:

• Open a shell as postgresql
• Connect to the DB
• Alter the rights of the target DB user

sudo -i -u postgres
psql
ALTER USER mydb_user WITH SUPERUSER;
\q
exit

References

• Very good explanation of access restrictions and configuration: http://www.depesz.com/2007/10/04/ident/
• Ubuntu FR forum: http://doc.ubuntu-fr.org/postgresql
• Setup example: http://technobytz.com/install-postgresql-9-3-ubuntu.html

PgAdmin administration fix: https://stackoverflow.com/questions/22135792/permission-denied-to-create-extension-uuid-ossp