PostgresSQL dying while inside of jails
I just found myself dealing with cases of Postgres dying while inside jails. The only trace of this issue was these logs from /var/log/messages:
postgres[96982]: [691-1] FATAL: semctl(7667718, 6, SETVAL, 0) failed: Invalid argument postgres[95837]: [691-1] WARNING: terminating connection because of crash of another server process postgres[78159]: [691-1] WARNING: terminating connection because of crash of another server process postgres[95837]: [691-2] 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. postgres[78159]: [691-2] 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. postgres[95837]: [691-3] HINT: In a moment you should be able to reconnect to the database and repeat your command. postgres[78159]: [691-3] HINT: In a moment you should be able to reconnect to the database and repeat your command.
After digging around on the interwebs, I found this reference to the problem, and they referenced a section of the Postgres administration guide, which said this:
If running in FreeBSD jails by enabling sysctl's security.jail.sysvipc_allowed, postmasters running in different jails should be run by different operating system users. This improves security because it prevents non-root users from interfering with shared memory or semaphores in different jails, and it allows the PostgreSQL IPC cleanup code to function properly. (In FreeBSD 6.0 and later the IPC cleanup code does not properly detect processes in other jails, preventing the running of postmasters on the same port in different jails.)
Well, there’s the problem. Following the solution on ServerFault, the solution is to just change the username and uid of the user that’s running the postgres daemon so that it’s unique to each jail that’s running postgres. First, let’s stop postgres if it’s running:
/usr/local/etc/rc.d/postgres stop
Now change the username of the postgres user:
pw usermod -n pgsql -l myjailpgsql
Change the uid for that user. The default is 70, so for each of my jails I’m just bumping the uid up by 1, so one jail would have uid 71, the next would have 72 and so on. Make sure to check the contents of /etc/passwd to make sure the uid you choose is not already taken by some other user.
WARNING: I have no idea if this is a bad idea. I don’t know the intimate details of unix uids, so be careful taking my advice here. If someone reading this is aware of a reason to not do this, please let me know.
pw usermod myjailpgsql -u 71
Changing the uid of the user will cause file permissions to be incorrect, so now we need to change them to be owned by the new user:
chown -R myjailpgsql:pgsql /usr/local/pgsql
And then change the user in rc.conf by adding this:
postgresql_user=“myjailpgsql”
Restart postgres and everything should work now!