Picky Postgres Permissions

Woot - first post in a while! Lots of fun life changes happen(ed/ing) and more projects underway. Hopefully this marks a return to regular posts and I’m looking forward to some new topics and better content.

I was tired of sudoing to pg_restore a database fixture for a contract I’m working on.

I made the data directory group executable and added my user to the postgres group.

Today, I rebooted after updating, and suddenly get an email from my email template service that the postgres service failed to start.

I looked at systemctl status postgres, and it indicated the service failed due to dependencies.

I have a non-copy on write mount point that I use for this stuff and automount through fstab. That is the only requirement, and that service was running!

I took a look at the service unit file

cat /etc/systemd/system/postgresql.service
Description=PostgreSQL database server
After=network.target media-storage_nocow.mount




ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGROOT}/data
ExecStart= /usr/bin/pg_ctl -s -D ${PGROOT}/data start -w -t 120
ExecReload=/usr/bin/pg_ctl -s -D ${PGROOT}/data reload
ExecStop=  /usr/bin/pg_ctl -s -D ${PGROOT}/data stop -m fast

# Due to PostgreSQL's use of shared memory, OOM killer is often overzealous in
# killing Postgres, so adjust it downward

# Additional security-related features


Well, considering that this used to work all the time, and the only thing I changed was permissions on the PGROOT directory, I suspected it has something to do with that and NoNewPrivaleges. Note also that User and Group are specified in the unit file [Service] section. From the man pages:


Takes a boolean argument. If true, ensures that the service process and all its children can never gain new privileges. This option is more powerful than the respective secure bits flags (see above), as it also prohibits UID changes of any kind. This is the simplest, most effective way to ensure that a process and its children can never elevate privileges again.

journalctl shows it’s definitely a permissions issue.

journalctl -u postgresql.service -f
-- Logs begin at Tue 2014-05-13 06:48:52 PDT. --
... systemd[1]: Starting PostgreSQL database server...
... postgres[1882]: FATAL:  data directory "/media/storage_nocow/postgres/data" has group or world access
... postgres[1882]: DETAIL:  Permissions should be u=rwx (0700).
... postgres[1882]: pg_ctl: could not start server
... postgres[1882]: Examine the log output.
... systemd[1]: postgresql.service: Control process exited, code=exited status=1
... systemd[1]: Failed to start PostgreSQL database server.
... systemd[1]: postgresql.service: Unit entered failed state.
... systemd[1]: postgresql.service: Triggering OnFailure= dependencies.
... systemd[1]: postgresql.service: Failed with result 'exit-code'.

NoNewPrivileges doesn’t seem to govern this. That log is from the postgres process.

Supposedly, compiling with this setting changed is necessary. Sounds more time consuming. Humbug. sudo -u postgres pg_restore it is, for now…

Go Top
comments powered by Disqus