Postgre 작성SQL ROLE(사용자)(존재하지 않는 경우)

Postgre 작성SQL ROLE(사용자)(존재하지 않는 경우)

Postgre에서 역할을 생성하기 위해 SQL 스크립트를 작성하는 방법SQL 9.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;

효과가 - 하만그소이없어요용건지없요어▁but▁-이소.IF일반 SQL에서 지원되지 않는 것 같습니다.

PostgreSQL 9.1 데이터베이스, 역할 및 기타 몇 가지를 생성하는 배치 파일을 가지고 있습니다.실행할 SQL 스크립트의 이름을 전달하는 psql.exe를 호출합니다.지금까지 이 모든 스크립트는 일반 SQL이며 가능하다면 PL/pgSQL 등은 피하고 싶습니다.

간단한 스크립트(질문)

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

예를 들어, 없는 것과 달리IF NOT EXISTS(최소 Postgres 14까지)에 대한 절.또한 일반 SQL에서는 동적 DDL 문을 실행할 수 없습니다.

"PL/pgSQL 회피" 요청은 다른 PL을 사용하지 않는 한 불가능합니다.문은 PL/pgSQL을 기본 절차 언어로 사용합니다.

DO [ LANGUAGE lang_name ] code
...
lang_name
코드가 작성된 절차 언어의 이름입니다.경우 은 " 생할경기은값본"입니다.plpgsql.

레이스 조건 없음

위의 간단한 솔루션을 사용하면 역할 조회와 작성 사이의 짧은 시간 내에 레이스 조건을 충족할 수 있습니다.동시 트랜잭션이 그 사이에 역할을 생성하면 결국 예외가 발생합니다.대부분의 워크로드에서 역할 생성은 관리자가 수행하는 드문 작업이기 때문에 이러한 작업은 절대로 발생하지 않습니다.그러나 @blub이 언급한 것처럼 매우 논쟁이 많은 워크로드가 있습니다.
@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$;

훨씬 더 저렴합니다.

• 역할이 이미 존재하는 경우 값비싼 코드 블록에 들어가지 않습니다.

• 만약 우리가 값비싼 코드 블록에 들어간다면, 그 역할은 예상치 못한 경쟁 조건이 발생할 때만 존재합니다.그래서 우리는 실제로 예외를 제기하는 일이 거의 없습니다(그리고 그것을 잡습니다.

할 것을 하는 몇 : 문제를 제기합니다.CREATE ROLE은 한 단점이 . 경주 조건입니다.이것은 한 가지 단점이 있습니다: 경주 상태.와 에 다른 CREATE ROLE 다음 명령을 내립니다.CREATE ROLE치명적인 오류로 인해 분명히 실패합니다.

위의 문제를 해결하기 위해, 이미 언급된 다른 답변들은 다음과 같습니다.PL/pgSQL발행 CREATE ROLE무조건적으로 그리고 나서 그 통화에서 예외를 잡는 것.이러한 해결책에는 단 한 가지 문제가 있습니다.이러한 오류는 해당 역할이 이미 존재한다는 사실에 의해 생성되지 않은 오류를 포함하여 자동으로 삭제됩니다. CREATE ROLE 수 .IF NOT EXISTS역할이 이미 존재하는 경우에만 오류가 발생합니다.

CREATE ROLE지다를 duplicate_object역할이 이미 있을 때 오류가 발생했습니다.또한 예외 처리기는 이 하나의 오류만 탐지해야 합니다.다른 답변에서 언급했듯이 치명적인 오류를 단순 알림으로 전환하는 것이 좋습니다.기타 는 SQL SQL입니다.IF NOT EXISTS는 " " " 를 추가합니다., skipping그들의 메시지에, 그래서 일관성을 위해 여기에도 그것을 추가합니다.

▁▁sql▁for다▁sim의 시뮬레이션을 위한 전체 SQL 입니다.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

또는 역할이 사용할 수 있는 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)

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

자세한 내용은 승인된 답변을 참조하십시오.

여기 있는 다른 답들을 바탕으로, 저는 실행할 수 있는 능력이 필요했습니다.psql한 번은 에 반대하여.sql초기화 작업 집합을 수행할 파일입니다.또한 실행 시 암호를 주입하여 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 파이프라인에 의해 실행됩니다.

---

주의:

• 변수를 할 수 을 찾지 못했습니다.:vPassword) 으로 ) 직적으로접에서DO 함수,즉한 명함수전, 서체라따입니다.FUNCTION논쟁을 통과하다 (@Clodaaldo Neto의 대답 참조)
• @Erwin Brandstetter의 답변은 우리가 왜 다음을 사용해야 하는지 설명합니다.EXECUTE할 수 .CREATE USER직접적으로.
• @팔리의 대답은 그 필요성을 설명합니다.EXCEPTION인종 조건을 방지하기 위해 (그래서)\gexec접근하지 않는 것이 좋습니다).
• 은 이기은다호합니다야출어되로 .SELECT진술. 용사를 합니다.-t/--tuples-only의 psql@villy393의 답변에 지적된 대로 로그 출력을 정리하는 명령입니다.
• 함수는 임시 스키마에 생성되므로 자동으로 삭제됩니다.
• 견적은 올바르게 처리되므로 암호에 특수 문자가 있으면 오류가 발생하거나 더 나쁜 보안 취약성이 발생할 수 없습니다.

은 한, 되지 않았습니다.SELECT * FROM pg_catalog.pg_user@erwin-brand stetter @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$

아마도 다른 예외를 배제하기 위해 더 구체적으로 만들 수 있을 것입니다.

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

셸에 액세스할 수 있는 경우 이 작업을 수행할 수 있습니다.

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

다음의 출력을 구문 분석하여 배치 파일에서 이 작업을 수행할 수 있습니다.

SELECT * FROM pg_user WHERE usename = 'my_user'

그고나달리기서리기달리를 실행합니다.psql.exe역할이 존재하지 않는 경우 다시 한 번 선택합니다.

나는 이것이 필요했습니다.Makefile사용자가 존재할 때 작업을 실패하지 않는 방법:

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

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