COPY TO/FROM PROGRAM has been valuable for our data warehousing operations.
The community consensus was the MS should make this thing disabled by default, and they did. Postgres should too.
https://docs.microsoft.com/en-us/sql/database-engine/configu...
- on a dev box (or your own workstation)
- in production
In the first case, the same person/people using the DBMS can just `sudo su postgres -` to do whatever Postgres would be doing for them.
In the second case, nobody who's a pure user of the database (i.e. anyone who's not the DBA of the cluster) should have the privileges required to `COPY FROM ... PROGRAM` anyway. (They certainly won't on any managed environment like RDS.)
As Raymond Chen says: if you can `COPY FROM ... PROGRAM`, you're already on the other side of the airtight hatchway.
---
Admittedly, though, it'd be nice if `COPY FROM ... PROGRAM` was made into something that didn't equate to superuser privileges. I think that's totally possible.
The simplest first step would be to ship with Postgres a setuid "shim launcher" binary, owned by the OS user `nobody` in the `postgres` group, with permissions 070 (i.e. only Postgres can run it.) Postgres could then just run all its `COPY FROM ... PROGRAM` command lines by fork+exec'ing this binary. A simple sandbox, but usually pretty effective.
(Being `nobody` isn't a perfect sandbox; you would need to trust someone with the privilege to execute `COPY FROM ... PROGRAM` statements about as much as you'd trust them with their own entirely-unprivileged shell account on the host instance. But—as the type of user who you'd give this privilege [e.g. users who can do ETL things to your DB] would already be able to thrash most of the same resources through Postgres that they could thrash with an unprivileged shell account, I don't see there being any extra risk in granting this privilege to such users, IMHO. They can write GBs of data to /tmp as `nobody`? Well, they can create GBs-wide temporary tables in Postgres. Etc.)
A cleaner, longer-term solution, in my mind, would be to actually get Postgres to take advantage of the way POSIX specifies "one user attempting to run a command as another user" privileges: sudoers(5).
Just a few changes would be necessary:
1. Postgres user roles would need to maintain, as a property, a mapping to an OS user of the host instance (either manually, or as a session property discovered from whatever AAA system the user authenticated through when connecting, e.g. LDAP or PAM.)
2. Postgres would execute `COPY FROM ... PROGRAM` statements by attempting to run the command line under the appropriate mapped OS user for the connected session, using sudo(1).
3. Postgres would expect the sysadmin to place entries in /etc/sudoers.d/, enabling its OS user to sudo(1) as specific other OS users for specific commands. It would gracefully handle failure-to-sudo(1) as a failure of the statement.
4. Installing Postgres would add at least one sudoers(5) entry, allowing the OS `postgres` user to execute any command as the OS `nobody` user. Postgres would target the `nobody` user for all `COPY FROM ... PROGRAM` statements by default†, unless you explicitly executed a `COPY FROM ... PROGRAM (... AS HOST USER)` statement.
It really should be up to OS security policy, not application software, to decide whether one OS user (e.g. `postgres`) is allowed to execute a given command line as another given OS user. sudoers(5) is the POSIX way‡ to declare such OS-level policy. Postgres just needs to take advantage of the OS—to communicate to the OS which OS user its commands should be interpreted as being on behalf of.
† Well, the default OS user would have to be configurable, to allow backward compatibility with existing ETL pipelines that expect `COPY FROM ... PROGRAM` to execute as the OS `postgres` user. Just have a global config param `postgres_copy_subcommand_default_os_user`, default it to "postgres" if it's unset (as it would be in existing installs), and update the postgresql.conf template for new installs to set it to "nobody".
‡ I'm not sure how you'd do this on non-POSIX OSes like Windows. I know Windows has `runas`, but that's equivalent to su(1), not sudo(1); it doesn't have policy-based non-password-prompting elevation capabilities. Anyone know what you'd do here? How does e.g. Docker on Windows run containers with specific user privileges?
The confusion probably comes from the notion of a 'database superuser' - the point is that any DB user with enough privileges to run COPY FROM ... PROGRAM can also do anything else in the DB, so they are already considered a DB superuser.
Quote from the article :
> By design, there exists no security boundary between a database superuser and the operating system user the server runs under. As such, by design the PostgreSQL server is not allowed to run as an operating system superuser (e.g. "root").
But, as well, I think it's important to point out that, on a system that's dedicated to being a database instance, there's really not that much difference between being able to execute arbitrary commands as the OS superuser, and being able to execute arbitrary commands as the OS user that the DBMS runs as. If you can run commands as the OS `postgres` user, you can, for example:
- delete the cluster (even if your DBMS user can't do that)
- read all the Postgres configuration files, including any secrets loaded from such files
- change other [DBMS!] users' passwords
- configure a new authentication method that sends credentials to an arbitrary third-party system (e.g. an LDAP server you control), turning the database instance into a login-credential harvester
Etc.
Yes, the OS `postgres` user isn't the OS superuser; but that is kind of meaningless if the box is essentially just a substrate for running Postgres on.
Basically, you were discussing a mechanism for allowing non-admin DB users to safely run COPY FROM... PROGRAM?