PostgreSQL
This procedure enforces SSL on the connections from SmartAGT components to PostgreSQL, and has them verify the server certificate, in three steps.
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.