Thread: psql lo_export documentation

Hi,

The 8.3 psql documentation says this about lo_export:

------8<------------8<------------8<------------8<------------8<------------8<------
       <varlistentry>
         <term><literal>\lo_export <replaceable
class="parameter">loid</replaceable> <replaceable
class="parameter">filename</replaceable></literal></term>

         <listitem>
         <para>
         Reads the large object with <acronym>OID</acronym> <replaceable
         class="parameter">loid</replaceable> from the database and
         writes it to <replaceable
         class="parameter">filename</replaceable>. Note that this is
         subtly different from the server function
         <function>lo_export</function>, which acts with the permissions
         of the user that the database server runs as and on the server's
         file system.
         </para>
         <tip>
         <para>
         Use <command>\lo_list</command> to find out the large object's
         <acronym>OID</acronym>.
         </para>
         </tip>
         </listitem>
       </varlistentry>
------8<------------8<------------8<------------8<------------8<------------8<------

Below is an error message I get when I try to use lo_export from client:

------8<------------8<------------8<------------8<------------8<------------8<------
other_database=> SELECT lo_export(16391, '/tmp/file') FROM
lotest_stash_values;
ERROR:  must be superuser to use server-side lo_export()
HINT:  Anyone can use the client-side lo_export() provided by libpq.
------8<------------8<------------8<------------8<------------8<------------8<------

With experiments I find that lo_export on 8.3 stores the files on the
database server file system. I also think it would be good if the
documentation said that you need to be database superuser to use this

Something along the lines of this?

   Note that this acts with the permissions of the user that the
   database server runs as and on the server's file system. Therefore you
   have to be a database superuser to be allowed to use this function.

On 8.2 I get this on client side import, this is fixed in 8.3.
------8<------------8<------------8<------------8<------------8<------------8<------
other_database=> INSERT INTO lotest_stash_values (loid) SELECT
lo_import('/export/home/ja155679/random_data.dat');
ERROR:  must be superuser to use server-side lo_import()
HINT:  Anyone can use the client-side lo_import() provided by libpq.
------8<------------8<------------8<------------8<------------8<------------8<------

So, on 8.2 it looks for me like you had to be database superuser to be
able to run lo_import, and that files would import from the server, and
that that now is changed in 8.3 so that the files are imported from the
client.

Do we really want lo_import and lo_export to work on different file systems?

-J
--

Jørgen Austvik, Software Engineering - QA
Sun Microsystems Database Technology Group

Jorgen Austvik wrote:
>
> The 8.3 psql documentation says this about lo_export:
>
[...]
>          <term><literal>\lo_export <replaceable
[...]
>          Note that this is subtly different from the server function
>          <function>lo_export</function>, which acts with the permissions
>          of the user that the database server runs as and on the server's
>          file system.

> Below is an error message I get when I try to use lo_export
> from client:
>
> other_database=> SELECT lo_export(16391, '/tmp/file') FROM
> lotest_stash_values;
> ERROR:  must be superuser to use server-side lo_export()
> HINT:  Anyone can use the client-side lo_export() provided by libpq.
>
> With experiments I find that lo_export on 8.3 stores the files on the
> database server file system. I also think it would be good if the
> documentation said that you need to be database superuser to use this

I think you are confusing lo_export(), the server function,
and \lo_export, the psql command.

See the documentation you quoted above and also
http://www.postgresql.org/docs/8.2/static/lo-funcs.html

The function writes to a file on the database server, while the
psql command writes to a file on the database client.

The behaviour has not changed in 8.3, as far as I know.

Yours,
Laurenz Albe

Albe Laurenz wrote:
>> With experiments I find that lo_export on 8.3 stores the files on the
>> database server file system. I also think it would be good if the
>> documentation said that you need to be database superuser to use this
>
> I think you are confusing lo_export(), the server function,
> and \lo_export, the psql command.

Yes, you are correct. The combination of \lo_import and lo_export()
confused me. Thank you!

-J
--

Jørgen Austvik, Software Engineering - QA
Sun Microsystems Database Technology Group