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

简单脚本（提问）

以 @a_horse_with_no_name 的回答为基础，并通过 @Gregory's comment 进行了改进：

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 没有 IF NOT EXISTS 子句（至少 Postgres 14）。而且您不能在纯 SQL 中执行动态 DDL 语句。

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

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

没有比赛条件

上述简单的解决方案允许在查找角色和创建角色之间的微小时间范围内出现竞争条件。如果并发事务在两者之间创建角色，我们毕竟会得到一个异常。在大多数工作负载中，这永远不会发生，因为创建角色是管理员很少执行的操作。但也有像 @blubb mentioned 这样有争议的工作负载。
@Pali 添加了一个捕获异常的解决方案。但是带有 EXCEPTION 子句的代码块很昂贵。 The manual:

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

便宜得多：

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

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

---

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

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

但前提是放弃该用户不会造成任何伤害。

---

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

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

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

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

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

---

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

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

---

这是使用 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)

---

正如@erwin-brandstetter 和@a_horse_with_no_name 所建议的那样，我的团队在一台服务器上遇到了多个数据库的情况，具体取决于您连接到哪个数据库，SELECT * FROM pg_catalog.pg_user 未返回相关角色。条件块执行，我们点击 role "my_user" already exists。

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

        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$

排除其他例外情况可能会更具体。

---

与 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

从外壳解决方法

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

有关详细信息，请参阅 accepted answer there。

---

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

---

基于此处的其他答案，我希望能够对 .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 来传递 arg。 （见@Clodoaldo Neto 的回答）

@Erwin Brandstetter 的回答解释了为什么我们必须使用 EXECUTE 而不能直接使用 CREATE USER。

@Pali 的回答解释了需要 EXCEPTION 来防止竞争条件（这就是不推荐使用 \gexec 方法的原因）。

该函数必须在 SELECT 语句中调用。正如@villy393 的回答中所指出的，使用 psql 命令中的 -t/--tuples-only 属性来清理日志输出。

该函数是在临时模式中创建的，因此将自动删除。

引号处理得当，因此密码中的特殊字符不会导致错误或更糟糕的安全漏洞。

---

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

SELECT * FROM pg_user WHERE usename = 'my_user'

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

---

如果您有权访问 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

---

当用户存在时，我需要在 Makefile 中使用它以确保作业不会失败：

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

---