Safe to Restore Database from Untrusted Sources

Question

I am asking someone to aggregate around 100GB of data for me. I would prefer this to be in a Postgres database instead of something like CSV. I was thinking pg_dump and pg_restore custom format. Is it safe to restore the database onto my machine? Can they inject something malicious in there?

Answer 1

Is it safe to restore the database onto my machine?

Quite safe if you restore using a non-superuser account. CREATE USER a user for the purpose, CREATE DATABASE ... WITH OWNER ... a db owned by that user, and pg_restore using that user into that database. I'd do it happily enough. The custom format is basically SQL snippets and CSV-like row data in an archive.

To be more careful you can request CSV files and use COPY or psql's \copy to load the CSV files into the database. That's exceedingly safe.

Can they inject something malicious in there?

Yes, if you restore it using a superuser account like the postgres user. They could do something nefarious like create a plperlu, plpythonu, etc procedure (if you have those languages available in your install) that then runs arbitrary code as the postgres user. It probably can't get outside the postgres operating system user and attack the rest of the system. Probably. But local root exploits aren't as hard to find as they ideally would be.

On Windows you're less likely to have plperl and plpython enabled, but you're likely to run PostgreSQL under NETWORKSERVICE, which is quite privileged. So again, I wouldn't, just in case. They might be able to do something nasty with lo_import and lo_export for example.

In general PostgreSQL tries to make it less than entirely trivial for the database superuser to gain shell access as the user it's running as, at least if no procedural languages other than plpgsql are installed. It's not a strong sandbox though, and it's not at all surprising if you can break out of it and get shell access from the postgres superuser.

Shell access, running code as superuser, etc, from non-superuser accounts should not be possible under any circumstances, though, barring gross misconfiguration like creating a SECURITY DEFINER procedure that runs arbitrary SQL as the superuser.