1

In Postgres bytea values are automatically converted to text when inserted to text/varchar columns, based on the bytea_output setting.

I am working with some program code which automatically converts certain values (binary strings) in the program to a bytea format. The problem is that users might accidentally try to insert these values into a text column. By default, Postgres will allow this, but this cannot always work smoothly - for example if there are non-ASCII bytes. I think users may not realise the strange insert behaviour is due to their use of a binary string in the calling program. Therefore, if a bytea to text conversion happens, I want Posgres to raise an exception.

I am aware of CREATE CAST, but as I understand, this is a system-wide action. I do not want to change the system behaviour for other connections. I could do CREATE CAST followed by DROP CAST but this seems dirty to me as it still not contained within the connection.

How do I make (implicit) casts from bytea to text throw an exception only within the current connection?

The sql is issued automatically, so I can add a preceding SQL statement before every statement, that's no problem.

I was a little surprised by this behaviour because Postgres usually errs on the side of strictness, which I like.

This question follows from my previous question:

1 Answer 1

1

for example if there are non-ASCII bytes it cannot work smoothly

Why would you think so? bytea is coerced to either hex (default) or escape format when assigned to a text column. Non-ASCII characters are encoded automatically. Should work "smoothly" at all times - except that you don't want to allow it.

I could do CREATE CAST followed by DROP CAST but this seems dirty to me as it still not contained within the connection.

True, but it is contained within the transaction if you create and drop the cast within: DDL commands are fully transactional in Postgres, so only your session (within that current transaction) will ever get to see the cast.

How do I make (implicit) casts from bytea to text throw an exception only within the current connection?

... I can add a preceding SQL statement before every statement, that's no problem.

Solution with custom cast

Currently (all versions incl. Postgres 13) the cast from bytea to text has no explicit entry in the system catalog pg_cast. It is provided by basic input/output functions of the respective types. This behavior can be overruled with an explicit entry, created with CREATE CAST.

You need to be the owner of the involved types, so this basically means you have to be superuser install it.

Create this casting function once per database:

CREATE FUNCTION public.text(bytea, int, bool) 
  RETURNS text
  LANGUAGE plpgsql STABLE STRICT PARALLEL SAFE AS
$func$
BEGIN
   IF $3 THEN           -- true if the cast is an explicit cast, false otherwise.
     -- no infinite loop because we do the cast manually
     -- honors current setting for bytea_output, hence function not IMMUTABLE
      RETURN textin(byteaout($1));
   ELSE
      RAISE EXCEPTION 'Assignment cast from bytea to text forbidden by custom cast rules in this database!';
      RETURN textin(byteaout($1));  -- we should *never* get here!
   END IF;
END
$func$;

To allow creating / dropping the special cast to unprivileged roles, add wrapper functions. Do this as superuser (or as dedicated daemon role):

CREATE FUNCTION public.f_create_cast_bytea2text() 
  RETURNS void
  LANGUAGE sql SECURITY DEFINER AS
'CREATE CAST (bytea AS text) WITH FUNCTION public.text(bytea, int, bool) AS ASSIGNMENT;';

CREATE FUNCTION public.f_drop_cast_bytea2text() 
  RETURNS void
  LANGUAGE sql SECURITY DEFINER AS
'DROP CAST IF EXISTS (bytea AS text);';

Now you can do what you asked for:

BEGIN;
SELECT public.f_create_cast_bytea2text();  -- optionally activate your casting rule

INSERT INTO tbl(txt_col)
VALUES ('\000'::bytea::text, 'local bytea_output: hex');    -- explicit cast still works!

INSERT INTO tbl(txt_col)
VALUES ('\000'::bytea); -- but assignment cast forbidden! -> ERROR

SELECT public.f_drop_cast_bytea2text();  -- deactivate your casting rule
END;

db<>fiddle here -- second half does not execute due to missing privileges.

Extended test case

Test table:

CREATE TABLE test(id int, txt_col text, note text);
INSERT INTO test(id, txt_col, note) VALUES
  (-1, 'foo', 'plain text input')
, ( 0, '\000'::bytea, 'default bytea_output: ' || current_setting('bytea_output'));

No exception raised:

BEGIN;
SELECT public.f_create_cast_bytea2text();

SET LOCAL bytea_output = 'hex';
INSERT INTO test(id, txt_col, note)
VALUES (1, '\000'::bytea::text, 'local bytea_output: hex');    -- explicit cast still works

SET LOCAL bytea_output = 'escape';
INSERT INTO test(id, txt_col, note)
VALUES (2, '\000'::bytea::text, 'local bytea_output: escape'); -- explicit cast still works

SELECT public.f_drop_cast_bytea2text();
END;

Also no exception:

BEGIN;
SELECT public.f_drop_cast_bytea2text();

SELECT '\000'::bytea || text 'foo'; -- implicit cast still works

SELECT public.f_drop_cast_bytea2text();
END;

Exception raised:

BEGIN;
SELECT public.f_create_cast_bytea2text();

INSERT INTO test(id, txt_col, note)
VALUES (3, '\000'::bytea, 'must fail!'); -- assignment cast forbidden!

SELECT public.f_drop_cast_bytea2text();
END;

Client side problem?

Your comment seems to reveal a rabbit hole:

Input values must be prepared differently depending on whether they are strings with encoding or binary strings. Even if we make use of the currently active encoding. Assuming that binary strings will go into bytea and strings with encoding will go into text.

The solution cannot work at all once you incorrectly assume data type text on the client side. The cast is only invoked if you hand in typed bytea values. I.e.: use a function or prepared statement with explicit data type or append an explicit cast to string literals handed to an INSERT command like demonstrated above: '\000'::bytea.

Once you pass untyped literals, Postgres has no way to know that it should really be bytea. And how can you (incorrectly) prepare bytea strings for text input and then still (correctly?) add an explicit cast to bytea?

6
  • I'll add more explanation later. Out of time now. Jul 4, 2020 at 5:51
  • Thank you, superbly complete answer. I would love to see the extra explanation, but even if that isn't added this answer already exceeds my expectations. I just have 1 remaining question - does this function perform a global change to the casting rules or is it scoped to the connection? I appreciate if it is global it is only in place during the query execution, so the chance of a bad interaction is small.
    – user183966
    Jul 4, 2020 at 10:03
  • Regarding the question "Why would you think so?", in case it is interesting, this is the reason. Input values must be prepared differently depending on whether they are strings with encoding or binary strings. Even if we make use of the currently active encoding. Assuming that binary strings will go into bytea and strings with encoding will go into text.
    – user183966
    Jul 4, 2020 at 10:20
  • For hex representation it's obvious. For "escape" representation it is because bytea backslash escaping is not compatible with text backslash escaping: E'\\' must go to E'\\\\'. So if the user submits a binary string with one backslash (in the current encoding), the application must assume it is for bytea and format as E'\\\\' (or use hex). If the user accidentally puts this into a text column it becomes a double backslash. Now when it is read it is the double backslash string, which is not the same bytes as we started. But if I missed something I would love to know.
    – user183966
    Jul 4, 2020 at 10:26
  • 1
    @user183966: The scope is limited to the current transaction because DDL commands are transactional, too. I added a bit above. Also, my cast only applies to assignment casts, explicit and "implicit" casts keep working, even in the same transaction with the special cast in place. Jul 6, 2020 at 10:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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