如果 PostgreSQLROLE (用户)不存在，则创建它

如何编写一个 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 等。

169569 次浏览
小开

您可以在批处理文件中通过解析以下内容的输出来完成:

SELECT * FROM pg_user WHERE usename = 'my_user'

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

小开

当你在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$
;
小开
最佳答案

简单的脚本(问题)

以 @ 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没有 IF NOT EXISTS子句(至少到 Postgres 14)。而且 不能用普通 SQL 执行动态 DDL 语句。

你要求“避免 PL/pgSQL”是不可能的，除非使用另一个 PL。 DO语句使用 PL/pgSQL 作为默认工作语言:

DO [ LANGUAGE lang_name ] code
...

代码所在工作语言的名称 如果省略，则默认值为 plpgsql。

没有比赛条件

上面的简单解决方案允许在查找角色和创建角色之间的很短时间内出现竞态条件。如果一个并发事务在两者之间创建角色，我们最终会得到一个异常。在大多数工作负载中，这种情况不会发生，因为创建角色是由管理员执行的罕见操作。但是也有像 @ blubb said这样高度有争议的工作负载。
@ 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$;

便宜多了:

  • 如果角色已经存在，我们就不会进入代价高昂的代码块。

  • 如果我们进入代价高昂的代码块，那么角色只有在不太可能的竞态条件发生时才会存在。因此，我们几乎从来没有真正提出一个异常(并抓住它)。

小开

或者如果角色不是可以使用的任何 db 对象的所有者:

DROP ROLE IF EXISTS my_user;
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)
小开

我的团队遇到了一个情况，在一台服务器上有多个数据库，根据您连接到的数据库，有问题的 ROLE 没有由 SELECT * FROM pg_catalog.pg_user返回，正如@erwin-brandstetter 和@a _ horse _ with _ no _ name 所建议的那样。执行条件块，然后我们点击 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$

也许可以更具体地排除其他例外情况。

小开

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

(不是这个问题的答案! 它只对那些可能有用的人有用)

小开

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

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

当角色已经存在时抛出 duplicate_object错误。异常处理程序应该只捕获这一个错误。正如其他答案所提到的，将致命错误转换为简单通知是一个好主意。其他 PostgreSQLIF 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
小开

同样的解决方案，为 模拟创建数据库如果 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

有关详细信息，请参阅 接受这个答案。

小开

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

基于这里的其他答案，我希望能够对一个 .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语句中调用。使用 psql命令中的 -t/--tuples-only属性清理日志输出，如 @ vily393的回答中所指出的。
  • 该函数是在临时架构中创建的，因此它将被自动删除。
  • 引号处理正确，因此密码中没有特殊字符可能导致错误或更糟的安全漏洞。
小开

我需要在 Makefile中使用这个函数，以便在用户存在时不会使工作失败:

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

提交答案