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
[Unit]
Description=PostgreSQL database server
After=network.target media-storage_nocow.mount
Requires=media-storage_nocow.mount
OnFailure=status-email-jotham@%i.service

[Service]
Type=forking
TimeoutSec=120
User=postgres
Group=postgres

Environment=PGROOT=/media/storage_nocow/postgres/

SyslogIdentifier=postgres
PIDFile=/media/storage_nocow/postgres/data/postmaster.pid

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
OOMScoreAdjust=-200

# Additional security-related features
PrivateTmp=true
ProtectSystem=full
NoNewPrivileges=true

[Install]
WantedBy=multi-user.target

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:

NoNewPrivileges

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