Обсуждение: Inserting .png file into bytea column

Good Morning All,

I am using PostgreSQL 9.3 on Linux Rehat...

I'm trying to insert an image (.png format) into a table of flags.  I've tried the following but keep getting errors.

CREATE TABLE FLAGS (country_code text, flag bytea);

INSERT INTO flags VALUES ('AD', pg_read_file('/home/flags')::bytea);

Get the following error:
ERROR:   absolute path not allowed
*********ERROR*************
ERROR:  absolute path not allowed
SQL State:  42501

Any assistance would be greatly appreciated.

Denise Ferrell

Good Morning All,

I am using PostgreSQL 9.3 on Linux Rehat...

I'm trying to insert an image (.png format) into a table of flags.  I've tried the following but keep getting errors.

CREATE TABLE FLAGS (country_code text, flag bytea);

INSERT INTO flags VALUES ('AD', pg_read_file('/home/flags')::bytea);

Get the following error:
ERROR:   absolute path not allowed
*********ERROR*************
ERROR:  absolute path not allowed
SQL State:  42501

Any assistance would be greatly appreciated.

Denise Ferrell

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Thank you!

-----Original Message-----
From: Craig James [mailto:cjames@emolecules.com]
Sent: Thursday, November 19, 2015 10:09 AM
To: Achilleas Mantzios
Cc: Ferrell, Denise D CTR NSWCDD, H11; pgsql-admin@postgresql.org
Subject: [Non-DoD Source] Re: [ADMIN] Inserting .png file into bytea column



On Thu, Nov 19, 2015 at 6:19 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:


    On 19/11/2015 16:07, Ferrell, Denise D CTR NSWCDD, H11 wrote:


        Good Morning All,

        I am using PostgreSQL 9.3 on Linux Rehat...

        I'm trying to insert an image (.png format) into a table of flags.  I've tried the following but keep getting
errors.

        CREATE TABLE FLAGS (country_code text, flag bytea);

        INSERT INTO flags VALUES ('AD', pg_read_file('/home/flags')::bytea);

        Get the following error:
        ERROR:   absolute path not allowed
        *********ERROR*************
        ERROR:  absolute path not allowed
        SQL State:  42501

        Any assistance would be greatly appreciated.


    http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-ADMIN-GENFILE

    use pg_read_binary_file(filename text [, offset bigint, length bigint]) as documented



The documentation you linked to explains the problem:


    The functions shown in Table 9-66 provide native access to files on the machine hosting the server. Only files
withinthe database cluster directory and the log_directory can be accessed. Use a relative path for files in the
clusterdirectory, and a path matching the log_directoryconfiguration setting for log files. Use of these functions is
restrictedto superusers.  


You can't access files on the client side, and on the server side you can only access files that are internal to
Postgres(e.g. inside /data/postgres, or whatever path your Postgres uses for the database). And only super-users can
usethis function at all because it allows access to sensitive server files. 

The easiest way to do this on the client side is to read the file into a client-side variable and then use a prepared
statement.In Perl, something like this: 


    use DBI;
    my $dbh = $dbi->connect(...);

    my $photo;
    open(PHOTO, "/home/flags");
    binmode PHOTO;
    while (<PHOTO>) {
      $photo .= $_;
    }
    my $sth = $dbh->prepare("insert into flags(country_code, flag) values(?, ?)");
    $sth->execute('AD', $photo);


Craig



        Denise Ferrell





    --
    Achilleas Mantzios
    IT DEV Lead
    IT DEPT
    Dynacom Tankers Mgmt




--

---------------------------------
Craig A. James

Chief Technology Officer

eMolecules, Inc.

---------------------------------

Ferrell, Denise D CTR NSWCDD, H11 schrieb am 19.11.2015 um 15:07:
> Good Morning All,
>
> I am using PostgreSQL 9.3 on Linux Rehat...
>
> I'm trying to insert an image (.png format) into a table of flags.  I've tried the following but keep getting errors.
>
> CREATE TABLE FLAGS (country_code text, flag bytea);
>
> INSERT INTO flags VALUES ('AD', pg_read_file('/home/flags')::bytea);
>
> Get the following error:
> ERROR:   absolute path not allowed
> *********ERROR*************
> ERROR:  absolute path not allowed
> SQL State:  42501
>
> Any assistance would be greatly appreciated.
>
> Denise Ferrell
>

If the image is on the computer where the SQL client is running, pg_read_file() can not be used
because it is reading files on the server, not the client.

If you are open to a different SQL client, the one I am maintaining - SQL Workbench/J - can
do precisely what you want:

     INSERT INTO flags VALUES ('AD', {$blobfile=/home/flags});

This will read the file from the computer where SQL Workbench is running:

But again: this syntax *only* works with SQL Workbench/J

   http://www.sql-workbench.net/blob_example.html

You can also do this directly from within a result of a SELECT statement:

    http://www.sql-workbench.net/BlobDisplay_png.html

Full disclosure: I am the author of that tool.

Thomas