Difference between revisions of "PostgreSQL"
| (4 intermediate revisions by the same user not shown) | |||
| Line 6: | Line 6: | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| + | # Postgresql binaries | ||
apt-get install postgresql postgresql-doc | apt-get install postgresql postgresql-doc | ||
| + | |||
| + | # Additional features | ||
| + | apt-get install postgresql-contrib libdbd-pg-perl | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 16: | Line 20: | ||
| − | Edit the server configuration file | + | ==Access rights== |
| + | |||
| + | Edit the server configuration file: | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| Line 56: | Line 62: | ||
### 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 75: | ||
==Port number== | ==Port number== | ||
| − | Edit the server configuration file | + | Edit the server configuration file: |
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| Line 69: | Line 82: | ||
| − | Search and adjust port number | + | Search and adjust port number: |
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| Line 75: | Line 88: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| + | |||
| + | Restart server to apply changes | ||
| + | |||
| + | <syntaxhighlight lang="bash"> | ||
| + | service postgresql restart | ||
| + | </syntaxhighlight> | ||
| Line 86: | Line 105: | ||
| + | Create a new user | ||
| − | |||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| − | + | createuser -P <username> | |
| + | </syntaxhighlight> | ||
| + | |||
| + | |||
| + | Create a database for that user | ||
| + | |||
| + | <syntaxhighlight lang="bash"> | ||
| + | createdb -O <username> -E UTF8 <newDB> | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | |||
| + | Leave shell | ||
| + | |||
| + | <syntaxhighlight lang="bash"> | ||
| + | exit | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | + | =postgresql client= | |
| + | |||
| + | You should use ''pgadmin''. | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| − | + | apt-get install pgadmin3 | |
</syntaxhighlight> | </syntaxhighlight> | ||
| − | + | ||
| + | '''Technical note''' | ||
| + | |||
| + | If you use ''<code>PgAdmin</code>'' 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 | ||
| + | |||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| − | + | sudo -i -u postgres | |
| + | psql | ||
| + | ALTER USER mydb_user WITH SUPERUSER; | ||
| + | \q | ||
| + | exit | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 113: | Line 161: | ||
* Very good explanation of access restrictions and configuration: http://www.depesz.com/2007/10/04/ident/ | * 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 | * 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 | ||
Latest revision as of 20:27, 24 April 2015
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
