QNAP/Linux Tool - PostgreSQL

QNAP has packaged the widely-used DBMS, PostgreSQL, from Linux open source. The supported version is newer than that packaged in OptIPKG. As for why choosing PostgreSQL, please see Ubuntu Programming Lesson 01 - Install PostgreSQL (Why PostgreSQL).

PostgreSQL is available on QNAP App Center

Version 9.2.1 is newer (My Ubuntu installed version 9.3).

OptIPKG's postgresql ipkg is older

After install PostgreSQL, just click Launch to connect and login with the administrator account "postgres".

QNAP also includes pgAdmin (with PHP Web UI) into the package.

As I wrote previously: Ubuntu Programming Lesson 02 - pgAdmin connect to PostgreSQL, following steps must be done.

1. Change password of "postgres" from Control Panel of QTS.

What is the password of "postgress"?

Control Panel / Privilege / Users: postgres account was added due to install PostgreSQL

Successfully connected!

2. Change settings of PostgreSQL through SSH.
Edit the configuration file: postgresDB/postgresql.conf
Remove the remark# from this config line ( #listen_addresses = 'localhost' ) so that this QNAP web server (localhost) can connect to PostgreSQL. Or change ( #listen_addresses = 'localhost' ) to ( listen_addresses = '*' ), if you want public hosts to be able to connect with this postgresql server. (But it's danger!)

[~] # cd /share/HDA_DATA/.qpkg/PostgreSQL/ [/share/HDA_DATA/.qpkg/PostgreSQL] # ls -ladrwxr-xr-x 9 admin administ 4096 Mar 26 10:08 ./drwxrwxrwx 17 admin administ 4096 Mar 26 10:08 ../-rw-r--r-- 1 admin administ 101439 Mar 26 10:08 .list-rwxr-xr-x 1 admin administ 1024 Mar 26 10:08 .uninstall.sh*-rwxr-xr-x 1 admin administ 783 Mar 27 2013 PostgreSQL.sh*drwxr-xr-x 2 admin administ 4096 Mar 27 2013 bin/drwxr-xr-x 4 admin administ 4096 Mar 27 2013 include/drwxr-xr-x 3 admin administ 4096 Mar 27 2013 lib/drwx------ 14 postgres administ 4096 Mar 26 10:08 postgresDB/drwxr-xr-x 3 admin administ 4096 Mar 27 2013 share/drwxrwxrwx 2 admin administ 4096 Mar 26 10:08 tmp/drwxr-xr-x 14 admin administ 4096 Mar 27 2013 web/[/share/HDA_DATA/.qpkg/PostgreSQL] # cd postgresDB/[/share/HDA_DATA/.qpkg/PostgreSQL/postgresDB] # lsPG_VERSION* global/ pg_hba.conf* pg_multixact/ pg_serial/ pg_stat_tmp/ pg_tblspc/ pg_xlog/ postmaster.optsbase/ pg_clog/ pg_ident.conf* pg_notify/ pg_snapshots/ pg_subtrans/ pg_twophase/ postgresql.conf* postmaster.pid[/share/HDA_DATA/.qpkg/PostgreSQL/postgresDB] # nano postgresql.conf...listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) port = 5432 # (change requires restart) max_connections = 100 # (change requires restart)

NOTE: I've changed my RAID0 SATA HDs into one SSD HD to make QNAP quite.

Find pg_hba.conf file in the PostgreSQL installation directory.
[~] # cd /share/HDA_DATA/.qpkg/PostgreSQL/postgresDB/ [/share/HDA_DATA/.qpkg/PostgreSQL/postgresDB] # ls PG_VERSION* pg_clog/ pg_multixact/ pg_snapshots/ pg_tblspc/ postgresql.conf* base/ pg_hba.conf* pg_notify/ pg_stat_tmp/ pg_twophase/ postmaster.opts global/ pg_ident.conf* pg_serial/ pg_subtrans/ pg_xlog/ postmaster.pid










Edit the pg_hba.conf (Host-Based Authencation) to allow public hosts to be able to connect with this server. Check following examples to set your pgsql client-server environment.
[/share/HDA_DATA/.qpkg/PostgreSQL/postgresDB] # nano pg_hba.conf

...

# IPv4 local connections:
host    all             all             127.0.0.1/32      trust    # only allow localhost to connect
host    all             all             localhost          trust    # only allow localhost to connect
host    all             all             0.0.0.0/0            md5   # allow all public host to connect
host    all             all             192.168.0.0/24  md5   # allow IPs 192.168.0.* to connect...

Notably, when you login Linux (Ubuntu or QNAP) with admin (the root), its not the root of PostgreSQL, but "psql -U postgres" command can login PostgreSQL without typing password. Check the pg_hba.conf, the following line means that PostgreSQL trust

# "local" is for Unix domain socket connections only
local   all             all                                     trust

However, PostgreSQL connections from other hosts or local web services (i.e. running PHP programs in the same machine with PostgreSQL) should be verified with dbuser accounts rather than Unix accounts and "postgres". Running PHP web services with "postgres" is dangerous, each service should be isolated with different dbusers for different databases.

Consequently, I suggest that you should set all Authentication method as md5 (encoded vs. password, not encoded) . If you set trust, some phpPgAdmin implementation may treat the setting as "trust connection" so that anonymous can login through "right" account name with "any" password string. In my QNAP, I set as follows to let postgresql accounts be accessible from localhost and hosts in the same network segments.
host    all             all             localhost           md5    # only allow localhost to connect
host    all             all             192.168.0.0/24  md5   # allow IPs 192.168.0.* to connect