PostgreSQL, repmgr and Barman

Initially, I started work on a Docker container build, but using the docker build provided some challenges with editing configurations to satisfy barman streaming requirement. For this reason, I moved to carrying out a native installation using packages straight from the Debian repo – postgresql-15-repmgr and rsync.

sudo apt install postgresql-15-repmgr rsync

Once installed, followed the repmgr quick start guide to the point where I needed to remove the 127.0.1.1 entry from the hosts file as it was preventing the registration and connection to the server due to no pg_hba.conf entry.

connection to server at "S00600" (127.0.1.1), port 5432 failed: fe_sendauth: no password supplied

The other alternative is to reorder the pg_hba.conf entries so that the repmgr connection appears before the all connection in IPv4 local connections:

# IPv4 local connections:
host    repmgr          repmgr          127.0.0.1/32            trust
host    all             all             127.0.0.1/32            scram-sha-256

PostgreSQL with TLS Client Auth

Configure postgresql.conf to enable ssl and configure a server key and certificate.

Server Certificate

Create a self-signed certificate pair. Put them in ./tls

openssl req -new -x509 -days 3650 -nodes -text -out server.crt -keyout server.key -subj “/CN=server” 

Client Certificates

openssl req -new -x509 -days 3650 -nodes -text -out client.crt -keyout client.key -subj “/CN=client”

postgresql.conf

ssl = on
ssl_key_file = '/tls/server.key'
ssl_cert_file = ‘/tls/server.crt’

We want to use self-signed client certificates that get verified. Put copies of each client certificate (NOT key) into the ca.crt file, eg.

cat client.crt >> tls/ca.crt

Add to postgresql.conf

ssl_ca_file = ‘/tls/ca.crt’

pg_hba.conf

This will require every connection to require a client certificate.

hostssl all all all cert

docker-compose.yml

This will mount the `./tls` folder into the image.

version: '3.7'

services:
  db:
    build: build/postgres
    environment:
      LANG: 'en_GB.UTF-8'
      LANGUAGE: 'en_GB.UTF-8'
      LC_ALL: 'en_GB.UTF-8'
      POSTGRES_DB: "${POSTGRES_DB:-postgres}"
      POSTGRES_PASSWORD: "${POSTGRES_PASSWORD?REQUIRED}"
      POSTGRES_USER: "${POSTGRES_USER:-postgres}"
    volumes:
      - "${CONTAINER_VOLUME?REQUIRED}/${SERIAL?REQUIRED}/postgres:/var/lib/postgresql/data:rw"
      - "${CONTAINER_VOLUME?REQUIRED}/${SERIAL?REQUIRED}/pg_socket:/var/run/postgresql:rw"
      - "${PWD}/tls:/tls:ro"
    restart: always
    ports:
      - "${PORTBASE?REQUIRED}32:5432"

Locales

Using Dockerfile to build with, we can specify the locale to use, in our case en_GB.UTF-8

FROM postgres:9.6
RUN localedef -i en_GB -c -f UTF-8 -A /usr/share/locale/locale.alias en_GB.UTF-8
ENV LANG en_GB.utf8

Connecting with psql

PGSSLKEY=client.key PGSSLCERT=client.crt psql --host 127.0.0.1 --port 5432 -U client -d postgres

or

psql --host 127.0.0.1 --port 5432 -U client -d “sslkey=client.key sslcert=client.crt dbname=postgres”

Evidence

select pg_ssl.pid, pg_ssl.ssl, pg_ssl.version,
           pg_sa.usename, pg_sa.client_addr
           from pg_stat_ssl pg_ssl
           join pg_stat_activity pg_sa
             on pg_ssl.pid = pg_sa.pid;

 pid | ssl | version | usename  | client_addr 
-----+-----+---------+----------+-------------
  63 | t   | TLSv1.2 | postgres | 172.23.0.1
(1 row)

PostgreSQL, SCRAM and TLS

When setting up a server using v10.1 and trying to connect using psql client v14, I got an error:

PGPASSWORD=SecretKey PGSSLMODE=require psql --host 172.16.10.110 --port 54732 -U myuser -d mydb

psql: error: connection to server at "172.16.10.110", port 5432 failed: FATAL:  unexpected SCRAM channel-binding attribute in client-final-message

Fail2ban – PostgreSQL

We don’t often have the need to expose PostgreSQL to a network, let alone the internet. Mostly the instances are enclosed within a Docker container set and don’t need to be accessed by anything outside of that. So when we have a need to expose it we need to protect it as much as possible.

Make the change to enforce security within PostgreSQL, using TLS and certificates, lock down authentication to IP addresses using pg_hba.conf, but we should also monitor and block access at the firewall.

