[sql] 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 FROM pg_catalog.pg_roles  -- SELECT list can be empty for this
      WHERE  rolname = 'my_user') THEN

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

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

예를 들어 with CREATE TABLE에 대한 IF NOT EXISTS절이 없습니다 CREATE ROLE(최소 12 페이지까지). 그리고 일반 SQL에서는 동적 DDL 문을 실행할 수 없습니다 .

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

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

답변

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

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

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

답변

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

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

그러나이 사용자를 삭제하는 경우에만 해를 끼치 지 않습니다.

답변

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)

답변

패턴 사용에 대한 몇 가지 답변 : 역할이 존재하지 않는지 확인하고 그렇지 않으면 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올바른 예외 및 sqlstate 전파 시뮬레이션을위한 전체 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

답변

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