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.
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.
