Thread: Reading from file without superuser privilege

Reading from file without superuser privilege

From
Jose Antonio Quintana/UPC
Date:
<font face="sans-serif" size="2">I need to read from a file in order to update a table.</font><br /><br /><font
face="sans-serif"size="2">The manual says that it is necessary to have the superuser privilege to read from a
file.</font><br/><br /><font face="sans-serif" size="2">Is it possible to read files without this privilege?</font><br
/><br/><font face="sans-serif" size="2">Thanks.</font><br /><font face="sans-serif" size="2"><br /><br />
_______________________________________________<br/> José Antonio Quintana Romero<br /> Unitat de Projectes<br />
Vicegerènciade Desenvolupament Organitzatiu i Personal<br /> Edifici Vèrtex. Planta 3<br /> Pl. Eusebi Güell, 6 <br />
08034- Barcelona</font> 

Re: Reading from file without superuser privilege

From
Adrian Klaver
Date:
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.klaver@gmail.com