Re: Reading from file without superuser privilege

On 03/21/2013 04:43 AM, Jose Antonio Quintana/UPC wrote:
> I need to read from a file in order to update a table.
>
> The manual says that it is necessary to have the superuser privilege to
> read from a file.
>
> Is it possible to read files without this privilege?

What sort of file, any file or one you want to do a COPY or \copy from?

For any file you would need to use one of the untrusted languages,
plpythonu for example. They need to be installed by a superuser. It is
possible to create a function in an untrusted language as the superuser
and then confer the superuser privileges to other users for that
function by using SECURITY DEFINER, see here:

http://www.postgresql.org/docs/9.2/interactive/sql-createfunction.html

For COPY :
"The file must be accessible to the server and the name must be
specified from the viewpoint of the server. "

http://www.postgresql.org/docs/9.2/interactive/sql-copy.html

For \copy:
"This is an operation that runs an SQL COPY command, but instead of the
server reading or writing the specified file, psql reads or writes the
file and routes the data between the server and the local file system.
This means that file accessibility and privileges are those of the local
user, not the server, and no SQL superuser privileges are required."

http://www.postgresql.org/docs/9.2/interactive/app-psql.html

>
> Thanks.
>
>
> _______________________________________________
> José Antonio Quintana Romero
> Unitat de Projectes
> Vicegerència de Desenvolupament Organitzatiu i Personal
> Edifici Vèrtex. Planta 3
> Pl. Eusebi Güell, 6
> 08034 - Barcelona

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com