PostgreSQL
Contents
Installation
apt-get install postgresql postgresql-doc
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
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
