1

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?

DimaSan
  • 12,264
  • 11
  • 65
  • 75

1 Answers1

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.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
  • 1
    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? – DimaSan Jul 15 '16 at 17:24
  • See the jdbc docs ;-) https://jdbc.postgresql.org/documentation/80/binary-data.html – Chris Travers Jul 16 '16 at 06:12
  • Every driver I have ever worked with has facilities to do the conversion for you. JDBC is no different. – Chris Travers Jul 16 '16 at 06:13
  • Thank you for responds Chris. But you see, in my application I'm using Hibernate, that do all ORM work for me. At the beginning I need to upload some initial data to database, for this purpose I use SQL. And I don't know how to insert a `byte[]` data into `BYTEA` field without calling function `pg_read_file` which is not allowed on Heroku server. Putting aside Java, could you suggest me how to do it? – DimaSan Jul 17 '16 at 22:25
  • 1
    Read it into a byte[] in java and then save that in the db? See http://stackoverflow.com/questions/3677380/proper-hibernate-annotation-for-byte – Chris Travers Jul 18 '16 at 07:22
  • 1
    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. – DimaSan Jul 18 '16 at 22:35