Adminer

PostgreSQL

Synology uses UNIX sockets for PostgreSQL (TYPE=local). Here is the default pg_hba.conf from DSM 6.0.2-8451u9 :

root@DiskStation:~# cat /etc/postgresql/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer map=pg_root
local   all             all                                     peer

There is no need to modify the pg_hba.conf file to access PostgreSQL databases. In addition, Unix sockets are faster than tcp/ip sockets and more efficient on small NASes. PostgreSQL uses the client user name (defined in /etc/passwd) to authenticate the user (METHOD=peer).

More information about pg_hba.conf file can be found here.

The postgres user account has all privileges on PostgreSQL. Use the following to connect to the PostgreSQL server :

login as: admin
admin@DiskStation’s password: ************
admin@DiskStation:~$ sudo -i
Password: ************
root@DiskStation:~# su - postgres
postgres@DiskStation:~$ psql
psql (9.3.6)
Type “help” for help.
 

postgres=# \du
                                     List of roles
        Role name          |                   Attributes                   | Member of
----------------------------+------------------------------------------------+-----------
postgres                   | Superuser, Create role, Create DB, Replication | {}


postgres=# \q
postgres@DS411:~$ exit
logout
root@DS411:~#

PostgreSQL and Web Station

As PHP scripts are executed with the user http in Web Station, proper privileges has to be set for this user :

postgres=# CREATE DATABASE "my_db";
CREATE DATABASE
postgres=# CREATE TABLE "my_table" (id SERIAL PRIMARY KEY,my_field VARCHAR(64) NOT NULL DEFAULT '');
CREATE TABLE
postgres=# GRANT CONNECT ON DATABASE "my_db" TO http;
GRANT
postgres=# \connect "my_db"
You are now connected to database "my_db" as user "postgres".
postgres=# GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE "my_table" TO http;
GRANT

Use the following PHP code to connect to your database :

try{
	$pdo = new PDO('pgsql:dbname=my_db');
	// Do your stuff
}catch (\Exception $e){
	var_dump($e);
}