Thread: lo wrappers - still working on it

Perhaps someone can point me towards what I'm missing.  I've included my make
command, the command for creating a function and the actual code I wrote
(derived from the sample code in the docs).  Incidentally, this project is too
far along to change to java or any other interface.  I currently am handling
the files to be lo's as system files but the request is in to save them as
blobs.

This is the result of my make:

gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -fpic
-I../../src/interfaces/libpq -I../../src/include   -c -o amslo.o amslo.c
amslo.c:19: warning: no previous prototype for `ams_loexport'
gcc -shared -o amslo.so amslo.o


This is the result of my trying to create a function:

wcgc_pg=# create function ams_loexport(Oid, char)
wcgc_pg-# returns opaque
wcgc_pg-# as '/usr/local/postgresql-7.1/contrib/WCGC_PG/amslo.so'
wcgc_pg-# language 'C';
ERROR:  Load of file /usr/local/postgresql-7.1/contrib/WCGC_PG/amslo.so
failed: /usr/local/postgresql-7.1/contrib/WCGC_PG/amslo.so: undefined symbol:
PQexec
wcgc_pg=#

And finally, this is my attempt at coding the function:

/*---------------------------------------------------------
 *
 *  amslo.c--
 *   large object handling for wcgc_pg documents
 *
 *
 *---------------------------------------------------------
 */
#include <stdio.h>
#include "libpq-fe.h"
#include "libpq/libpq-fs.h"

#define BUFSIZE        1024

/*
 * ams_loexport * export large object to file
 */
void ams_loexport(Oid lobjId, char *filename)
{
  PGconn *conn;
  PGresult *res;

  conn = PQsetdb(NULL, NULL, NULL, NULL, "wcgc_pg");
  if (PQstatus(conn) == CONNECTION_BAD)
  {
    fprintf(stderr, "Connection to wcgc_pg failed.\n");
    fprintf(stderr, "%s", PQerrorMessage(conn));
    return;
  }

  res = PQexec(conn, "begin");
  PQclear(res);

  lo_export(conn, lobjId, filename);

  res = PQexec(conn, "end");
  PQclear(res);
  PQfinish(conn);
  return;
}

On Tue, Jul 03, 2001 at 08:56:20PM -0700, Scott Holmes wrote:
> Perhaps someone can point me towards what I'm missing.  I've included my make
> command, the command for creating a function and the actual code I wrote
> (derived from the sample code in the docs).  Incidentally, this project is too
> far along to change to java or any other interface.  I currently am handling
> the files to be lo's as system files but the request is in to save them as
> blobs.

You've written a client-side program, but are trying to use it as a
server side function.  I don't think that'll work.  You don't want to be
using libpq for server side functions.  I don't know the Java interface, but
it seems you are trying to do what is already available via
lo_import/lo_export functions.  Maybe you could explain the interface
you need a little better.  Is it not possible to use the lo_import() and
lo_export functions that are already available? (Why they are hidden in
the Programmer's Guide is beyond me...).

SELECT lo_export (<table>.<column>, '/path/to/file') from <table>;
INSERT INTO <table> (<column>) VALUES (lo_import ('/path/to/file'));

Someone recently suggested using bytea fields for blobs as well...

--
Eric G. Miller <egm2@jps.net>

> You've written a client-side program, but are trying to use it as a
> server side function.  I don't think that'll work.  You don't want to be
> using libpq for server side functions.  I don't know the Java interface, but
> it seems you are trying to do what is already available via
> lo_import/lo_export functions.  Maybe you could explain the interface
> you need a little better.  Is it not possible to use the lo_import() and
> lo_export functions that are already available? (Why they are hidden in
> the Programmer's Guide is beyond me...).
>
> SELECT lo_export (<table>.<column>, '/path/to/file') from <table>;
> INSERT INTO <table> (<column>) VALUES (lo_import ('/path/to/file'));
>

I tried this initially, it results in an error and the following warning:

You must have Postgres superuser privilege to use server-side lo_export().
Anyone can use the client-side lo_export provided by libpq.

PostgreSQL has not been installed on any of the client computers and I would
much rather retain the function in an SQL statement capability for this.

> Someone recently suggested using bytea fields for blobs as well...
>
> --
> Eric G. Miller <egm2@jps.net>
>

I've been considering bytea, however, I do have some parallel development on
PHP 4 using lo's that work just fine.  I have not tried re-writing this stuff
with bytea.  The two separate systems do need to utilize the same database.

Scott Holmes  <sholmes@pacificnet.net> writes:
> I tried this initially, it results in an error and the following warning:
> You must have Postgres superuser privilege to use server-side lo_export().
> Anyone can use the client-side lo_export provided by libpq.

There's a very good reason for that restriction: lo_export/lo_import
allow a client to command reading and writing of any file that the
server can access, with the server's permissions.  Your proposed
functions appear to be the same thing without any security check.

If you are intent on installing such a security hole into your
system, you can define ALLOW_DANGEROUS_LO_FUNCTIONS in config.h
when you build the server.  But God help you if any unfriendlies
get access to your database.

            regards, tom lane

> If you are intent on installing such a security hole into your
> system, you can define ALLOW_DANGEROUS_LO_FUNCTIONS in config.h
> when you build the server.  But God help you if any unfriendlies
> get access to your database.
>
>             regards, tom lane

That doesn't sound the least bit encouraging and as the application is for
legal case management and this particular functionality is for maintaining
legal documents, allowing such security holes is a bad idea.  I'm now
wondering if the PHP functions suffer from the same security risks.

Are there any issues like this using the client side libpq.dll?  Is it
necessary to install the entire postgresql client package on the NTs or can I
get by with just libpq.dll?  I note that it imports from only WSOCK32.dll and
KERNEL32.dll, but as I've mentioned, my knowledge of C is minimal.

Scott Holmes  <sholmes@pacificnet.net> writes:
> Are there any issues like this using the client side libpq.dll?

No, client-side lo_import/lo_export are perfectly okay, because those
are doing file reads and writes in the client's process with the
client's permissions.  This is all about the difference between
server-side and client-side operations.

            regards, tom lane