如果 PostgreSQL ROLE（用户）不存在，则创建它

Question

如何编写 SQL 脚本来在 PostgreSQL 9.1 中创建 ROLE，但如果它已经存在则不会引发错误？

当前脚本仅具有：

CREATE ROLE my_user LOGIN PASSWORD 'my_password';

如果用户已存在，则此操作将失败。我想要这样的东西：

IF NOT EXISTS (SELECT * FROM pg_user WHERE username = 'my_user')
BEGIN
    CREATE ROLE my_user LOGIN PASSWORD 'my_password';
END;

...但这不起作用 - IF 似乎在普通 SQL 中不受支持。

我有一个批处理文件，用于创建 PostgreSQL 9.1 数据库、角色和其他一些内容。它调用 psql.exe，并传入要运行的 SQL 脚本的名称。到目前为止，所有这些脚本都是纯 SQL，如果可能的话，我想避免使用 PL/pgSQL 等。

Answer 1

简单脚本（提出问题）

基于 @a_horse_with_no_name 的答案构建，并使用 @Gregory 的评论：

DO
$do$
BEGIN
   IF EXISTS (
      SELECT FROM pg_catalog.pg_roles
      WHERE  rolname = 'my_user') THEN

      RAISE NOTICE 'Role "my_user" already exists. Skipping.';
   ELSE
      CREATE ROLE my_user LOGIN PASSWORD 'my_password';
   END IF;
END
$do$;

不同的是，例如，使用 CREATE TABLE CREATE ROLE（至少 Postgres 14）。并且您无法以普通 SQL 执行动态 DDL 语句。

除非使用另一个 PL，否则您要求“避免 PL/pgSQL”是不可能的。 DO 语句 使用 PL/pgSQL 作为默认程序语言：

DO [ LANGUAGE lang_name ] 代码
...
lang_name
编写代码所用的过程语言的名称。如果
省略，默认为plpgsql。

无竞争条件

上述简单的解决方案允许在查找角色和创建角色之间的极短时间范围内出现竞争条件。如果并发事务在两者之间创建角色，我们最终会得到一个异常。在大多数工作负载中，这种情况永远不会发生，因为创建角色是管理员很少执行的操作。但也存在一些极具争议性的工作负载，例如提到的@blubb.
@Pali 添加了一个捕获异常的解决方案。但是带有 EXCEPTION 子句的代码块的成本很高。 手册：

包含 EXCEPTION 子句的块明显更多
进入和退出比没有进入和退出的街区昂贵。因此，不要
无需使用EXCEPTION。

实际上，引发异常（然后捕获它）的成本相对较高。所有这些仅对经常执行它的工作负载很重要 - 这恰好是主要目标受众。优化：

DO
$do$
BEGIN
   IF EXISTS (
      SELECT FROM pg_catalog.pg_roles
      WHERE  rolname = 'my_user') THEN

      RAISE NOTICE 'Role "my_user" already exists. Skipping.';
   ELSE
      BEGIN   -- nested block
         CREATE ROLE my_user LOGIN PASSWORD 'my_password';
      EXCEPTION
         WHEN duplicate_object THEN
            RAISE NOTICE 'Role "my_user" was just created by a concurrent transaction. Skipping.';
      END;
   END IF;
END
$do$;

更便宜：

• 如果角色已经存在，我们永远不会输入昂贵的代码块。

• 如果我们输入昂贵的代码块，则只有在不太可能发生的竞争条件出现时，该角色才会存在。所以我们几乎不会真正引发异常（并捕获它）。

Answer 2

一些答案建议使用模式：检查角色是否不存在，如果不存在则发出 CREATE ROLE 命令。这有一个缺点：竞争条件。如果其他人在检查和发出 CREATE ROLE 命令之间创建了新角色，则 CREATE ROLE 显然会失败并出现致命错误。

为了解决上述问题，更多其他答案已经提到了使用PL/pgSQL，无条件发出CREATE ROLE，然后捕获该调用的异常。这些解决方案只有一个问题。它们会默默地丢弃任何错误，包括那些不是由于角色已经存在而生成的错误。 CREATE ROLE 还可能引发其他错误，并且模拟 IF NOT EXISTS 应仅在角色已存在时静默错误。

