Heroku PostgreSQL BLOB insertion fails

Question

I'm going to deploy my application on Heroku, for this purpose I created a database there. As Hibernate's option hibernate.hbm2ddl.auto=create didn't work I wrote a SQL queries manually. Everything went good until I tried to insert a BLOB to the database.

I have a table photo:

CREATE TABLE photo (
  id      SERIAL PRIMARY KEY,
  content BYTEA
);

and I'm doing such insertion query:

INSERT INTO photo (content) VALUES (pg_read_file('./files/images/01_Tomato-Soup.jpg')::BYTEA);

After this step I get an error:

[2016-07-15 18:57:01] [42501] ERROR: must be superuser to read files

Inserting of other entities also fails as they have a foreign key to photo table.

What is this error about, is it possible to insert BLOBs in Heroku database not being a superuser?

Chris Travers · Accepted Answer · 2016-07-15 17:19:36Z

2

Server-side file access is super-user-only because it runs with the file permissions of the server. If you can read file you could, for example read database files in and store that. And you could destroy data through writes.

The file access functions on the server are thus relatively limited to things like administrative actions. For your application you want to do something different. For bytea, use whatever client-side libraries you would to do the escaping. This will be dependent on language (it is different in Perl, PHP, Java, etc).

One thing to note is that the escaping and unescaping of bytea fields takes quite a bit of RAM so figure it may take 8 or more times the ram than the file is large. So that is just one thing to think about.

answered Jul 15, 2016 at 17:19

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

DimaSan Over a year ago

Do you mean I need to convert byte array into a String? I use Java. So is it impossible to store BYTEA in database on Heroku?

Chris Travers Over a year ago

See the jdbc docs ;-) jdbc.postgresql.org/documentation/80/binary-data.html

Chris Travers Over a year ago

Every driver I have ever worked with has facilities to do the conversion for you. JDBC is no different.

Chris Travers Over a year ago

Read it into a byte[] in java and then save that in the db? See stackoverflow.com/questions/3677380/…

DimaSan Over a year ago

the problem was that pg_read_file reads file from the SERVER, not my LOCAL file as I thought. Now everything works perfectly! Thank you again.

|

Collectives™ on Stack Overflow

Heroku PostgreSQL BLOB insertion fails

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related