Difference between revisions of "PostgreSQL"
| Line 16: | Line 16: | ||
| − | Edit the server configuration file | + | ==Access rights== |
| + | |||
| + | Edit the server configuration file: | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| Line 56: | Line 58: | ||
### Reject other incoming connections | ### Reject other incoming connections | ||
host all all 0.0.0.0/0 reject | host all all 0.0.0.0/0 reject | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | |||
| + | Restart server to apply changes | ||
| + | |||
| + | <syntaxhighlight lang="bash"> | ||
| + | service postgresql restart | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 62: | Line 71: | ||
==Port number== | ==Port number== | ||
| − | Edit the server configuration file | + | Edit the server configuration file: |
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| Line 69: | Line 78: | ||
| − | Search and adjust port number | + | Search and adjust port number: |
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| Line 75: | Line 84: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| + | |||
| + | Restart server to apply changes | ||
| + | |||
| + | <syntaxhighlight lang="bash"> | ||
| + | service postgresql restart | ||
| + | </syntaxhighlight> | ||
| Line 86: | Line 101: | ||
| + | Connection to db server | ||
| − | |||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
psql | psql | ||
| Line 93: | Line 108: | ||
| − | + | Create a new user | |
| − | Create | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| Line 101: | Line 115: | ||
| − | Create database for that user | + | Create a database for that user |
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| Line 107: | Line 121: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| + | |||
| + | Leave DB console and terminal shell | ||
| + | |||
| + | <syntaxhighlight lang="bash"> | ||
| + | quit | ||
| + | exit | ||
| + | </syntaxhighlight> | ||
Revision as of 19:51, 24 April 2015
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
Connection to db server
psql
Create a new user
createuser -P <username>
Create a database for that user
createdb -O <username> -E UTF8 <newDB>
Leave DB console and terminal shell
quit
exit
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
