{"id":281,"date":"2014-02-16T21:14:05","date_gmt":"2014-02-16T21:14:05","guid":{"rendered":"https:\/\/rwec.co.uk\/blog\/?p=281"},"modified":"2014-02-19T12:47:41","modified_gmt":"2014-02-19T12:47:41","slug":"securely-importing-and-exporting-csv-with-postgresql","status":"publish","type":"post","link":"https:\/\/rwec.co.uk\/blog\/2014\/02\/securely-importing-and-exporting-csv-with-postgresql\/","title":{"rendered":"Securely Importing and Exporting CSV with PostgreSQL"},"content":{"rendered":"

Many moons ago, I posted a surprisingly popular answer on StackOverflow<\/a> regarding how to write to CSV files using PostgreSQL. The answer, in a nutshell, is the COPY<\/code> statement. But there’s a catch – PostgreSQL imposes strict security limitations on the use of this statement, which are best dealt with using another feature, the SECURITY DEFINER<\/code> option to CREATE FUNCTION<\/code>. Here, I will attempt to explain in more detail what these security restrictions are, and how to do what you want without simply disabling that security.<\/p>\n

<\/p>\n

CSV made easy: the COPY statement<\/h2>\n

There is no standard mechanism in SQL for reading or writing CSV (or similar) files, but most DBMSes provide at least one approach. In the case of PostgreSQL, this comes in the form of the very flexible COPY FROM<\/code> and COPY TO<\/code>. The relevant manual page<\/a> sums these up pretty neatly:<\/p>\n

COPY<\/code> moves data between PostgreSQL tables and standard file-system files.
\nCOPY TO<\/code> copies the contents of a table to<\/em> a file, while COPY FROM<\/code> copies data from<\/em> a file to a table (appending the data to whatever is in the table already).
\nCOPY TO<\/code> can also copy the results of a SELECT<\/code> query.<\/p><\/blockquote>\n

There are, basically, two places a file could be which PostgreSQL is expected to read or write: on the server<\/em> (i.e. where PostgreSQL itself is running) or on the client<\/em> (i.e. where your SQL is originating, such as a webserver running a PHP script).<\/p>\n

If your file is on the server<\/em>, then the “full” version of COPY<\/code> is available to you: you can write something like COPY (SELECT * FROM public.foo) TO '\/tmp\/foo.csv' WITH (FORMAT CSV);<\/code> or COPY public.foo FROM '\/tmp\/foo.csv' WITH (FORMAT CSV);<\/code>.<\/p>\n

But there’s a snag: you can only use this form if connected to PostgreSQL as the “superuser” (usually called “root”). Why? Because PostgreSQL has no way of knowing which PostgreSQL “roles” should map to which users or permissions on the underlying OS; the process running the DBMS itself may well have access to read and write all sorts of files which you really wouldn’t want someone to get their hands on. In a worst case scenario, an attacker who got hold of an SQL account with COPY<\/code> privileges might be able to create a back-door to take complete control of your server, and PostgreSQL would rather not be responsible for that!<\/p>\n

Side-stepping the problem: accessing files on the client<\/h2>\n

If your file is on the client<\/em>, then the PostgreSQL server can’t just go onto that remote machine and grab the file – like uploading a file to a website, you need to provide the data in some way. Since as far as it’s concerned, you’re not actually reading a file, just throwing it data, PostgreSQL can apply the same security policy it would if you just wrote a bunch of INSERT<\/code> statements, and doesn’t need the superuser-only restriction.<\/p>\n

This is why there are a couple of special cases in the COPY<\/code> command: COPY FROM STDIN<\/code> and COPY TO STDOUT<\/code>. The downside is that it’s up to you to push that data in (or catch it coming out); for instance, graphical tools like PgAdmin III or phpPgAdmin generally have an “Execute query to file” option, which internally sets up a COPY TO STDOUT<\/code> command and saves the result to disk.<\/p>\n

How to be root: SECURITY DEFINER functions<\/h2>\n

Obviously, if it were really only possible to use COPY<\/code> when connected as superuser, it wouldn’t be very useful – you certainly don’t want all your scripts to be regularly connecting as “root” and leaving the door open for attackers to compromise that account. Luckily, there is another feature we can use to our advantage, which is described thus in the manual page for CREATE FUNCTION<\/code><\/a>:<\/p>\n

SECURITY INVOKER<\/code> indicates that the function is to be executed with the privileges of the user that calls it. That is the default.
\nSECURITY DEFINER<\/code> specifies that the function is to be executed with the privileges of the user that created it.<\/p><\/blockquote>\n

This brief explanation belies the power of this option: you can create a function such that, whatever user actually<\/em> runs it, PostgreSQL acts as though it were run as a different user – specifically, the “owner” of the function.<\/p>\n

Hopefully it is obvious how this applies to the topic in hand – define a SECURITY DEFINER<\/code> function while logged in as “root”, and it will be able to use the full COPY<\/code> command, even when your application is logged in as a properly locked-down user. At its simplest, it could look something like this:<\/p>\n

CREATE FUNCTION export_foo()\r\n\tRETURNS VOID\r\n\tSECURITY DEFINER\r\n\tLANGUAGE SQL\r\n\tAS $BODY$\r\n\t\tCOPY (SELECT * FROM public.foo) TO '\/tmp\/foo.csv' WITH ( FORMAT CSV );\r\n\t$BODY$;<\/pre>\n
Obviously, this is a rather awkward function – it exists only to add the SECURITY DEFINER<\/code> attribute to one line of some larger piece of application logic. You could, though, expand this with procedural code (using PL\/pgSQL<\/a>, or one of the other pluggable languages<\/a>) to process the whole task – for instance, define a Temporary Table, fill it from an uploaded CSV, sanity-check the data, and insert it into a permanenent table.<\/p>\n
The dangers of over-generalising<\/h2>\n
Rather than expand the function for a specific purpose, you might be tempted to simply generalise it into one which wraps up the basic options of COPY<\/code>.<\/p>\n
Here’s a simple example of how not<\/strong> to write a general-purpose import function:<\/p>\n
CREATE FUNCTION copy_from_ignoring_all_security(table_name text, file_path text)\r\n        RETURNS VOID\r\n        LANGUAGE plpgsql\r\n        SECURITY DEFINER\r\n        AS $BODY$\r\n\t\tBEGIN\r\n\t\t\t-- This is a really bad idea. Do not copy this function.\r\n\t\t\tEXECUTE '\r\n\t\t\t\tCOPY\r\n\t\t\t\t\t' || quote_ident(table_name) || '\r\n\t\t\t\tFROM\r\n\t\t\t\t\t' || quote_literal(file_path) || '\r\n\t\t\t\tWITH (\r\n\t\t\t\t\tFORMAT CSV, HEADER\r\n\t\t\t\t);\r\n\t\t\t';\r\n\t\t\t-- This is a really bad idea. Do not copy this function.\r\n\t\tEND;\r\n\t$BODY$;<\/pre>\n
This is a really bad idea<\/strong>! The security restriction on COPY<\/code> is there for a good reason, so simply over-riding it as though you know better is clearly not the way to go. Indeed, you could generalise this further, and write a function which took a string of SQL, and executed it as a superuser – thus giving any user which could execute that function all the rights of a superuser. This is actually quite a common mistake; for instance, some Linux systems configure the sudo<\/code> command so that users can run any command as root at will.<\/p>\n
\"'Make<\/a>
\nImage courtesy of xkcd (Randall Munroe)<\/a>, used under a Creative Commons Attribution-NonCommercial 2.5 License<\/a>. Also available as a T-shirt.<\/a><\/cite><\/p>\n
This is no more a correct configuration of sudo<\/code> than our hypothetical PostgreSQL function is a correct use of SECURITY DEFINER<\/code>, since it effectively makes users who can sudo<\/code> into superusers. (It does afford some protection, since the user will generally be prompted for their password, like in Windows UAC; but on a multi-user system, it makes any weak user password into a complete system risk).<\/p>\n
Doing it right: letting root say no<\/h2>\n
Rather than “running code as root”, perhaps a better way of phrasing what tools such as sudo<\/code> and SECURITY DEFINER<\/code> should<\/em> be used for is “asking root to run code for you”. The crucial difference being that it is perfectly acceptable – indeed, essential – for root to “say no”, rather than blindly carrying out the requested action.<\/p>\n
In the case of COPY<\/code>, it is up to your function to constrain what users can do – the tighter the restrictions the better – while allowing for all the scenarios your application needs. There are actually two things you need to define:<\/p>\n
    \n
  1. Which files<\/strong> should the user be allowed to read\/write on disk? This might be a particular directory, for instance, and the filename might have to have a suitable prefix or extension.<\/li>\n
  2. Which tables<\/strong> should the user be able to read\/write in the database? This would normally be defined by GRANT<\/code>s in the database, but the function is now running as “root”, so tables which would normally be “out of bounds” will be fully accessible. You probably don’t want to let someone invoke your function and add rows on the end of your “users” table…<\/li>\n<\/ol>\n
    You might want to just hard-code as much as possible, but remember to guard against awkward things like \/..\/<\/code> and the dreaded null byte. If in doubt, a tight whitelist always trumps an incomplete blacklist. When you find something that doesn’t meet your requirements, you want to abort, and make sure your application knows you’ve aborted, so you will probably want to make use of RAISE EXCEPTION<\/code><\/a> or something similar.<\/p>\n
    Finally, a couple of extra things to be careful of:<\/p>\n

The command-line psql<\/code> interface, meanwhile, has a special \\copy<\/code> command<\/a>, which you can run as though it were an ordinary COPY<\/code> statement, but with the file handled by the client, not the server. This could be built in to a shell script, and not needing to copy data to\/from the server might be an advantage, but building it into a more complex data import\/export system could be tricky.<\/p>\n