Feedback for postgresql server/client users and Hardened BSD 14.1/15

[B.C. Cotman]

Hello,

This email message is purely informational so it can be a list
reference in case others encounter this issue with postgres and JiT
errors. Information found from October-November 2024.

I've been working with PostgreSQL 16 then 17 server initially on
Hardened BSD 14.0 - 14.1, then on Hardened BSD 15 with PostgreSQL 17
and 17.1 , and logical replication in a chroot and without chroot,
using pmac rules.

When working with logical replication from one DB on one postgres
server to a Hardened BSD server running with PostgreSQL server 17
(then 17.1) can lead to problems when performing queries on the
destination DB (Hardened BSD server with PostgreSQL server) and then
attempting a psql query on the destination server.

This problem appeared while replication was not yet caught up and
after it was caught up.

When attempting a query (like: "SELECT COUNT(*) FROM table_name;"
content like this would be logged by postgres:

WARNING: error during JITing: Permission denied
ERROR: failed to look up symbol "evalexpr_0_1": Failed to materialize
symbols: { (main, { evalexpr_0_1, evalexpr_0_2, evalexpr_0_0,
evalexpr_0_3 }) }

sometimes logged with:

[HBSD INTERNAL] postgres (jid 0, uid 770) exited on signal 6 (no core
dump - bad address)
[PID-REDACTED] -> pid: PID-REDACTED ppid: PPID-REDACTED p_pax:
0x58555<PAGEEXEC,MPROTECT,SEGVGUARD,ASLR,SHLIBRANDOM,DISALLOWMAP32BIT,<f15>,<f16>,<f18>>
[PID-REDACTED] [HBSD SEGVGUARD] [postgres (PID_REDACTED)] Suspension expired.
[PID-REDACTED] -> pid: PID-REDACTED ppid: PPID-REDACTED p_pax:
0x58555<PAGEEXEC,MPROTECT,SEGVGUARD,ASLR,SHLIBRANDOM,DISALLOWMAP32BIT,<f15>,<f16>,<f18>>

and with enough queries resulting in JiT related errors, the postgres
server was killed/died:
[HBSD INTERNAL] postgres (jid JID-REDACTED, uid UID-REDACTED) exited
on signal 6 (no core dump - bad address)

and the psql client session was dead:

The connection to the server was lost. Attempting reset: WARNING:
terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process
exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
Failed.
The connection to the server was lost. Attempting reset: Failed.
!?>

Once the DB exited ungracefully, the restart and repair would take
5-20 minutes (80GB replicated DB) and refuse new psql sessions:

PG_USER@PG_DBNAME FATAL: the database system is not yet accepting connections

One way to avoid this JiT error/problem was by disabling JiT / LLVM
support in postgres server either through ports config/build of
postgres server pkg by disabling option ''new [ ] LLVM
Build with support for JIT-compiling expressions' or postgres config
"postgresql.conf" disabling like with:

max_parallel_workers_per_gather = 0 # limited by max_parallel_workers

(IIRC, commenting it out leaves this set to default, but setting to
and empty quoted string disables it: )
#jit_provider = 'llvmjit'
jit_provider = ''
#jit_above_cost = 100000
#jit_inline_above_cost = 500000
#jit_optimize_above_cost = 500000
#jit = on
jit = off

But disabling JiT may have a performance cost:
https://www.postgresql.org/docs/current/jit-decision.html

Another route if JiT/LLVM support was still desired was to disable mprotect:
# Default in HardenedBSD 15:
#/sbin/sysctl hardening.pax.mprotect.status=2
# Change to 1 for JiT / LLVM to not log failure and risk killing server:
/sbin/sysctl hardening.pax.mprotect.status=1

It is also likely that this route might work, too but I have not tested it:

(Replace path to bins to those that should have it disabled in system
root or chroot...)

hbsdcontrol pax list /usr/local/bin/postgres
aslr: sysdef
disallow_map32bit: sysdef
harden_shm: sysdef
insecure_kmod: sysdef
mprotect: sysdef
pageexec: sysdef
prohibit_ptrace_capsicum: sysdef
segvguard: sysdef
shlibrandom: sysdef

hbsdcontrol pax disable mprotect /usr/local/bin/postgres

hbsdcontrol pax list /usr/local/bin/postgres
aslr: sysdef
disallow_map32bit: sysdef
harden_shm: sysdef
insecure_kmod: sysdef
mprotect: disabled
pageexec: sysdef
prohibit_ptrace_capsicum: sysdef
segvguard: sysdef
shlibrandom: sysdef

It is likely other support bins for postgres server may also need to
have mprotect disabled if this route is selected.

Letting you all know about this as a possibility in case you were
trying to use postgres server on HardenedBSD and found odd errors
related to JiT and LLVM with your DB, tables and queries.

Thanks, and good luck!