PostgreSQL PL/pgSQL函数变量被识别为字面量导致执行报错问题咨询

问题场景 #

用户编写了如下PL/pgSQL函数，意图创建不存在的用户并授予对应数据库权限：

CREATE FUNCTION create_user_if_not_exists(_name text, _pass text)
  RETURNS void AS
$func$
DECLARE
   _dbname TEXT := concat(_name, '_db');
BEGIN
    IF EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = _name) THEN
       RAISE NOTICE 'Role already exists. Skipping.';
    ELSE
     CREATE USER _name WITH encrypted password '_pass';
     GRANT ALL PRIVILEGES ON DATABASE _dbname TO _name;
    END IF;
END
$func$ LANGUAGE plpgsql;

执行调用命令：

psql --username "$POSTGRES_USER" -c "SELECT create_user_if_not_exists('Foo', 'BAR');"

得到错误：

ERROR: database "_dbname" does not exist

注释掉GRANT语句后，发现创建的用户名为_name而非传入的Foo，变量未被正确解析。

问题根源 #

这是PL/pgSQL中静态SQL的限制导致的：

• CREATE USER、GRANT这类DDL语句属于静态SQL范畴，PL/pgSQL不会自动将语句中的标识符（如用户名_name、数据库名_dbname）或字符串常量（如密码_pass）替换为变量值，而是直接将其当作字面量处理。
• 只有DML语句（如SELECT、INSERT、UPDATE）中的变量才会被自动解析，DDL语句不支持这种直接的变量引用。

解决方法 #

必须使用动态SQL（通过EXECUTE语句）来构建并执行这些DDL操作，同时用format()函数安全处理标识符和字符串，避免SQL注入风险：

CREATE FUNCTION create_user_if_not_exists(_name text, _pass text)
  RETURNS void AS
$func$
DECLARE
   _dbname TEXT := concat(_name, '_db');
BEGIN
    IF EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = _name) THEN
       RAISE NOTICE 'Role already exists. Skipping.';
    ELSE
      -- 动态创建用户，%I处理标识符，%L处理密码字符串
      EXECUTE format('CREATE USER %I WITH encrypted password %L', _name, _pass);
      -- 动态授予权限
      EXECUTE format('GRANT ALL PRIVILEGES ON DATABASE %I TO %I', _dbname, _name);
    END IF;
END
$func$ LANGUAGE plpgsql;

• %I：用于格式化SQL标识符（如用户名、数据库名），会自动处理特殊字符并添加引号，符合PostgreSQL的标识符规则。
• %L：用于格式化字符串常量（如密码），会自动添加单引号并转义内部的特殊字符，避免SQL注入。

内容的提问来源于stack exchange，提问作者zar3bski