0

I'm using Postgraphile to interact with PostgreSQL, and I need upload images to server from my Android app. I plan to send an image as a base64 encoded string by using a mutation, this mutation is a PostgreSQL function that when is called will take the value for image and will save it into file system, getting the file path which will be saved to database.

Is it possible? how? Any suggestion is welcome.

6
  • 1
    There is no thing directly built into Postgres. You would need to write your own function that uses pg_file_write
    – user1822
    Commented Nov 2, 2021 at 12:50
  • 1
    Wouldn't it be easier to let Postgraphile handle the filesystem part? Commented Nov 2, 2021 at 12:53
  • @GerardH.Pille , how do I get it? Commented Nov 2, 2021 at 13:59
  • I don't get your question. Commented Nov 2, 2021 at 14:51
  • I mean, What should I do to let Postgraphile handle the filesystem part? and How should I do it? Commented Nov 2, 2021 at 16:04

1 Answer 1

2

Writing files through SQL queries is not allowed to normal, unprivileged users, as it gives the ability to bypass any security measures and corrupt any database file.

That being said, users with the pg_execute_server_program role or the superuser status can write a base64-encoded image into a file with an SQL COPY command looking like this:

COPY (<subquery returning the base64 string>)
  TO PROGRAM 'sed ''s/\\n/\n/g'' | base64 -d > /path/to/the/file';

This assumes that the base64 command is installed on the server.

lo_export is another option that might be considered. The large object subsystem is precisely meant to get files into and out of the database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.