Re: Inserting .png file into bytea column - Mailing list pgsql-admin

From Craig James
Subject Re: Inserting .png file into bytea column
Date
Msg-id CAFwQ8reCNXyLiWaeV0ZCvkBSCt4ajrS0jk08o4ikWLzzc1aDRQ@mail.gmail.com
Whole thread Raw
In response to Re: Inserting .png file into bytea column  (Achilleas Mantzios <achill@matrix.gatewaynet.com>)
Responses Re: [Non-DoD Source] Re: Inserting .png file into bytea column  ("Ferrell, Denise D CTR NSWCDD, H11" <denise.ferrell.ctr@navy.mil>)
Re: Inserting .png file into bytea column  (Dorian Machado <dorian599@gmail.com>)
List pgsql-admin


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 within the database cluster directory and the log_directory can be accessed. Use a relative path for files in the cluster directory, and a path matching the log_directoryconfiguration setting for log files. Use of these functions is restricted to 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 use this 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.
---------------------------------

pgsql-admin by date:

Previous
From: Achilleas Mantzios
Date:
Subject: Re: Inserting .png file into bytea column
Next
From: "Jan-Peter Seifert"
Date:
Subject: Re: postgresql.conf