首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >创建PostgreSQL角色(用户)(如果该角色不存在

创建PostgreSQL角色(用户)(如果该角色不存在
EN

Stack Overflow用户
提问于 2011-11-11 17:39:27
回答 8查看 138.1K关注 0票数 161

如何编写一个PostgreSQL脚本来在SQL9.1中创建角色,而不会在角色已经存在的情况下引发错误?

当前脚本只包含:

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;

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

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

EN

回答 8

Stack Overflow用户

发布于 2012-12-14 00:09:14

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

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

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

票数 49
EN

Stack Overflow用户

发布于 2019-05-02 22:20:00

建议使用模式的一些答案:检查角色是否不存在,如果不存在,则发出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
票数 31
EN

Stack Overflow用户

发布于 2014-02-20 20:31:37

下面是一个使用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)
票数 10
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/8092086

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档