Solve a permission problem found when running pgbackrest as a cron job

Question

I have a permissions error on a ubuntu host from the cron job setup to make a database backup using pgbackrest.

ERROR [041]: : unable to open /var/lib/postgresql/10/main/global/pg_control

The cron job is setup to run under my administrator account. The only option I see to fix this is to change the directory permissions to /var/lib/postgresql/10/main to allow my admin account in, and I don't want to do that.

Clearly only the postgres user has access to this directory and I found that its not possible to setup a cron job using that user. i.e.

postgres@host110:~/$ crontab -e
You (postgres) are not allowed to use this program (crontab)
See crontab(1) for more information

What else can I do? There is no more information on this in the pgbackrest manual.

score 1 · Accepted Answer · answered Sep 24 '19 at 17:18

Only the PostgreSQL OS user (postgres) and its group are allowed to access the PostgreSQL data directory. See this code from the source:

    /*
     * Check if the directory has correct permissions.  If not, reject.
     *
     * Only two possible modes are allowed, 0700 and 0750.  The latter mode
     * indicates that group read/execute should be allowed on all newly
     * created files and directories.
     *
     * XXX temporarily suppress check when on Windows, because there may not
     * be proper support for Unix-y file permissions.  Need to think of a
     * reasonable check to apply on Windows.
     */
#if !defined(WIN32) && !defined(__CYGWIN__)
    if (stat_buf.st_mode & PG_MODE_MASK_GROUP)
        ereport(FATAL,
                (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
                 errmsg("data directory \"%s\" has invalid permissions",
                        DataDir),
                 errdetail("Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).")));
#endif

If the data directory allows the group in, the group will normally also have permissions for pg_control.

So you can allow that pgBackRest user in if you give it postgres' primary user group.

postgres is allowed to create a crontab if the system is configured accordingly.

From man crontab:

Running cron jobs can be allowed or disallowed for different users. For this purpose, use the cron.allow and cron.deny files. If the cron.allow file exists, a user must be listed in it to be allowed to use cron If the cron.allow file does not exist but the cron.deny file does exist, then a user must not be listed in the cron.deny file in order to use cron. If neither of these files exists, only the super user is allowed to use cron. Another way to restrict access to cron is to use PAM authentication in /etc/security/access.conf to set up users, which are allowed or disallowed to use crontab or modify system cron jobs in the /etc/cron.d/ directory.

Yep. Added 'postgres' to cron.allow fixed it. – user3341576 Sep 25 '19 at 06:22 — user3341576, Sep 25 '19 at 06:22

score 0 · Answer 2 · answered Sep 24 '19 at 17:36

If you are able to sudo -u postgres at the prompt, you can do it in your cron job, too.

Your question doesn't reveal which actual commands you are trying to run, but to run thiscommand as postgres, simply

sudo -u postgres thiscommand

If you have su but not sudo, the adaptation is minor but not entirely trivial:

su -c thiscommand postgres

With sudo you can set fine-grained limitations on what exactly you can do as another user, so in that sense, it's safer than full unlimited su.

Maybe see also https://stackoverflow.com/questions/37586811/pass-commands-as-input-to-another-command-su-ssh-sh-etc — tripleee, Sep 24 '19 at 17:39

Solve a permission problem found when running pgbackrest as a cron job

2 Answers2