PostgreSQL and Pointsharp IdP

This guide walks through the full setup of PostgreSQL and two Pointsharp IdP instances for a production-ready high-availability environment.

Preparations

Prepare your hosts.

Ensure all are on the same network and resolvable via /etc/hosts or DNS.

Install postgreSQL:

$ sudo apt update && sudo apt install -y postgresql

Create a database and user:

psql
CREATE DATABASE psidpdb;
CREATE USER psidpuser WITH PASSWORD 'StrongPassword123!';
GRANT ALL PRIVILEGES ON DATABASE psidpdb TO psidpuser;
\q

Edit pg_hba.conf to allow remote access:

$ sudo nano /etc/postgresql/*/main/pg_hba.conf

Add:

$ host psidpdb psidpuser 192.168.1.0/24 md5

Allow external connections:

$ sudo nano /etc/postgresql/*/main/postgresql.conf

Find and update:
```
$ listen_addresses = '*'
```
Restart PostgreSQL:
```
$ sudo systemctl restart postgresql
```
Test from remote:
```
$ psql -h 10.50.0.41 -U psidpuser -d psidpdb
```
Allow port 5432 through firewall (if needed): ufw allow 5432

psidp-01: 10.50.0.31
psidp-02: 10.50.0.32
1. Edit keycloak.conf:
  hostname=HOSTNAME hostname-strict=true db=postgres # PostgreSQL connection db-url=jdbc:postgresql://10.50.0.41:5432/psidpdb db-username=psidpuser db-password=StrongPassword123!
2. Replace HOSTNAME with the actual hostname of the instances: psidp-01 and psidp-02
3. Replace StrongPassword123 with your own secure password.
4. Ensure both instances are using the same TLS certs and realm configuration.
5. Start the IdP service:
  $ sudo systemctl start pointsharpidp $ sudo systemctl enable pointsharpidp

Check logs:
```
$ journalctl -u pointsharpidp -f
```
Ensure both nodes successfully connect to the PostgreSQL server and start without Liquibase schema errors.
Done!

You now have a PostgreSQL database server with two Pointsharp IdP instances, using it in a shared high availability compatible setup.