Difference between revisions of "PostgreSQL"

(Created page with "Category:Linux Category:Development Required packages <syntaxhighlight lang="bash"> apt-get install postgresql postgresql-doc </syntaxhighlight> Configuration Ed...")
 
 
(8 intermediate revisions by the same user not shown)
Line 3: Line 3:
  
  
−
Required packages
+
=Installation=
  
 
<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>
  
  
−
Configuration
 
  
−
Edit the server configuration file
+
=Server configuration=
 +
 
 +
By default only the ''<code>postgres</code>'' user can access database and configure it.
 +
 
 +
 
 +
==Access rights==
 +
 
 +
Edit the server configuration file:
  
 
<syntaxhighlight lang="bash">
 
<syntaxhighlight lang="bash">
−
vim /etc/postgresql/xxx/main/
+
vim /etc/postgresql/9.3/main/pg_hba.conf
 
</syntaxhighlight>
 
</syntaxhighlight>
  
Line 25: Line 35:
 
# USER access
 
# USER access
 
########
 
########
−
# Allow postgresql super administrator login, using password
+
# Allow postgresql super administration connection !! only from the Linux user 'postgres' !!
−
local  all        pgdba                            md5
+
local  all        postgres                          peer
 
# Allow other users connection
 
# Allow other users connection
 
local  all        all                              trust
 
local  all        all                              trust
Line 42: Line 52:
  
 
### Forbid remote connection for non-trusted locations
 
### Forbid remote connection for non-trusted locations
−
host    all        pgdba      0.0.0.0/0            reject
+
host    all        postgres    0.0.0.0/0            reject
  
 
### Password required  
 
### Password required  
Line 48: Line 58:
 
host    all        all        172.16.100.0/24        md5
 
host    all        all        172.16.100.0/24        md5
 
# Specific host(s)
 
# Specific host(s)
−
host    all        all        5.39.81.23/32          trust
+
host    all        all        5.39.81.23/32          md5
  
 
### 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>
 
</syntaxhighlight>
 +
 +
 +
Restart server to apply changes
 +
 +
<syntaxhighlight lang="bash">
 +
service postgresql restart
 +
</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>
 +
 +
 +
Restart server to apply changes
 +
 +
<syntaxhighlight lang="bash">
 +
service postgresql restart
 +
</syntaxhighlight>
 +
 +
 +
=Create user + DB=
 +
 +
Log-in as ''postgres''
 +
 +
<syntaxhighlight lang="bash">
 +
sudo -i -u postgres
 +
</syntaxhighlight>
 +
 +
 +
Create a new user
 +
 +
<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>
 +
 +
 +
 +
=postgresql client=
 +
 +
You should use ''pgadmin''.
 +
 +
<syntaxhighlight lang="bash">
 +
apt-get install pgadmin3
 +
</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">
 +
sudo -i -u postgres
 +
psql
 +
ALTER USER mydb_user WITH SUPERUSER;
 +
\q
 +
exit
 +
</syntaxhighlight>
 +
  
  
Line 59: 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 19:27, 24 April 2015


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


PgAdmin administration fix: https://stackoverflow.com/questions/22135792/permission-denied-to-create-extension-uuid-ossp

Retrieved from "http://www.daxiongmao.eu/wiki/index.php?title=PostgreSQL&oldid=2310"