Configuration: Postgre SQL set up

This document guides IT administrators through setting up a dedicated PostgreSQL database instance for use as a database repository for the AVIX Server.

Overview & Prerequisites

Before configuring AVIX Server, the database engine must be installed, configured for network connectivity, and populated with the target database schema/owner. This table demonstrate configuration properties and values from the XML configuration file  /repositories.xml at the root of the AVIX Server installation:

1. Engine Installation

Windows

1. Download the official PostgreSQL Interactive Installer from EDB (see https://www.postgresql.org/download/windows/  and https://www.enterprisedb.com/downloads/postgres-postgresql-downloads )

2. Run the executable and accept default paths

   1. 

3. Select PostgreSQL Server, pgAdmin 4, and Command Line Tools.

   1. 

4. Specify a master password for the postgres superuser during setup.

   1. 

   2. Take note of the password!

5. Specify the port of the Postgres server:

   1. 

Linux (Ubuntu / Debian)

sudo apt update
sudo apt install -y postgresql postgresql-contrib

Linux (Red Hat / Rocky / Fedora)

sudo dnf install -y postgresql-server postgresql-contrib
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

2. Network & Authentication Configuration

By default, PostgreSQL only accepts connections on localhost. Perform these steps if AVIX Server runs on a separate machine:

Edit

postgresql.conf

• Windows Location: C:\Program Files\PostgreSQL\<version>\data\postgresql.conf

• Linux Location: /etc/postgresql/<version>/main/postgresql.conf (Debian/Ubuntu) or /var/lib/pgsql/data/postgresql.conf (RHEL)

Set the listen address and verify the port:

listen_addresses = '*'
port = 5432

Edit pg_hba.conf

In the same data directory, add an authorization rule for the AVIX Server host IP:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             <AVIX_SERVER_IP>/32     scram-sha-256

Restart Service & Firewall

• Windows: Restart postgresql-x64-<version> via services.msc and allow TCP port 5432 in Windows Defender Firewall.

• Linux:

  Bash

  sudo systemctl restart postgresql
  
  # Firewall (Ubuntu UFW)
  sudo ufw allow 5432/tcp
  
  # Firewall (RHEL firewalld)
  sudo firewall-cmd --add-port=5432/tcp --permanent && sudo firewall-cmd --reload
  

3. Database & User Creation

Log into psql using the postgres administrator account:

# Linux
sudo -i -u postgres psql

# Windows (SQL Shell / CMD)
psql -U postgres

Windows: Should the PostgreSQL bin directory not be in your system's PATH environment variable, instead use the full path: 

# Windows (SQL Shell / CMD)
"C:\Program Files\PostgreSQL\18\bin\psql.exe" -U postgres

Execute the following SQL commands to prepare the environment. 

Make sure to set the password of your choice (instead of 'verysecret'), and store it in a safe place.

-- 1. Create dedicated application user
CREATE USER avix_postgres WITH PASSWORD 'verysecret';

-- 2. Create target database owned by the new user
CREATE DATABASE avix_db OWNER avix_postgres;

-- 3. Connect to the new database   (ensures no permission errors occur when AVIX Server connects and table structures are created)
\c avix_db

-- 4. Grant schema-level permissions (required for PostgreSQL 15+)
GRANT ALL ON SCHEMA public TO avix_postgres;

Example: 

4. Verification & AVIX repositories.xml Integration

Connection Verification

Test connection from the machine hosting AVIX Server prior to service launch:

psql -h <serverHost> -p 5432 -U avix_postgres -d avix_db

XML Configuration

Update repositories.xml on the AVIX Server host to reflect the database parameters: 

<repositoryConfig>
    <repository name="postgre_repo" 
                type="se.solme.avix.server.cdo.postgresql"
                serverHost="localhost"
                portNumber="5432"
                databaseName="avix_db"
                databaseUser="avix_postgres"
                databasePassword="verysecret"
                useSSL="false"/>
</repositoryConfig>

The name field (set to 'postgre_repo' here) is how this repository - a connection to the underlying db - will be presented in AVIX client UI. 

5. Activating SSL

Server-side setup (postgreSQL)

To enable SSL on PostgreSQL, three steps are needed:

1. Obtain or Generate Certificates:

   • A Server Certificate (server.crt) and Private Key (server.key).

2. Place Keys in Data Directory:

   • Move them to the PostgreSQL data directory (e.g., C:\Program Files\PostgreSQL\18\data\).

   • Permissions note: On Linux, server.key must have strict permissions (chmod 0600).

3. Enable SSL in postgresql.conf:

   Ini

   ssl = on
   ssl_cert_file = 'server.crt'
   ssl_key_file = 'server.key'
   

4. Enforce SSL in pg_hba.conf (Optional but recommended for security): Change host to hostssl so non-encrypted connections are rejected:

   Plaintext

   # TYPE    DATABASE        USER            ADDRESS                 METHOD
   hostssl   all             all             <AVIX_SERVER_IP>/32     scram-sha-256
   

Java/AVIX Server Side

When Java establishes a JDBC connection to PostgreSQL with SSL requested (ssl=true ), behavior depends on the certificate type:

• CA-Signed Certificate (Public or Internal CA): If the PostgreSQL server uses a certificate issued by a recognized Certificate Authority (CA) already trusted by the Java Runtime Environment (JRE) or your enterprise domain, it works seamlessly with zero client configuration.

• Self-Signed Certificate: If a self-signed certificate was generated, Java's security manager will block the connection by default because it doesn't trust the unknown CA (PKIX path building failed).