Difference between revisions of "PostgreSQL"

 
Line 124: Line 124:
 
exit
 
exit
 
</syntaxhighlight>
 
</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 132: Line 163:
 
* Ubuntu FR forum: http://doc.ubuntu-fr.org/postgresql
 
* 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