Skip to content

PostgreSQL

This procedure enforces SSL on the connections from SmartAGT components to PostgreSQL, and has them verify the server certificate, in three steps.

  1. Access rulesSSL required for SmartAGT
  2. AuthorityCA certificate
  3. YAMLBackend and Frontend

Prerequisites

  • PostgreSQL prepared: Prepare PostgreSQL.
  • SSL enabled on the PostgreSQL server, with a certificate carrying the name SmartAGT uses to reach it: PostgreSQL documentation.
  • The certificate of the authority (CA) that signed this certificate, in PEM format.
  • PostgreSQL prepared: Prepare PostgreSQL.
  • SSL enabled on every PostgreSQL server of the instance, with a certificate carrying the name of the virtual address, for example postgres.example.test: PostgreSQL documentation.
  • The certificate of the authority (CA) that signed these certificates, in PEM format.

Procedure

1. Require SSL for SmartAGT roles

In pg_hba.conf, replace the SmartAGT rules set in Prepare PostgreSQL with these, still before the existing general permissions:

hostnossl smartagt,smartagt_vector,smartagt_bff all          0.0.0.0/0    reject
hostnossl smartagt,smartagt_vector,smartagt_bff all          ::/0         reject
hostssl   smartagt,smartagt_vector             smartagt     127.0.0.1/32 scram-sha-256
hostssl   smartagt,smartagt_vector             smartagt     ::1/128      scram-sha-256
hostssl   smartagt_bff                         smartagt_bff 127.0.0.1/32 scram-sha-256
hostssl   smartagt_bff                         smartagt_bff ::1/128      scram-sha-256
host      all                                  smartagt,smartagt_bff 0.0.0.0/0 reject
host      all                                  smartagt,smartagt_bff ::/0      reject

The first two rules refuse any connection without SSL to the SmartAGT databases. The final two rejections prevent the SmartAGT roles from falling back on a general permission.

On every PostgreSQL server of the instance, in pg_hba.conf, replace the SmartAGT rules set in Prepare PostgreSQL with these, still before the existing general permissions:

hostnossl smartagt,smartagt_vector,smartagt_bff all          0.0.0.0/0    reject
hostnossl smartagt,smartagt_vector,smartagt_bff all          ::/0         reject
hostssl   smartagt,smartagt_vector             smartagt     <CIDR of the Backend nodes>  scram-sha-256
hostssl   smartagt_bff                         smartagt_bff <CIDR of the Frontend nodes> scram-sha-256
host      all                                  smartagt,smartagt_bff 0.0.0.0/0 reject
host      all                                  smartagt,smartagt_bff ::/0      reject

The first two rules refuse any connection without SSL to the SmartAGT databases. The final two rejections prevent the SmartAGT roles from falling back on a general permission.

Then reload the configuration:

sudo -u postgres psql -X -c 'SELECT pg_reload_conf();' -c 'SHOW ssl;'
& 'C:\app\PostgreSQL\18\bin\psql.exe' -X -h localhost -U postgres -d postgres -c 'SELECT pg_reload_conf();' -c 'SHOW ssl;'

Expected result: ssl is on.

2. Place the authority

Copy the authority certificate to /app/cert, where the Backend and the Frontend read it:

On every Backend and Frontend node, copy the authority certificate to /app/cert, where the services read it:

sudo install -D -m 644 <authority certificate> /app/cert/postgresql-ca.crt
New-Item -ItemType Directory -Force C:\app\cert | Out-Null
Copy-Item <authority certificate> C:\app\cert\postgresql-ca.crt

If the authority is already in the certificate store of the system, skip this step.

Expected result: postgresql-ca.crt is in /app/cert (C:\app\cert on Windows).

3. Declare SSL in the YAML

Add these keys under smartagt.core.database, in the Backend YAML and in the Frontend YAML:

smartagt:
  core:
    database:
      host: postgres.example.test                               # name carried by the server certificate
      sslmode: verify-full                                      # SSL required, server name verified
      sslrootcert: /app/cert/postgresql-ca.crt                  # authority, optional if it is in the system store

Then restart the services:

sudo systemctl restart SmartAGT-Platform-Service smartagt-rag-ingestor-worker smartagt-rag-embed-worker smartagt-scheduler-worker SmartAGT-AI-Gateway-Service smartagt-frontend-service
smartagt:
  core:
    database:
      host: postgres.example.test                               # name carried by the server certificate
      sslmode: verify-full                                      # SSL required, server name verified
      sslrootcert: C:/app/cert/postgresql-ca.crt                # authority, optional if it is in the system store

Then restart the services:

Restart-Service SmartAGT-Platform-Service, smartagt-rag-ingestor-worker, smartagt-rag-embed-worker, smartagt-scheduler-worker, SmartAGT-AI-Gateway-Service, smartagt-frontend-service

Expected result: the services restart and connect. A name outside the certificate or an unknown authority stops them with an SSL verification error.

On every node, add these keys under smartagt.core.database, in the Backend YAML or in the Frontend YAML:

smartagt:
  core:
    database:
      host: postgres.example.test                               # virtual address, carried by the certificate
      sslmode: verify-full                                      # SSL required, server name verified
      sslrootcert: /app/cert/postgresql-ca.crt                  # authority, optional if it is in the system store

Then restart the services of this node, one node after the other:

sudo systemctl restart <services of this node>
smartagt:
  core:
    database:
      host: postgres.example.test                               # virtual address, carried by the certificate
      sslmode: verify-full                                      # SSL required, server name verified
      sslrootcert: C:/app/cert/postgresql-ca.crt                # authority, optional if it is in the system store

Then restart the services of this node, one node after the other:

Restart-Service <services of this node, separated by commas>

Expected result: on every node, the services restart and connect. A name outside the certificate or an unknown authority stops them with an SSL verification error.

Special cases

SmartAGT components on other servers

Add hostssl rules with the exact addresses or CIDRs of the authorized hosts, before the final two rejections:

hostssl   smartagt,smartagt_vector             smartagt     <CIDR of the Backend hosts> scram-sha-256
hostssl   smartagt_bff                         smartagt_bff <CIDR of the Frontend hosts> scram-sha-256

The server certificate must cover the PostgreSQL DNS name used by these hosts. Place the authority on every SmartAGT host.

Instance failover

After a failover, the nodes reach the new primary through the same virtual address. Its certificate must carry this name and be signed by the same authority, otherwise verify-full refuses the connection.

PostgreSQL 18 references: server SSL, client verification, authentication rules.