BYTEA -Feld wieder als Binärdatei

[akretschmer]

Hi,

mein Kunde suchte sowas und die Recherche im Internet ist nicht wirklich erhellend.

Daher kurze Lösung:

edb=# create table pdf_store(id int generated always as identity primary key, filename text, filecontent bytea);
CREATE TABLE

--
-- diverse Versuche, eine Datei zu laden
--

edb=*# insert into pdf_store (filename, filecontent) values ('PostgreSQL14Beta1_New_Features_en_20210521-1.pdf', pg_read_binary_file('/tmp/PostgreSQL14Beta1_New_Features_en_20210521-1.pdf')::bytea);
INSERT 0 1
edb=*# select id, filename, left(filecontent,100) from pdf_store;
 id |                     filename                     |                                                 left                                                 
----+--------------------------------------------------+------------------------------------------------------------------------------------------------------
  2 | PostgreSQL14Beta1_New_Features_en_20210521-1.pdf | \x255044462d312e370d25e2e3cfd30d0a31353036332030206f626a0d3c3c2f4c696e656172697a656420312f4c20313138
  4 | PostgreSQL14Beta1_New_Features_en_20210521-1.pdf | \x255044462d312e370d25e2e3cfd30d0a31353036332030206f626a0d3c3c2f4c696e656172697a656420312f4c20313138
(2 rows)

Habe nun also 2 identische PDF's gespeichert.

Jetzt auf der Command-Line:

akretschmer@akretschmer-desktop:~/EDB$ /usr/lib/edb-as/13/bin/psql.bin -p 5444 edb -tAc "select encode(filecontent,'base64') from pdf_store where id = 2" | base64 -d > /tmp/pdf.pdf
akretschmer@akretschmer-desktop:~/EDB$ md5sum /tmp/PostgreSQL14Beta1_New_Features_en_20210521-1.pdf
86f0b8cc6356e25b4b920d67add4a82f  /tmp/PostgreSQL14Beta1_New_Features_en_20210521-1.pdf
akretschmer@akretschmer-desktop:~/EDB$ md5sum /tmp/pdf.pdf
86f0b8cc6356e25b4b920d67add4a82f  /tmp/pdf.pdf
akretschmer@akretschmer-desktop:~/EDB$

Ich blase das also aus psql als Base64-codierten Kram raus, pipe es durch base64 (ein Tool) und dekodiere es wieder zurück in Hex und speicher das als Datei. md5sum ist gleich der Ursprungsdatei.

Prost.

 

[dabadepdu]

Verstehe ich nicht ganz. Das liegt an der Zwischenverarbeitung im psql für die Ausgabe? Read kommt offenbar ohne Encoding aus und es scheint kein Gegenstück "write" zu geben? Ich kenne sowas wie base64 Encoding als Vehikel, Binärdaten in Text Scripten zu verarbeiten. Aber darum geht es hier ja gar nicht.

 

[akretschmer]

ja, das ist das Problem: es gibt kein pg_write_binary_file(), und über die libpq-Schnittstelle kannst Du keine Binärdaten schicken. Das pg_read_binary_file() funktioniert auch nur mit Dateien, die im Filesystem vom Serverprozess aus sichtbar sind. Hab ich hier nur verwendet, um überhaupt erst einmal valide PDF-Daten in BYTEA einzulesen.

 

[dabadepdu]

Ok, die Serverperspektive ist ja wahrscheinlich gewollt (Anforderung) und muss dann im Data Verzeichnis abgewickelt werden.
Wenn man wegen der Anforderung ein Programm (Client) auf dem Server einsetzen würde, wäre es doch aber kein Problem oder? Und man könnte auch andere Verzeichnisse nutzen.
Also die Lösung von Dir ist sozusagen "mit Bordmitteln".

 

[akretschmer]

Ok, die Serverperspektive ist ja wahrscheinlich gewollt (Anforderung) und muss dann im Data Verzeichnis abgewickelt werden.

Nein, exakt das Gegenteil. Kunde unterliegt MASSIVEN Regulationen, u.a. was Sicherheit anbelangt. Pharma-Industrie. Die Alternative wäre mit lo_from_bytea() und lo_export() zu arbeiten, auf dem Server, mit Superuserrechten. Das ist aber nicht akzeptabel.
Also, es muß übers Netz gehen, als normaler Nutzer, mit einem normalen Client.

 

[dabadepdu]

Jetzt versteh ich es noch weniger, glaub ich. read_binary ist doch auch/nur eine Serverfunktion, die auf definierte (data directory) Verzeichnisse beschränkt ist. Oder ist Dein Beispiel oben auf dem Client? Und umgekehrt die lo_.. Funktionen gibt es angeblich unter anderem Namen auch auf dem Client.
Und grundsätzlich, wenn es sowieso ein Client Programm sein soll (ob auf Server oder lokal), ist es nicht vollkommen normal, Binärdaten per Client in Binärfeld (bytea) zu schreiben und daraus zu lesen? Diese Daten dann als Datei zu speichern oder aus einer Datei zu lesen ist doch ein Client Problem oder? Oder laufen die Client Funktionen auch alle über Libpq?
Und noch was anderes: Gibt es nicht bei PG die Möglichkeit in SP invoker und definer/owner zu unterscheiden? Also eine Proc, die einem anderen User gehört (mit SU Privileges), aber einem anderen User gegranted wird?

 

[akretschmer]

Jetzt versteh ich es noch weniger, glaub ich. read_binary ist doch auch/nur eine Serverfunktion, die auf definierte (data directory) Verzeichnisse beschränkt ist. Oder ist Dein Beispiel oben auf dem Client? Und umgekehrt die lo_.. Funktionen gibt es angeblich unter anderem Namen auch auf dem Client.

Okay, schauen wir in die Doku:

"The server-side lo_import and lo_export functions behave considerably differently from their client-side analogs. These two functions read and write files in the server's file system, using the permissions of the database's owning user. Therefore, by default their use is restricted to superusers. In contrast, the client-side import and export functions read and write files in the client's file system, using the permissions of the client program. The client-side functions do not require any database privileges, except the privilege to read or write the large object in question."

Würde also auch auf dem Client gehen. Mea culpa.

Und grundsätzlich, wenn es sowieso ein Client Programm sein soll (ob auf Server oder lokal), ist es nicht vollkommen normal, Binärdaten per Client in Binärfeld (bytea) zu schreiben und daraus zu lesen? Diese Daten dann als Datei zu speichern oder aus einer Datei zu lesen ist doch ein Client Problem oder? Oder laufen die Client Funktionen auch alle über Libpq?

Ungetestet jetzt, aber vermutlich 'kümmert' sich die libpq dann transparent darum.

Und noch was anderes: Gibt es nicht bei PG die Möglichkeit in SP invoker und definer/owner zu unterscheiden? Also eine Proc, die einem anderen User gehört (mit SU Privileges), aber einem anderen User gegranted wird?

Korrekt, das geht.

 

[dabadepdu]

Ok, also ich versuch mal zu sortieren:
Man könnte SP eines "fremden" Superuser zum Schreiben/Lesen nutzen und der reguläre User bräuchte keine Superuser Rechte, nur Grants für diese SP.
Zusätzlich könnte man im Client die Clientvariante der lo_ Funktionen nutzen.
Oder durch Clientprogrammierung, die Binärdaten von/zu Postgres mit Clientseitigen Routinen im FS als Datei schreiben/lesen. (ohne pg_read_binary_file und encoding)