0

I have a small database in which I want to store images in a separate table. I have been reading different methods and believe due to the small size of the DB there is no issue in physically storing the image within the DB.

I have been reading and trying to get the sql code correct in order to upload one image into a new table. However I am running into "SQL Error [42501]: ERROR: could not open file.... for reading: Permission denied".

I have tried to change permission access by clicking Command + I on the folder and at the bottom adding "postgres" with "Read & Write" privileges. I closed DBeaver and still not luck.

Is my approach and sql code correct for uploading and image? How do I get around permissions?


create table category  (
"id_category" SERIAL,
"category_name" TEXT,
"category_image" bytea,
constraint id_cat_pkey primary key ("id_category"))without oids;

insert into category (category_name,category_image) values('MANGO', pg_read_binary_file('tmp/IMG_2405.jpeg')::bytea)
Eizy
  • 253
  • 1
  • 9
  • this looks more like a problem with the file sstem try something like https://stackoverflow.com/a/54577270/5193536 – nbk Sep 06 '22 at 21:49
  • I implemented the sudo chown as in the post you linked but got this response "chown: postgres: illegal group name" – Eizy Sep 06 '22 at 21:53
  • Read the docs [Admin functions](https://www.postgresql.org/docs/current/functions-admin.html): `pg_read_binary_file ( )` "This function is restricted to superusers by default, but other users can be granted EXECUTE to run the function." Want to bet your are not running the `INSERT` as a superuser or a user that has been granted `EXECUTE` on the function? – Adrian Klaver Sep 06 '22 at 23:28
  • 1
    @AdrianKlaver that should give a different error: `ERROR: permission denied for function pg_read_binary_file`. I think he probably added perms to just the directory or just the file, but not both. – jjanes Sep 07 '22 at 02:17
  • So I added permissions for the file. How do I add permission for the directory? What do you mean by the directory too? Where the postgres files live? – Eizy Sep 07 '22 at 14:41

1 Answers1

0

I was able to upload the image to postgres. I had to move the picture to a folder with in the same directory as postgres. I am still working how to give permission to folders outside of postgres.

Eizy
  • 253
  • 1
  • 9