PostgreSQL ROLE (사용자)가없는 경우 생성

PostgreSQL ROLE (사용자)가없는 경우 생성

---

PostgreSQL 9.1에서 ROLE을 생성하는 SQL 스크립트를 작성하지만 이미 존재하는 경우 오류를 발생시키지 않고 어떻게합니까?

현재 스크립트는 다음과 같습니다.

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 등을 피하고 싶습니다.

---

생각했던 것과 비슷한 방식으로 단순화하십시오.

DO
$do$
BEGIN
   IF NOT EXISTS (
      SELECT                       -- SELECT list can stay empty for this
      FROM   pg_catalog.pg_roles
      WHERE  rolname = 'my_user') THEN

      CREATE ROLE my_user LOGIN PASSWORD 'my_password';
   END IF;
END
$do$;

( @a_horse_with_no_name 의 답변을 기반으로하고 @Gregory 의 의견 이후 개선되었습니다 .)

예를 들어와 달리 (아직)에 대한 절이 CREATE TABLE없습니다 . 그리고 일반 SQL에서는 동적 DDL 문을 실행할 수 없습니다 .IF NOT EXISTSCREATE ROLE

"PL / pgSQL 방지"요청은 다른 PL을 사용하는 경우를 제외하고는 불가능합니다. 이 DO명령문 은 plpgsql을 기본 절차 언어로 사용합니다. 구문은 명시 적 선언을 생략 할 수 있습니다.

DO [ LANGUAGE lang_name ] code
... 코드가 작성되는 절차 언어의 이름입니다. 생략하면 기본값은 입니다.
lang_name
plpgsql

---

또는 역할이 db 개체의 소유자가 아닌 경우 다음을 사용할 수 있습니다.

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

그러나이 사용자를 떨어 뜨리는 경우에만 해를 끼치 지 않습니다.

---

연속 통합 환경에서 일반적으로 사용 되는 것처럼 두 개의 스크립트가 동일한 Postgres 클러스터 (DB 서버)에서 동시에 실행되는 경우 허용되는 답변은 경합 상태가됩니다 .

일반적으로 역할을 생성하고 생성 할 때 문제를 정상적으로 처리하는 것이 더 안전합니다.

DO $$
BEGIN
  CREATE ROLE my_role WITH NOLOGIN;
  EXCEPTION WHEN OTHERS THEN
  RAISE NOTICE 'not creating role my_role -- it already exists';
END
$$;

---

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)

---

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

---

My team was hitting a situation with multiple databases on one server, depending on which database you connected to, the ROLE in question was not returned by SELECT * FROM pg_catalog.pg_user, as proposed by @erwin-brandstetter and @a_horse_with_no_name. The conditional block executed, and we hit role "my_user" already exists.

Unfortunately we aren't sure of exact conditions, but this solution works around the problem:

        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$

It could probably be made more specific to rule out other exceptions.

---

You can do it in your batch file by parsing the output of:

SELECT * FROM pg_user WHERE usename = 'my_user'

and then running psql.exe once again if the role does not exist.

---

Some answers suggested to use pattern: check if role does not exist and if not then issue CREATE ROLE command. This has one disadvantage: race condition. If somebody else creates a new role between check and issuing CREATE ROLE command then CREATE ROLE obviously fails with fatal error.

To solve above problem, more other answers already mentioned usage of PL/pgSQL, issuing CREATE ROLE unconditionally and then catching exceptions from that call. There is just one problem with these solutions. They silently drop any errors, including those which are not generated by fact that role already exists. CREATE ROLE can throw also other errors and simulation IF NOT EXISTS should silence only error when role already exists.

CREATE ROLE throw duplicate_object error when role already exists. And exception handler should catch only this one error. As other answers mentioned it is a good idea to convert fatal error to simple notice. Other PostgreSQL IF NOT EXISTS commands adds , skipping into their message, so for consistency I'm adding it here too.

Here is full SQL code for simulation of CREATE ROLE IF NOT EXISTS with correct exception and sqlstate propagation:

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

Test output (called two times via DO and then directly):

$ 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

---

The same solution as for Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL? should work - send a CREATE USER … to \gexec.

Workaround from within psql

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

Workaround from the shell

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

See accepted answer there for more details.

참고URL : https://stackoverflow.com/questions/8092086/create-postgresql-role-user-if-it-doesnt-exist