当角色已存在时，CREATE ROLE 抛出duplicate_object 错误。异常处理程序应该只捕获这一个错误。正如其他答案提到的，将致命错误转换为简单通知是个好主意。其他 PostgreSQL IF NOT EXISTS 命令将 ,skipping 添加到其消息中，因此为了保持一致性，我也在此处添加它。

以下是用于模拟 CREATE ROLE IF NOT EXISTS 的完整 SQL 代码，具有正确的异常和 sqlstate 传播：

DO $
BEGIN
CREATE ROLE test;
EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
END
$;

测试输出（通过 DO 调用两次，然后直接调用）：

$ sudo -u postgres psql
psql (9.6.12)
Type "help" for help.

postgres=# \set ON_ERROR_STOP on
postgres=# \set VERBOSITY verbose
postgres=# 
postgres=# DO $
postgres$# BEGIN
postgres$# CREATE ROLE test;
postgres$# EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
postgres$# END
postgres$# $;
DO
postgres=# 
postgres=# DO $
postgres$# BEGIN
postgres$# CREATE ROLE test;
postgres$# EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
postgres$# END
postgres$# $;
NOTICE:  42710: role "test" already exists, skipping
LOCATION:  exec_stmt_raise, pl_exec.c:3165
DO
postgres=# 
postgres=# CREATE ROLE test;
ERROR:  42710: role "test" already exists
LOCATION:  CreateRole, user.c:337

Answer 3

或者，如果该角色不是任何可以使用的数据库对象的所有者：

DROP ROLE IF EXISTS my_user;
CREATE ROLE my_user LOGIN PASSWORD 'my_password';

但前提是删除该用户不会造成任何损害。

Answer 4

Bash 替代方案（对于 Bash 脚本）：（

psql -h localhost -U postgres -tc \
  "SELECT 1 FROM pg_user WHERE usename = 'my_user'" \
  | grep -q 1 \
  || psql -h localhost -U postgres \
  -c "CREATE ROLE my_user LOGIN PASSWORD 'my_password';"

不是问题的答案！它仅适用于那些可能有用的人）

Answer 5

这是使用 plpgsql 的通用解决方案：

CREATE OR REPLACE FUNCTION create_role_if_not_exists(rolename NAME) RETURNS TEXT AS
$
BEGIN
    IF NOT EXISTS (SELECT * FROM pg_roles WHERE rolname = rolename) THEN
        EXECUTE format('CREATE ROLE %I', rolename);
        RETURN 'CREATE ROLE';
    ELSE
        RETURN format('ROLE ''%I'' ALREADY EXISTS', rolename);
    END IF;
END;
$
LANGUAGE plpgsql;

用法：

posgres=# SELECT create_role_if_not_exists('ri');
 create_role_if_not_exists 
---------------------------
 CREATE ROLE
(1 row)
posgres=# SELECT create_role_if_not_exists('ri');
 create_role_if_not_exists 
---------------------------
 ROLE 'ri' ALREADY EXISTS
(1 row)

Answer 6

与 Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL? 相同的解决方案应该可以工作 - 发送 CREATE USER ... 到 \gexec。

psql 中的解决方法

SELECT 'CREATE USER my_user'
WHERE NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'my_user')\gexec

来自 shell 的解决方法

echo "SELECT 'CREATE USER my_user' WHERE NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'my_user')\gexec" | psql

请参阅那里接受的答案了解更多详细信息。

Answer 7

根据此处的其他答案，我希望能够针对 .sql 文件执行一次 psql ，以使其执行一组初始化操作。我还希望能够在执行时注入密码以支持 CI/CD 场景。

-- init.sql

CREATE OR REPLACE FUNCTION pg_temp.create_myuser(theUsername text, thePassword text)
RETURNS void AS
$BODY$
DECLARE
  duplicate_object_message text;
BEGIN
  BEGIN
    EXECUTE format(
      'CREATE USER %I WITH PASSWORD %L',
      theUsername,
      thePassword
    );
  EXCEPTION WHEN duplicate_object THEN
    GET STACKED DIAGNOSTICS duplicate_object_message = MESSAGE_TEXT;
    RAISE NOTICE '%, skipping', duplicate_object_message;
  END;
END;
$BODY$
LANGUAGE 'plpgsql';

