Creating a secure transcient duplicate of a folder for consumption by ‘psql’ running as ‘postgres’

Question

This is a question about finding an ideal way to deal with a particular situation.

In a folder under my home folder is a group of psql script files that reference each others through \include_relative meta-commands. These scripts sometimes need to be run through psql as a database superuser. Because postgres is peer-authenticated, I must call psql with sudo --user postgres. But since the files are in my home folder, the OS user postgres has no access to them. Now, if there was only a single script file to read, I could simply use a shell redirection, as in sudo -u postgres psql < ~/psql/myscript.psql, but as soon as myscript.psql includes another file, then psql won't find it.

There are various simple and trivial ways of making this work. However, I exclude manually copying/moving/deleting files around every time I need to run a script, and I want to retain the ability to freely move around and rename the source folder and script files without breaking things. Furthermore, this is a mission-critical production server, so interfering with the PostgreSQL service, as in changing the cluster's configuration — for example, creating additional superusers, or changing authentication settings — is out of the question. Ideally, we want this to be transparent to the rest of the server, which excludes solutions involving pre-configured (e.g. ftab, systemd-mount) bind-mount and overlays, or other changes to the server's configuration.

I am looking specifically for a command that makes the script folder available on-the-fly to psql, and only for the duration of the script run. My solution so far is this:

$ sudo mount --bind -o ro,X-mount.mkdir=777 ./psql ~postgres/tmp && \
  sudo -iu postgres psql -f ~postgres/tmp/myscript.psql; \
  sudo umount ~postgres/tmp && \
  sudo rmdir ~postgres/tmp

But still, I find it verbose and I'm trying to improve on it. And I don't like the creation of a tmp folder in ~postgres. Has anybody better ideas?

Edit: Following @larsks' comment below, I realize that my above solution has a privacy flaw in systems where multiple user are given access to the postgres user.

Answer 1

We create a shell script run-with-tempdir that looks like this:

#!/bin/sh

tmpdir=$(mktemp -d ~postgres/tmp/psqlXXXXXX)
trap "rm -rf $tmpdir" EXIT

tar -C "$tmpdir" -xf- &&
  psql -f "$tmpdir/$1"

And then we run it like this:

tar -C psql -cf- . | sudo -u postgres run-with-tempdir myscript.psql

This (a) doesn't expose your home directory to the postgres user, (b) doesn't expose your scripts to any other users on the system (because the tmpdir is create with mode 700), (c) doesn't require bind mounts (which can cause problems if an error prevents the unmount from happening), and (d) cleans up after itself automatically.