Difference between revisions of "PostgreSQL"
| Line 3: | Line 3: | ||
| − | + | =Installation= | |
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| Line 10: | Line 10: | ||
| − | + | ||
| + | =Server configuration= | ||
| + | |||
| + | By default only the ''<code>postgres</code>'' user can access database and configure it. | ||
| + | |||
Edit the server configuration file | Edit the server configuration file | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| − | vim /etc/postgresql/ | + | vim /etc/postgresql/9.3/main/pg_hba.conf |
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 25: | Line 29: | ||
# USER access | # USER access | ||
######## | ######## | ||
| − | # Allow postgresql super | + | # Allow postgresql super administration connection !! only from the Linux user 'postgres' !! |
| − | local all postgres | + | local all postgres peer |
# Allow other users connection | # Allow other users connection | ||
local all all trust | local all all trust | ||
| Line 53: | Line 57: | ||
host all all 0.0.0.0/0 reject | host all all 0.0.0.0/0 reject | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| + | |||
| + | |||
| + | |||
| + | ==Port number== | ||
| + | |||
| + | Edit the server configuration file | ||
| + | |||
| + | <syntaxhighlight lang="bash"> | ||
| + | vim /etc/postgresql/9.3/main/postgresql.conf | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | |||
| + | Search and adjust port number | ||
| + | |||
| + | <syntaxhighlight lang="bash"> | ||
| + | port = 5432 | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | |||
| + | |||
| + | =Create user + DB= | ||
| + | |||
| + | Log-in as ''postgres'' | ||
| + | |||
| + | <syntaxhighlight lang="bash"> | ||
| + | sudo -i -u postgres | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | |||
| + | |||
| + | Connection to db server | ||
| + | <syntaxhighlight lang="bash"> | ||
| + | psql | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | |||
| + | |||
| + | Create an user | ||
| + | |||
| + | <syntaxhighlight lang="bash"> | ||
| + | createuser -P <username> | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | |||
| + | Create database for that user | ||
| + | |||
| + | <syntaxhighlight lang="bash"> | ||
| + | createdb -O <username> -E UTF8 <newDB> | ||
| + | </syntaxhighlight> | ||
| + | |||
Revision as of 19:45, 24 April 2015
Installation
apt-get install postgresql postgresql-doc
Server configuration
By default only the postgres user can access database and configure it.
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
Port number
Edit the server configuration file
vim /etc/postgresql/9.3/main/postgresql.conf
Search and adjust port number
port = 5432
Create user + DB
Log-in as postgres
sudo -i -u postgres
Connection to db server
psql
Create an user
createuser -P <username>
Create database for that user
createdb -O <username> -E UTF8 <newDB>
References
- Very good explanation of access restrictions and configuration: http://www.depesz.com/2007/10/04/ident/
- Setup example: http://technobytz.com/install-postgresql-9-3-ubuntu.html
