如何防止在 postgres 的目前連接期間從 bytea 隱式轉換為文本？

在 Postgres中，值在插入到/列時會根據設置bytea自動轉換為。text``text``varchar``bytea_output

我正在使用一些程式碼，它會自動將程序中的某些值（二進製字元串）轉換為 bytea 格式。問題是使用者可能不小心嘗試將這些值插入到text列中。預設情況下，Postgres 將允許這樣做，但這並不總是能順利進行 - 例如，如果有非 ASCII 字節。我認為使用者可能沒有意識到奇怪的插入行為是由於他們在呼叫程序中使用了二進製字元串。因此，如果bytea發生text轉換，我希望 Posgres 引發異常。

我知道CREATE CAST，但據我所知，這是一個系統範圍的操作。我不想更改其他連接的系統行為。我可以這樣做CREATE CAST，DROP CAST但這對我來說似乎很髒，因為它仍然不包含在連接中。

如何進行（隱式）強制轉換bytea以text僅在目前連接中引發異常？

sql是自動發出的，所以我可以在每條語句之前添加一個前面的sql語句，沒問題。

我對這種行為有點驚訝，因為 Postgres 通常會在嚴格方面犯錯，這是我喜歡的。

這個問題來自我之前的問題：

• 如何防止不同的連接對同一查詢中的 bytea 進行不同的處理？

例如，如果有非 ASCII 字節，它就不能順利工作

你為什麼會這樣想？分配給列時bytea強制轉換為hex（預設）或格式。非 ASCII 字元會自動編碼。應該始終“順利”工作-除非您不想允許它。escape``text

我可以這樣做CREATE CAST，DROP CAST但這對我來說似乎很髒，因為它仍然不包含在連接中。

是的，但是**如果您在其中創建和刪除強制轉換，**它包含在事務中：DDL 命令在 Postgres 中是完全事務性的，因此只有您的會話（在目前事務中）才能看到強制轉換。

如何進行（隱式）強制轉換bytea以text僅在目前連接中引發異常？

…我可以在每個語句之前添加一個前面的SQL語句，這沒問題。

自定義演員的解決方案

目前（所有版本，包括 Postgres 13）從byteato的轉換text在系統目錄中沒有明確的條目pg_cast。它由相應類型的基本輸入/輸出功能提供。這個行為可以被一個顯式的條目所推翻，這個條目是用CREATE CAST.

您需要成為相關類型的所有者，所以這基本上意味著您必須是超級使用者安裝它。

每個數據庫創建一次此轉換函式：

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$;

要允許為非特權角色創建/刪除特殊演員表，請添加包裝函式。以超級使用者（或專用守護程序角色）身份執行此操作：

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);';

現在你可以做你所要求的：

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 - 由於缺少權限，後半部分無法執行。

擴展測試案例

測試表：

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'));

未引發異常：

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;

也不例外：

BEGIN;
SELECT public.f_drop_cast_bytea2text();

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

SELECT public.f_drop_cast_bytea2text();
END;

引發異常：

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;

客戶端問題？

您的評論似乎揭示了一個兔子洞：

輸入值的準備方式必須不同，具體取決於它們是帶編碼的字元串還是二進製字元串。即使我們使用目前活動的編碼。假設二進製字元串將進入 bytea，而帶有編碼的字元串將進入文本。

一旦您錯誤地假設客戶端的數據類型，該解決方案將根本無法工作。text僅當您輸入類型bytea值時才會呼叫強制轉換。即：使用具有顯式數據類型的函式或準備好的語句，或將顯式強制轉換附加到傳遞給INSERT命令的字元串文字，如上所示：'\000'::bytea.

一旦你傳遞了無類型的文字，Postgres 就無法知道它真的應該是bytea. 您如何（錯誤地）準備輸入bytea字元串text，然後仍然（正確地？）添加顯式轉換bytea？

引用自：https://dba.stackexchange.com/questions/270349