PostgreSQL in FreeBSD Jail

16.04.2020 - ~5 Minutes

For some use cases it can be interesting to put services into a jail. Putting PostgreSQL into its own jail has an advantage if you are using pkg to install your software. It can happen that the package you want to install is compiled with a different version of PostgreSQL. In that case you cannot install the new package without touching your production database. By separating the database you will not have to worry about any incompatibilities anymore.

This page will go through the necessary steps in order te run PostgreSQL in a FreeBSD jail.

Step 1: Jail creation

Create a new jail with ezjail.

root@pgjail:~ # ezjail-admin create pgjail 'lo1|127.0.1.1,bge0|192.168.1.10'

Modify jail for PostgreSQL

Before you proceed edit the jail config.

Edit /usr/local/etc/ezjail/pgjail

# To specify the start up order of your ezjails, use these lines to
# create a Jail dependency tree. See rcorder(8) for more details.
#
# PROVIDE: standard_ezjail
# REQUIRE: 
# BEFORE:
#
export jail_pgjail_hostname="pgjail"
export jail_pgjail_ip="lo1|127.0.1.1,bge0|192.168.1.10"
export jail_pgjail_rootdir="/usr/jails/pgjail"
export jail_pgjail_exec_start="/bin/sh /etc/rc"
export jail_pgjail_exec_stop=""
export jail_pgjail_mount_enable="YES"
export jail_pgjail_devfs_enable="YES"
export jail_pgjail_devfs_ruleset="devfsrules_jail"
export jail_pgjail_procfs_enable="YES"
export jail_pgjail_fdescfs_enable="YES"
export jail_pgjail_image=""
export jail_pgjail_imagetype=""
export jail_pgjail_attachparams=""
export jail_pgjail_attachblocking=""
export jail_pgjail_forceblocking=""
export jail_pgjail_zfs_datasets=""
export jail_pgjail_cpuset=""
export jail_pgjail_fib=""
export jail_pgjail_parentzfs=""
export jail_pgjail_parameters="sysvmsg=new sysvsem=new sysvshm=new"
export jail_pgjail_post_start_script=""
export jail_pgjail_retention_policy=""

The important part is export jail_pgjail_parameters="sysvmsg=new sysvsem=new sysvshm=new"

From the man page JAIL(8):

sysvmsg
Allow access to SYSV IPC message primitives. If set to “inherit”, all IPC objects on the system are visible to this jail, whether they were created by the jail itself, the base system, or other jails. If set to “new”, the jail will have its own key namespace, and can only see the objects that it has created; the system (or parent jail) has access to the jail’s objects, but not to its keys. If set to “disable”, the jail cannot perform any sysvmsg-related system calls.

sysvsem, sysvshm
Allow access to SYSV IPC semaphore and shared memory primitives, in the same manner as sysvmsg.

Step 2: Start jail

Start the jail and connect to the console.

root@pgjail:~ # ezjail-admin start pgjail
root@pgjail:~ # ezjail-admin console pgjail

Step 3: Jail configuration

Configuration inside jail

Before the jail can access the internet for installation you need to configure a nameserver. Herefore you need to edit /etc/resolv.conf

nameserver 192.168.1.1

Step 4: Installation of PostgreSQL

Install PostgreSQL server.

Now you are ready to install PostgreSQL server in the jail.

root@pgjail:~ # pkg install postgresql12-server
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
The following 7 package(s) will be affected (of 0 checked):

    New packages to be INSTALLED:
    gettext-runtime: 0.20.1
    icu: 66.1,1
    indexinfo: 0.3.1
    perl5: 5.30.2
    postgresql12-client: 12.2
    postgresql12-server: 12.2_1
    readline: 8.0.4

    Number of packages to be installed: 7

    The process will require 142 MiB more space.
    32 MiB to be downloaded.

    Proceed with this action? [y/N]:

Add PostgreSQL to /etc/rc.conf to enable it on start of the jail.

postgresql_enable="YES"

PostgreSQL needs to be initialized before it can be started.

 root@pgjail:~ # /usr/local/etc/rc.d/postgresql initdb

Error initialization

If you did not adjust the jail as described earlier you will not be able to initialize the database. You will get an error:

root@pgjail:~ # /usr/local/etc/rc.d/postgresql initdb                                                                                                  
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/db/postgres/data12 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 20
selecting default shared_buffers ... 400kB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... 2020-04-17 04:23:22.188 UTC [34146] FATAL:  could not create shared memory segment: Function not implemented
2020-04-17 04:23:22.188 UTC [34146] DETAIL:  Failed system call was shmget(key=5432001, size=48, 03600).
child process exited with exit code 1
initdb: removing data directory "/var/db/postgres/data12"

PostgreSQL successfully initialized

If you enabled sysvmsg then everything should work and you can initialize the db.

root@pgjail:~ # /usr/local/etc/rc.d/postgresql initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/db/postgres/data12 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/bin/pg_ctl -D /var/db/postgres/data12 -l logfile start

root@pgjail:~ #

Post initialization

Now you can start PostgreSQL server.

root@pgjail:~ # service postgresql start
2020-04-17 04:31:43.949 UTC [39302] LOG:  starting PostgreSQL 12.2 on amd64-portbld-freebsd11.3, compiled by FreeBSD clang version 8.0.0 (tags/RELEASE_800/final 356365) (based on LLVM 8.0.0), 64-bit
2020-04-17 04:31:43.950 UTC [39302] LOG:  could not create IPv6 socket for address "::1": Protocol not supported
2020-04-17 04:31:43.950 UTC [39302] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2020-04-17 04:31:43.973 UTC [39302] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-04-17 04:31:44.009 UTC [39302] LOG:  ending log output to stderr
2020-04-17 04:31:44.009 UTC [39302] HINT:  Future log output will go to log destination "syslog".
root@pgjail:~ #

Post installation

Finally you are able to connect to the PostgreSQL server and start with your database administration.

root@pgjail:~ # psql -U postgres
psql (12.2)
Type "help" for help.

postgres=#

FreeBSD Ezjail PostgreSQL