PostgreSQL
Contents
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
