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.
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 (' 00'::bytea::text, 'local bytea_output: hex'); -- explicit cast still works!
INSERT INTO tbl(txt_col)
VALUES (' 00'::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, ' 00'::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, ' 00'::bytea::text, 'local bytea_output: hex'); -- explicit cast still works
SET LOCAL bytea_output = 'escape';
INSERT INTO test(id, txt_col, note)
VALUES (2, ' 00'::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 ' 00'::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, ' 00'::bytea, 'must fail!'); -- assignment cast forbidden!
SELECT public.f_drop_cast_bytea2text();
END;
