How to avoid PostgREST escaping newlines in a large(ish) text column (containing BASE64 encoded binary data)

[draos]

Hi!

I have a table (a view, actually, but that doesn't matter) where one of the columns is BASE64 encoded binary data generated by the
postgres encode function. BASE64 data is correctly split into 76 character lines terminated by (x10). However, in JSON I receive linefeeds are escaped - I get "\n" string instead of actual one-byte linefeed. (I know that the standard calls for , but decoders work just fine with just .)

I would like to avoid having to replace these escapes with linefeeds on the receiving end. How to politely ask PostgREST not to mangle my long text columns?

Thanks!

[wolfgangwalther]

I know that the standard calls for

Exactly. So that won't change. Also, because you'd need PostgreSQL to change, not PostgREST. We are not doing the transformation to JSON, PostgreSQL does.

BASE64 data is correctly split into 76 character lines terminated by (x10).

Does it need to be? I remember returning base64 somewhere, too, and IIRC, I just removed all newlines on the server to get back a continuous string. Much easier to work with.

[steve-chavez]

but I would bet you a nice six-pack that this happens on PostgREST side

The resultset is not modified at all by PostgREST. Essentially it just does a SELECT json_agg(x) FROM your_table x.

I have tried it, PostgreSQL does it correctly, and ends those lines with (0x10). I do not generate JSON from Postgres (and when I tried that, it still had actual linefeeds).

Maybe try sharing reproducible examples to see what could be wrong, psql preferred. Maybe there's a client side setting difference.

[steve-chavez]

The devel release has a new https://docs.postgrest.org/en/latest/references/observability.html#sql-query-logs, which shows the SQL query being executed.

[DutchGerman]

I'll take a six-pack 🍺

Query:

SELECT json_build_object( 
  'base64', encode('Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at ultrices diam. Aenean sit amet placerat lectus. Sed egestas id mauris non consequat. Sed sem ante, mollis ac congue vitae, tempor ac elit. Nullam commodo arcu et urna pharetra tincidunt. In nec interdum purus. In faucibus, sapien eu egestas semper, leo quam dictum mi, et tristique augue turpis a libero. Morbi scelerisque ligula ipsum, at pretium sem dapibus eget. ', 'base64')
);

Ouput:

{
 "base64":"TG9yZW0gaXBzdW0gZG9sb3Igc2l0IGFtZXQsIGNvbnNlY3RldHVyIGFkaXBpc2NpbmcgZWxpdC4g\nUHJvaW4gYXQgdWx0cmljZXMgZGlhbS4gQWVuZWFuIHNpdCBhbWV0IHBsYWNlcmF0IGxlY3R1cy4g\nU2VkIGVnZXN0YXMgaWQgbWF1cmlzIG5vbiBjb25zZXF1YXQuIFNlZCBzZW0gYW50ZSwgbW9sbGlz\nIGFjIGNvbmd1ZSB2aXRhZSwgdGVtcG9yIGFjIGVsaXQuIE51bGxhbSBjb21tb2RvIGFyY3UgZXQg\ndXJuYSBwaGFyZXRyYSB0aW5jaWR1bnQuIEluIG5lYyBpbnRlcmR1bSBwdXJ1cy4gSW4gZmF1Y2li\ndXMsIHNhcGllbiBldSBlZ2VzdGFzIHNlbXBlciwgbGVvIHF1YW0gZGljdHVtIG1pLCBldCB0cmlz\ndGlxdWUgYXVndWUgdHVycGlzIGEgbGliZXJvLiBNb3JiaSBzY2VsZXJpc3F1ZSBsaWd1bGEgaXBz\ndW0sIGF0IHByZXRpdW0gc2VtIGRhcGlidXMgZWdldC4g"
}

[wolfgangwalther]

Yes, it does, RFC 2045 Section 6.8 requires breaking BASE64 text into lines 76 characters long. Besides, if one removes those newlines (easily done on Postgres side), decoders barf at this non-standard BASE64 text - I have tried it, PostgreSQL does it correctly, and ends those lines with (0x10). I do not generate JSON from Postgres (and when I tried that, it still had actual linefeeds).

This is about "Base64 Content-Transfer-Encoding". PostgreSQL creates that, yes.

But delivering base64 inlined in JSON has nothing to do with "Content-Transfer-Encoding" or MIME at all.

Also, it strikes me as odd that you are citing an RFC, while asking us to return non-standard-compliant JSON instead...

Are you saying that Postgres is generating JSON you are sending, and that this escaping is done by Postgres, not PostgREST, although the select from, say, DBeaver (from a "non-JSON" view that contains a TEXT column which is BASE64 encoded data from a BYTEA column from another table) clearly shows 0x10 ?

As Steve wrote above, we are calling json_agg and, yes, PostgreSQL does the transformation to JSON.

I have no time to go into the source and find where this escaping is happening, but I would bet you a nice six-pack that this happens on PostgREST side.

So you are saying "I don't believe what you're saying, but I'm too lazy to confirm myself.". Not cool.

Here you go:

postgrest/src/PostgREST/Query/SqlFragment.hs

Lines 204 to 220 in 53ca035

	asJsonSingleF :: Maybe Routine -> Bool -> SQL.Snippet
	asJsonSingleF rout strip
	| returnsScalar = "coalesce(" <> addNullsToSnip strip "json_agg(_postgrest_t.pgrst_scalar)->0" <> ", 'null')"
	| otherwise = "coalesce(" <> addNullsToSnip strip "json_agg(_postgrest_t)->0" <> ", 'null')"
	where
	returnsScalar = maybe False funcReturnsScalar rout
	asJsonF :: Maybe Routine -> Bool -> SQL.Snippet
	asJsonF rout strip
	| returnsSingleComposite = "coalesce(" <> addNullsToSnip strip "json_agg(_postgrest_t)->0" <> ", 'null')"
	| returnsScalar = "coalesce(" <> addNullsToSnip strip "json_agg(_postgrest_t.pgrst_scalar)->0" <> ", 'null')"
	| returnsSetOfScalar = "coalesce(" <> addNullsToSnip strip "json_agg(_postgrest_t.pgrst_scalar)" <> ", '[]')"
	| otherwise = "coalesce(" <> addNullsToSnip strip "json_agg(_postgrest_t)" <> ", '[]')"
	where
	(returnsSingleComposite, returnsScalar, returnsSetOfScalar) = case rout of
	Just r -> (funcReturnsSingleComposite r, funcReturnsScalar r, funcReturnsSetOfScalar r)
	Nothing -> (False, False, False)

I'm not interested in your six-pack, but please consider donating to our Patreon fund instead: https://www.patreon.com/postgrest.

[draos]

Not cool.

Agreed.

I'm not interested in your six-pack, but please consider donating to our Patreon fund instead: https://www.patreon.com/postgrest.

Done.

[draos]

Also, it strikes me as odd that you are citing an RFC, while asking us to return non-standard-compliant JSON instead...

Well, that was BASE64 encoding standard - I wouldn't know about JSON standards... But you are probably right, here, too, and the receiving side will have to do "un-escaping" as a part of extraction of BASE64-endoded data from JSON.

Thanks