AWS RDS PostgreSQL Passwords

Working on a customer’s AWS database instance, I found they didn’t have all the passwords.

I could get onto administer to AWS RDS database and instance, but didn’t have the postgres user password. I could log on as a user that had CreateDB, but how do I get to change the main postgres user password to something I know?

First thing was to change the master password for the rdsadmin user – easy enough as that’s in the web console. But none of the documented passwords for the postgres user work.

Try logging into the instance from an AWS host using the rdsadmin user and get knocked back.

$ PGPASSWORD=SecretKey psql -h domain.eu-west-2.rds.amazonaws.com -U rdsadmin -d postgres

psql: error: FATAL:  pg_hba.conf rejects connection for host "10.0.0.219", user "rdsadmin", database "postgres", SSL on
FATAL:  pg_hba.conf rejects connection for host "10.0.0.219", user "rdsadmin", database "postgres", SSL off

And you can’t edit pg_hba.conf on AWS.

TLDR;

It’s a very simple issue if you know it. Use the same password you just gave the rdsadmin user!

$ PGPASSWORD=SecretKey psql -h domain.eu-west-2.rds.amazonaws.com -U postgres            

psql (12.X (Ubuntu 12.X-0ubuntu0.XX.XX.XX), server 12.X)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=>

Keycloak Cluster

As resilient as our docker build is I’d like to extend it to a clustered instance of Keycloak. This way, I can load balance a pair of docker containers and maintain an even higher uptime. The model I’ve chosen is the standalone HA method. This gives me two Keylcoak instances and a single back end database.

I wanted to build this across a data centre in two different virtual hosts, connected to the same network. I’m also going to replicate the postgres database between systems using repmgr.

First thing is to prepare the environment and allow firewall connections between the virtuals on TCP ports 5432 (for postgres) and TCP port 7600 (for Keycloak TCPPING). Then I need acess to TCP port 8080 (for the Keycloak web interface) from the load balancer. I’ll be using TLS termination on the load balancer/reverse proxy to handle the certificates.

Asterisk and PostgreSQL

I started out wanting a real-time database connection to our existing LDAP server. This went well, but involved importing a schema into the LDAP cn=config and mapping the data into Asterisk.

It then became apparent that the effort involved in linking Asterisk to LDAP didn’t really produce the key result that I was after. My whole reason for linking Asterisk to LDAP was to share authentication credentials from our users for their SIP devices. After I’d deployed it I discovered that Asterisk would store it’s credentials in different fields and what’s worse is that the password could only be plain-text or an MD5 hash.

If our users must use a separate credential for logging into a SIP device, then using LDAP is no longer of interest to me. We may as well use a database – enter PostgreSQL.

PostgreSQL and Replication

We do a lot of postgres replication in our environment. Just about every docker postgres instance replicates its data over to a remote site for failover, should the worst happen. But we don’t tend to automate the process, maybe we should look at moving away from a manual failover.

SQL Format

We’ve all got SQL that needs tidying up.

https://poorsql.com/ or https://sqlformat.org/ or http://sqlformat.darold.net/

It wont fix your syntax but it will make it look nice.

PostGIS Query Optimisation

I started to do some work on how efficient the spacial SQL queries I was using are. That meant looking at the indexing that is used on the geometry column and understanding the functions I used a bit more.

I made a simple change from using ST_Contains to ST_Within and made a tenfold increase in the performance of my query. Similarly with a change from ST_Distance to ST_DWithin.

Now if only I read the documentation more!

Prior to 1.3, ST_Expand was commonly used in conjunction with && and ST_Distance to achieve the same effect and in pre-1.3.4 this function was basically short-hand for that construct. From 1.3.4, ST_DWithin uses a more short-circuit distance function which should make it more efficient than prior versions for larger buffer regions.

https://postgis.net/docs/ST_DWithin.html

PostGIS and geoJson

On with this current theme of spacial systems and mapping, one of the interesting challenges I faced was querying spacial data.

My particular problem was trying not to publish masses of polygons up onto an Esri map layer and make the data layer too cumbersome for our intended visitors purpose.

We have a lot of plots of land that we care for and needed to check that when a web site visitor clicks on the Esri map that we check to see if the location they have clicked actually belongs to a plot we manage.

DBeaver – SQL GUI

I’ve used a few SQL GUI’s over the years, SQuirreL, DBVisualizer, HeidiSQL, MySQL Workbench, but the one that stands out recently is DBeaver.

It’s got a community and enterprise edition. The community does everything I need and connects to all the SQL servers we use, Microsoft SQL, MySQL, Postgres/PostGIS.

Being Java based it’s cross platform, so you can use it in Windows too.