Articles

Debian/Ubuntu users please see this tutorial. To use Directadmin PostgreSQL plugin you will first need to ensure PostgreSQL server is installed as well as PostgreSQL support in PHP exists. See how you can install PostgreSQL 12 and build related PHP module. EPEL is another prerequisite so start with:

yum -y install epel-release yum-utils

Ensure you have latest pgdg repo to avoid broken dependencies:

yum remove pgdg-redhat-repo
yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Default used to be all repos enabled including pgdg-common so let’s disable uneeded ones:

yum-config-manager --disable pgdg11 pgdg10 pgdg96 pgdg95
yum -y install postgresql12-server postgresql12-contrib
#dnf module enable postgresql:12 # centos8 
#dnf install postgresql-server postgresql12-contrib libpq-devel # centos8 

You may also need to install database extensions if any of the databases to be restored used them. For example to install postgis you would run yum install postgis30_12. Let’s now find where psql resides, init DB server and start it.

readlink -f `which psql` 
/usr/pgsql-12/bin/postgresql-12-setup initdb
#postgresql-setup initdb # centos8
systemctl enable --now postgresql-12
#systemctl enable --now postgresql # centos8
su - postgres -c 'psql -c "SELECT version()"'
su - postgres -c 'psql -tA -c "SHOW data_directory;"'

Ensure local all postgres peer (and optionally local all root peer) line exists in pg_hba.conf for access to postgres by the plugin and (optionally) management user root.

Replace ident with md5 in pg_hba.conf

sed -i 's/ident$/md5/' /var/lib/pgsql/12/data/pg_hba.conf
systemctl reload postgresql-12
#sed -i 's/ident$/md5/' /var/lib/pgsql/data/pg_hba.conf # centos8
#systemctl reload postgresql # centos8

You may also use su - postgres -c '/usr/pgsql-12/bin/pg_ctl reload' to reload DB server.

Enabling PostgreSQL support in default PHP

This is not needed for the plugin itself but for users who may need to access their PostgreSQL databases from PHP. Below yum command section is not needed for CentOS 8 as we already have libpq-devel installed. Pure Red Hat users need to download and install centos-release-scl-rh RPM manually. Cloudlinux users may use cloudlinux-scl-release to get libpqxx-devel.

[ -f `/usr/pgsql-12/bin/pg_config --includedir`/libpq-fe.h ] && echo "devel installed" || echo "devel missing"
# scl-rh for llvm-toolset-7-clang 4.0.1
yum -y install centos-release-scl-rh 
yum-config-manager --disable centos-sclo-rh
yum -y install postgresql12-devel --enablerepo=centos-sclo-rh

Now check default PHP version and copy configure.phpXX of the version to custom directory. If you already have it there please add this line --with-pgsql=/usr/pgsql-12 --with-pdo-pgsql=/usr/pgsql-12 to the file. In our case default PHP uses configure.php73 and the customized file is not existing so we copy it.

cd /usr/local/directadmin/custombuild
./build used_configs | grep 'PHP (default) configuration' | awk '{print $NF}'
/usr/local/directadmin/custombuild/configure/php/configure.php73
mkdir -p /usr/local/directadmin/custombuild/custom/php
\cp -fp /usr/local/directadmin/custombuild/configure/php/configure.php73 /usr/local/directadmin/custombuild/custom/php

Add your --with-pgsql= --with-pdo-pgsql= line to custom/php/configure.php73. In this case ‘include’ directory is in /usr/pgsql-12 so we use this value. To know where is include run dirname $(/usr/pgsql-12/bin/pg_config --includedir).

perl -0777 -i.bak -pe 's|^(\./configure \\)$(?!\s*--with-pgsql)|$1\n\t--with-pgsql=/usr/pgsql-12 --with-pdo-pgsql=/usr/pgsql-12 \\|ms' /usr/local/directadmin/custombuild/custom/php/configure.php73
cd /usr/local/directadmin/custombuild
./build php d

For CentOS 8 the line in configure.php73 should be --with-pgsql=/usr --with-pdo-pgsql=/usr.

Similarly you can add PostgreSQL support to other PHP versions.

Here are all the commands for Postgres 14 installtion in a text file that you can run with bash setup_postgresql_14_almalinux_8_php_8.1.txt.

Facilitate your work with PostgreSQL

As a server admin you may want to have faster access to PostgreSQL client. While we need postgres user peer authentication to work for the DirectAdmin PostgreSQL plugin you may prefer to manage PostgreSQL as root.

su - postgres -c 'createuser -d -s -r root; createdb -O root root'

Now we can simply use psql from root account.

You can then proceed to plugin installation. Note the plugin requires your DirectAdmin server to be acessible over https.

AlmaLimux 8.5 + PosgtreSQL 14

In AlmaLimux 8.5 + PosgtreSQL 14 you can get:

# yum -y install postgresql14-server postgresql14-contrib
Last metadata expiration check: 0:20:39 ago on Mon 17 Jan 2022 12:34:19 PM UTC.
All matches were filtered out by modular filtering for argument: postgresql14-server
All matches were filtered out by modular filtering for argument: postgresql14-contrib
Error: Unable to find a match: postgresql14-server postgresql14-contrib

