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?

Answer 1

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.