Hi,
When a superuser creates a foreign table with file_fdw, the target file
can belong to any user, as long as postgres can read it.
This is useful when the data files are handed by non-postgres tools,
such as a spreadsheet.
In such a case, it makes sense that the superuser would additionally grant
SELECT on the foreign table to a non-superuser to process the contents.
But once the foreign table is set up, a malicious user owning the file
or even the containing directory can replace it by a link to any file that
can be read only by postgres, such as the server's private key.
With SELECT permission granted on the table, the non-superuser can
then proceed to read the contents in SQL. I've tried it successfully
with 9.3rc1.
As a result, it means that an admin should not allow a file as the target of
a foreign table if it can be replaced on the filesystem by a non-postgres
user. It may be writable by a non-postgres user, though, which is
quite useful with moving data.
This is a bit subtle. To avoid the whole issue, how about adding an
option in file_fdw forcing it to check each time it opens the file
that its ownership and maybe permissions have not changed
since CREATE FOREIGN TABLE?
Or is there a simpler way to deal with the above case?
Best regards,
-- Daniel VéritéPostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org