Then run

# dnf -y module disable postgresql
# dnf -y install libpq-devel # this module may be not existing or already disabled

phpPgAdmin 1-click SSO integration

In this approach we chose to reserve socket login for phpPgAdmin and PAM authentication. Users that used to use command line psql client should be advised to specify -h 127.0.0.1 in their psql command line. You may easily modify the recipe to use IPv6 or an other e.g. LAN IP for PAM authentication (defined in pg_hba.conf) if you want to preserve socket-based access for other goals.

1. Unzip phpPgAdmin files into /var/www/html/phpPgAdmin

cd /var/www/html
wget https://github.com/phppgadmin/phppgadmin/releases/download/REL_7-13-0/phpPgAdmin-7.13.0.tar.gz
tar xzf phpPgAdmin-7.13.0.tar.gz
mv phpPgAdmin-7.13.0 phpPgAdmin && rm -f phpPgAdmin-7.13.0.tar.gz
chown -R webapps: phpPgAdmin

2. Create additional directories, set access rights and ownership

mkdir -p /var/www/html/phpPgAdmin/direct_login/tokens
chown -R webapps:postgres /var/www/html/phpPgAdmin/direct_login
chmod 771 /var/www/html/phpPgAdmin/direct_login
chmod 770 /var/www/html/phpPgAdmin/direct_login/tokens

3. Copy sso.pl helper script into /var/www/html/phpPgAdmin/direct_login and install its prerequisites

cp /usr/local/directadmin/plugins/postgres/bin/sso.pl /var/www/html/phpPgAdmin/direct_login
chown root:postgres /var/www/html/phpPgAdmin/direct_login/sso.pl
chmod 750 /var/www/html/phpPgAdmin/direct_login/sso.pl
yum -y install perl-File-Touch perl-IO-All

4. Edit 2 lines in /var/www/html/phpPgAdmin/conf/config.inc.php so that they read:

$conf['servers'][0]['desc'] = 'PostgreSQL SSO';
$conf['servers'][0]['host'] = '/var/run/postgresql';
$conf['owned_only'] = false

You can also make the edit with this single command:

sed -i -e "s|\(\$conf\['servers'\]\[0\]\['host'\] = \)''|\1'/var/run/postgresql'|" -e "s|\(\$conf\['servers'\]\[0\]\['desc'\] = 'PostgreSQL\)'|\1 SSO'|"  \
-e "s|\(\$conf\['owned_only'\] =\) false|\1 true|" /var/www/html/phpPgAdmin/conf/config.inc.php

This will instruct phpPgAdmin to use socket login method and this method is now serviced by PAM (as set in php_pga.conf).

If your users used to use psql command line client (using socket based auth by default), advise them to add -h 127.0.0.1 to the command line as socket connection will be used by phpPgAdmin one click login since now on.

5. Create /etc/pam.d/postgresql_sso with the following command

cat > /etc/pam.d/postgresql_sso<<EOF
auth sufficient pam_exec.so expose_authtok stdout /usr/bin/perl /var/www/html/phpPgAdmin/direct_login/sso.pl
account required pam_permit.so
EOF

Note the line responsible for this one click login in pg_hba.conf is local all all pam pamservice=postgresql_sso. Lines order in pg_hba.conf is important. Changing order can block some clients from logging in.

Example set of pg_hba.conf lines (all the original permission lines have been commented out):

# postgres and root can use socket
local all postgres peer
local all root peer
# other users connecting via socket will be handled by PAM (for phpPgAdmin 1-click login)
local all all pam pamservice=postgresql_sso
# all users can also connect using localhost IP
host all all 127.0.0.1 255.255.255.255 md5
host all all ::1/128 md5

The plugin will add permissions to access scpecific databases by specific users from a remote IP/subnet to the bottom of php_pga.conf. Now, Postgres users created by you as well as the default user (that is the same as DirectAdmin account username) can open phpPgAdmin with a click from the plugin. In case of some imported databases (e.g. from cPanel) that the default user does not have grants to you may want to click Synchronize Grants buton on the List DB Users tab of the plugin.

Notes: If using PHP8 as default PHP please apply the patch metnioned in https://github.com/phppgadmin/phppgadmin/pull/118. For your convenience it is also here.

gunzip phpPgAdmin-7.13.0-php8.patch.gz
patch -d /var/www/html/phpPgAdmin -p1 < phpPgAdmin-7.13.0-php8.patch
rm -f phpPgAdmin-7.13.0-php8.patch
#wget https://github.com/ADOdb/ADOdb/releases/download/v5.20.19/adodb-5.20.19.tar.gz
#tar -C /var/www/html/phpPgAdmin/libraries/adodb/drivers -xvzf adodb-5.20.19.tar.gz --strip-components=2 adodb5/drivers/adodb-postgres64.inc.php

Read more on adodb update in https://github.com/phppgadmin/phppgadmin/issues/119.

Here are all the commands for phpPgAdmin in a text file that you can run with bash setup_phppgadmin_almalinux_8_php_8.1.txt.