phpPgAdmin to access SQL on SMG (non-appliance)

SMG will change and has changed its outfit. SMG will move from appliance based version to a rpm based version. I do not discuss why it happens but I want to bring back a utility which I used many times to access the sql database in the background.

SMG uses a PostgreSQL database to store configuration and daily data in the background. The appliance offered phpPgAdmin to access and maintain the database. There you are able to search for values, to check settings, to create automatic reports or to shrink databases in case of troubles.

To bring back phpPgAdmin to my sles server my approach will be docker. I use docker because it is easy to roll out and bring applications to life. And it is easy to throw it away if there is no need.

Intro

My description here is based on Sles15SP5. However I think it is easy to adapt it for OpenSUSE too. Instead of a long docker command I use docker-compose and a yml file. It is easier to explain and to change.

How to get docker

In my yast configuration environment I add extension “Containers Module”.

The next steps are easy. I install docker, enable docker service and start it:

zypper install docker

systemctl enable docker.service

systemctl start  docker.service

Because preferring docker-compose I will long for it.

zypper install docker-compose

Find and get phpPgAdmin container

Let’s use the “docker advantage”!

Someone was interested in phpPgAdmin, did some researches, played around with settings and created a container which contains all ingredients to get a running phpPgAdmin application. There are several ideas and ways for this software package.
I have selected dockage/phppgadmin (https://github.com/dockage/phppgadmin). You can download this prepared container with ‘docker pull dockage/phppgadmin:latest’.

However, I mentioned that I go for docker-compose. At https://github.com/dockage/phppgadmin you will find a helpful docker-compose.yml file which can be downloaded and copied to your sles server. I have created an own directory for this.

If you want to test it, change into the directory where this yml file is located and run “docker compose up’.  If your container is starting then stop it with ‘Ctrl C’ or ‘docker compose down’.

Make Container phpPgAdmin run

This docker-compose.yml file is a first approach and I have changed some settings. Let’s start with the first lines.

version: '3.4'

services:

  phpPgAdmin:

    restart: always

    image: dockage/phpPgAdmin:latest

    container_name: phpPgAdmin

    ports:

    - "8180:80"

    - "8443:443"

    environment:

    - PHP_PG_ADMIN_SERVER_DESC=PostgreSQL

    - PHP_PG_ADMIN_SERVER_HOST=1.2.3.4

    - PHP_PG_ADMIN_SERVER_PORT=5432

    - PHP_PG_ADMIN_SERVER_SSL_MODE=allow

Adjust yellow marked values for your purposes.

You see that I use port 8180 or 8443 to access phpPgAdmin. Adjust these values to your environment but take care that these ports are not occupied by other services.

The original docker-compose.yml offers a lot more values/parameters. Use it if you need! Nevertheless, I added some more configuration statements in my case.

    volumes:

      - /root/Connection.php:/var/www/classes/database/Connection.php

      - /root/Postgres.php:/var/www/classes/database/Postgres.php

    networks:

        php-stack:

            ipv4_address: 172.18.0.18

networks:

  php-stack:

    ipam:

        config:

          - subnet: 172.18.0.0/24

First of all I added some network settings. I have to do this because postgresql does not allow general access from all ip addresses. If I use a defined ip address for my docker environment then it is easier to create exceptions in postgresql.

In my preparation for this paper I have found out that my new phpPgAdmin has some issues to access postgresql. My SMG appliance uses two php files which have been adjusted to make phpPgAdmin work proper; one has been changed by SMG development, one by me. Therefore I have to push these two php files into the container. I use the ‘volumes’ part for this. I push Connection.php and Postgres.php into container phppgadmin.

Both files are stored in directory /root. I will attach both files to this document.

PostgreSQL Configuration

My work is almost done. Now I have to change some postgresql settings to make access possible.

These files are stored in /var/lib/pgsql/data/

I start with pg_hba.conf. You will find some lines similar to this:

The yellow marked parts are important. I allow general access to postgresql: 0.0.0.0/0 or I can limit it to ip range 172.18.0.0. Do you remember that I used this range for my container in docker-compose.yml?

The second file I had to change is postgres.conf. Postgresql will listen on which ip address – I keep it open.

Now I come to my last change. But maybe this happened only in my case and it does not happen in your environment. However, I want to be sure you take care of this. Postgresql encrypts passwords in a special way. md5 or scram-sha-256, scram-sha-256 is the new default. But we need md5. Therefore, add this statement to postgres.conf.

Maybe you have to change your “postgres” password to get it encrypted in the right way.

alter user postgres with password 'postgres';

Done! Please restart you postgresql!

Start your PhpPgAdmin!

Change to your directory where docker-compose.yml is stored. Try to start the new container.

docker compose up

Hopefully your container will start in interactive mode. If you want to run it in the background then use:

docker compose up –detach

Access phpPgAdmin with server-ip-address:8180 and you should end up here:

Summary

Because of docker you can bring back SQL access in an easy way. Although it seems to be a long paper only a few settings have to be adjusted from case to case. Go for it!

My approach can be used for other postgreSQL environments too.

php.files.zip