SELECT pg_temp.create_myuser(:'vUsername', :'vPassword');

使用 psql 调用：

NEW_USERNAME="my_new_user"
NEW_PASSWORD="password with 'special' characters"

psql --no-psqlrc --single-transaction --pset=pager=off \
  --tuples-only \
  --set=ON_ERROR_STOP=1 \
  --set=vUsername="$NEW_USERNAME" \
  --set=vPassword="$NEW_PASSWORD" \
  -f init.sql

这将允许 init.sql 在本地或通过 CI/CD 管道运行。

---

注意：

• 我没有找到直接在 DO 匿名函数中引用文件变量 (:vPassword) 的方法，因此完整的 FUNCTION 为传递参数。 （请参阅@Clodoaldo Neto 的回答）
• @Erwin Brandstetter 的回答解释了为什么我们必须使用 EXECUTE 而不能使用 CREATE直接用户。
• @Pali 的回答解释了 EXCEPTION 防止竞争条件的必要性（这就是为什么 <不推荐使用 code>\gexec 方法）。
• 该函数必须在 SELECT 语句中调用。使用 psql 命令中的 -t/--tuples-only 属性来清理日志输出，如 @villy393 的回答。
• 该函数是在临时模式中创建的，因此它将被自动删除。
• 引用处理得当，因此密码中的特殊字符不会导致错误或更糟糕的安全漏洞。

Answer 8

我的团队遇到了一台服务器上有多个数据库的情况，根据您连接到的数据库，SELECT * FROM pg_catalog.pg_user 没有返回有问题的角色，正如 @erwin-brandstetter 提议的那样和@a_horse_with_no_name。条件块被执行，我们命中角色“my_user”已经存在。

不幸的是，我们不确定确切的条件，但这个解决方案可以解决这个问题：

DO  
$body$
BEGIN
    CREATE ROLE my_user LOGIN PASSWORD 'my_password';
EXCEPTION WHEN others THEN
    RAISE NOTICE 'my_user role exists, not re-creating';
END
$body$

它可能会变得更加具体，以排除其他异常。

Answer 9

当您使用 9.x 时，您可以将其包装到 DO 语句中：

do 
$body$
declare 
  num_users integer;
begin
   SELECT count(*) 
     into num_users
   FROM pg_user
   WHERE usename = 'my_user';

   IF num_users = 0 THEN
      CREATE ROLE my_user LOGIN PASSWORD 'my_password';
   END IF;
end
$body$
;

Answer 10

如果您有权访问 shell，则可以执行此操作。

psql -tc "SELECT 1 FROM pg_user WHERE usename = 'some_use'" | grep -q 1 || psql -c "CREATE USER some_user"

对于那些想要解释的人：

-c = run command in database session, command is given in string
-t = skip header and footer
-q = silent mode for grep 
|| = logical OR, if grep fails to find match run the subsequent command

Answer 11

您可以在批处理文件中通过解析以下输出来完成此操作：

SELECT * FROM pg_user WHERE usename = 'my_user'

如果角色不存在，则再次运行 psql.exe 。

Answer 12

我需要在 Makefile 中使用此命令，以便在用户存在时不会使作业失败：

initdb:
    psql postgres -c "CREATE USER foo CREATEDB PASSWORD 'bar'" || true
    ...

Answer 13

如果有人有兴趣在 Postgres 中使用 Liquibase 创建角色（如果该角色尚不存在）：

<databaseChangeLog
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
            http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">

    <changeSet id="create-role" author="me" dbms="postgresql">
        <preConditions onFail="MARK_RAN">
            <sqlCheck expectedResult="0">SELECT COUNT(*) FROM pg_catalog.pg_roles WHERE rolname = 'role_name'</sqlCheck>
        </preConditions>
        <sql dbms="postgresql">
            CREATE ROLE role_name;
        </sql>
    </changeSet>

</databaseChangeLog>

Answer 14

DO $
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = '<role_name>') THEN
            CREATE ROLE <role_name>;
        END IF;
    END
$

说明：

• DO $$ ... $$;：此块允许您执行 PL/pgSQL 代码块，而无需创建永久的功能。
• IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = '') THEN：此命令检查是否有名为 ROLE >已存在于数据库中。如果不存在，则执行 CREATE ROLE; 语句。