主页 / dba / 问题 / 270349
Accepted
user183966
Asked: 2020-07-04 07:32:55 +0800 CST

如何防止在 postgres 的当前连接期间从 bytea 隐式转换为文本？

  • 772

在 Postgres中，值在插入到/列时会根据设置bytea自动转换为。texttextvarcharbytea_output

我正在使用一些程序代码，它会自动将程序中的某些值（二进制字符串）转换为 bytea 格式。问题是用户可能不小心尝试将这些值插入到text列中。默认情况下，Postgres 将允许这样做，但这并不总是能顺利进行 - 例如，如果有非 ASCII 字节。我认为用户可能没有意识到奇怪的插入行为是由于他们在调用程序中使用了二进制字符串。因此，如果bytea发生text转换，我希望 Posgres 引发异常。

我知道CREATE CAST，但据我所知，这是一个系统范围的操作。我不想更改其他连接的系统行为。我可以这样做CREATE CAST，DROP CAST但这对我来说似乎很脏，因为它仍然不包含在连接中。

如何进行（隐式）强制转换bytea以text仅在当前连接中引发异常？

sql是自动发出的，所以我可以在每条语句之前添加一个前面的sql语句，没问题。

我对这种行为有点惊讶，因为 Postgres 通常会在严格方面犯错，这是我喜欢的。

这个问题来自我之前的问题：

postgresql concurrency

1 个回答

  1. Best Answer

    例如，如果有非 ASCII 字节，它就不能顺利工作

    你为什么会这样想？分配给列时bytea强制转换为hex（默认）或格式。非 ASCII 字符会自动编码。应该始终“顺利”工作-除非您不想允许它。escapetext

    我可以这样做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？

    • 1

相关问题