PostgreSQL에 존재하지 않는 경우 역할 또는 사용자 생성

1. PostgreSQL에 존재하지 않는 경우 CREATE ROLE 방법을 사용하여 역할 생성
2. 결론

오늘은 PostgreSQL에 존재하지 않는 역할을 생성하는 방법을 알아보겠습니다.

역할은 우리가 정의한 데이터베이스의 개체를 소유하는 엔터티이며 다른 권한을 갖도록 만들 수 있습니다. 필요한 시스템에 따라 PostgreSQL 데이터베이스에 여러 사용자 또는 역할이 필요할 수 있습니다.

PostgreSQL에 존재하지 않는 경우 CREATE ROLE 방법을 사용하여 역할 생성

PostgreSQL은 새로운 역할을 정의하는 방법을 제공합니다.

통사론:

CREATE ROLE name [ [ WITH ] option [ ... ] ]

where option can be:

        SUPERUSER | NOSUPERUSER ...

구문의 다른 옵션은 생략되었으며 여기에서 볼 수 있습니다.

계속해서 데이터베이스에 로그인하기 위한 비밀번호로 사용자 MARK를 생성하겠습니다.

암호:

create role MARK LOGIN PASSWORD 'mark123';

사용자가 아직 존재하지 않는 경우 위 명령을 실행하면 SUCCESSFUL이 반환됩니다.

이를 통해 MARK 역할이 암호를 가질 수 있습니다. LOGIN 대신 WITH로 이동하여 이 사용자의 암호를 정의할 수도 있습니다.

사용자가 이미 존재하는 경우 아래 오류가 표시됩니다.

출력:

ERROR:  role "mark" already exists
SQL state: 42710

이를 해결하기 위해 아래에 정의된 몇 가지 방법을 사용할 수 있습니다.

PL/PgSQL에서 EXCEPTION과 함께 CREATE ROLE 사용

PL/PgSQL은 단순한 SQL 문이 아닌 FOR 루프 및 제어 구조를 갖는 데 사용됩니다. 우리는 이것을 유리하게 사용할 수 있으며 전체 스크립트가 소멸되는 대신 오류를 피하기 위해 중복에 EXCEPTION을 발행할 수 있습니다.

암호:

 DO $$
BEGIN
CREATE ROLE MARK;
EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, moving to next statement', SQLERRM USING ERRCODE = SQLSTATE;
END
$$;

중복 역할이 발견되면 위의 쿼리는 간단한 명령문(아래 표시)을 발행한 후 다음 쿼리로 이동합니다.

출력:

NOTICE:  role "mark" already exists, moving to the next statement
DO

쿼리가 168msec 내에 성공적으로 반환되었습니다.

쿼리 작동 방식을 이해하려면 해당 구조를 살펴보십시오. PostgreSQL 설명서의 ERRORS 및 MESSAGES에서 찾을 수 있는 EXCEPTION 절의 사용을 알 수 있습니다.

그런 다음 CASE 절을 사용하여 원하는 조건이 충족되면 달성하려는 결과를 지정할 수 있습니다.

CASE 문의 구문:

CASE WHEN condition THEN result
    [WHEN ...]
    [ELSE result]
END

우리의 진술에서 우리는 duplicate_object에서 EXCEPTION이 잡히면 NOTICE를 발생시켰습니다. 중복이 발견되는 경우에만 오류가 발생한다는 의미입니다.

다른 오류나 오류는 건너뛰고 NOTICE가 발생하지 않습니다. SQLERRM은 SQL ERROR MESSAGE의 약자이며 반환 SQLSTATE와 함께 오류 메시지를 설명하는 문자열을 포함합니다.

DO는 트랜잭션 내에서 대부분 절차인 코드 블록을 시작합니다.

이 솔루션은 RACE 상태를 방지하는 데 도움이 됩니다. 중복 예외를 확인하고 호출하는 사이에 지연이 없기 때문에 완벽하게 작동합니다.

또한 다른 언어나 PL/SQL 스크립트에서 이것을 호출하는 경우 여전히 올바른 반환 SQLSTATE를 발행합니다. 아래 제공된 솔루션과 달리 다른 상황을 고려하면 완벽하게 작동합니다.

IF EXISTS를 사용하여 중복 위반 조건 정의

중복 위반이 발견되면 IF EXISTS 조건을 RAISE NOTICE로 설정하여 절차를 생성할 수 있습니다. 이는 위에 제공된 솔루션의 수정일 수 있습니다.

암호:

DO
$do$
BEGIN
    IF EXISTS (SELECT FROM pg_user
        WHERE  usename = 'mark'
        ) THEN

        RAISE NOTICE 'SKIP ROLE MAKER!';
    ELSE
        CREATE ROLE MARK LOGIN PASSWORD 'mark123';
    END IF;
END
$do$;

출력:

NOTICE:  SKIP ROLE MAKER!
DO

쿼리가 109msec 내에 성공적으로 반환되었습니다.

PG_USER 테이블 내에서 역할을 생성했기 때문에 이 테이블의 SELECT 문에서 IF EXISTS를 호출합니다. 그러나 다른 경우에는 카탈로그 아래 PG_ROLES에 정의된 역할이 있을 수 있으며 코드를 다음과 같이 변경해야 할 수 있습니다.

암호:

DO
$do$
BEGIN
    IF EXISTS (SELECT FROM pg_catalog.pg_roles
        WHERE  rolname = 'mark'
        ) THEN

        RAISE NOTICE 'SKIP ROLE MAKER!';
    ELSE
        CREATE ROLE MARK LOGIN PASSWORD 'mark123';
    END IF;
END
$do$;

그러나 역할이 PG_ROLES 테이블 아래에 정의되지 않은 경우 오류가 반환될 수 있습니다. 따라서 역할을 정의하는 위치를 확인한 다음 조건에 맞게 코드를 수정하십시오.

‘IF EXISTS’ 검사를 호출하고 쿼리가 ‘TRUE’를 반환하면 알림을 발생시키기를 기다리면 ‘RACE’ 조건이 이 솔루션에서 문제가 되는 경향이 있습니다. 다른 거래가 이미 여기에 와서 통지를 제기했을 것이며, 이로 인해 이 작업이 무효화될 수 있습니다.

RACE 조건을 방지하기 위해 위에 제공된 솔루션을 사용하고 여기에서 사용한 쿼리에 추가할 수 있습니다. 위의 솔루션은 코드 블록 내부에 추가된 EXCEPTION 절로 인해 비용이 많이 드는 경향이 있습니다.

EXCEPTION 절이 포함된 이 코드 블록을 입력하고 종료하는 데 어려움이 있기 때문에 이런 일이 발생하는 경향이 있습니다. 따라서 이제 간단한 조정을 할 수 있습니다.

확인하기 위해 항상 이 코드 블록을 입력하는 대신 RACE CONDITION이 있는 경우에만 쿼리가 이 코드 블록을 사용하도록 할 수 있습니다. 그렇지 않으면 건너뛰고 통과하십시오. 조정은 다음과 같이 진행됩니다.

암호:

DO
$do$
BEGIN
    IF EXISTS (SELECT FROM pg_user
        WHERE  usename = 'mark'
        ) THEN

        RAISE NOTICE 'SKIP ROLE MAKER!';
    ELSE
        BEGIN
CREATE ROLE mark;
EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, moving to next statement', SQLERRM USING ERRCODE = SQLSTATE;
END;
END IF;
END
$do$;

따라서 중복이 없는 경우 ELSE 블록이 입력되며 RACE CONDITION을 피할 수 있습니다. 쿼리가 다른 테이블에서 역할을 확인하도록 하려면 또 다른 약간의 수정을 수행할 수 있습니다.

암호:

DO
$do$
BEGIN
    IF EXISTS (SELECT FROM pg_user
        WHERE  usename = 'mark'
        ) THEN

        RAISE NOTICE 'SKIP ROLE MAKER!';
    ELSE
        BEGIN
        IF EXISTS (SELECT FROM pg_catalog.pg_roles
        WHERE  rolname = 'mark'
        ) THEN

        RAISE NOTICE 'SKIP ROLE MAKER!';
        ELSE
        BEGIN
    CREATE ROLE mark;
    EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, moving to next statement', SQLERRM USING ERRCODE = SQLSTATE;
    END;
        END IF;
    END;
    END IF;
END;
$do$;

위의 쿼리는 역할의 존재를 다시 확인한 다음 테이블 중 하나에 중복 위반이 있는 경우 알림을 발생시킵니다.

중복 위반을 방지하기 위해 CREATE와 결합된 DROP IF 사용

암호:

DROP ROLE IF EXISTS mark;
CREATE ROLE mark LOGIN PASSWORD 'mark123';

이 코드는 역할 생성의 또 다른 간단한 변형입니다. 두 개의 쿼리를 그룹으로 실행하면 역할이 존재하는 경우 역할을 DROP하고 발생 가능한 모든 항목을 제거한 다음 중복 위반 없이 역할을 CREATE하는 경향이 있습니다.

그러나 이 솔루션은 새 역할을 추가할 때 기존 역할을 삭제하지 않으려는 경우에 가장 잘 작동합니다. CREATE ROLE 호출이 이번에 역할을 CREATE하려는 것을 의미하는 경우 위의 방법도 사용할 수 있습니다.

CREATE ROLE 쿼리를 GEXEC와 함께 사용하여 중복 위반 방지

복제 없이 역할을 생성하는 또 다른 효율적인 방법은 PostgreSQL 데이터베이스 세션에 루트/수퍼유저로 연결된 후 PSQL 셸 내에서 다음 쿼리를 사용하는 것입니다.

암호:

 SELECT 'CREATE USER mark' where not exists (select from pg_user where usename = 'mark')\gexec

역할이 이미 존재하는 경우 아무 것도 반환되지 않습니다. 그러나 그렇지 않은 경우 아래 결과가 반환됩니다.

출력:

CREATE ROLE

이 출력은 역할 생성이 성공했음을 의미합니다.

PSQL의 \GEXEC는 그룹으로 작성된 명령문을 실행하도록 되어 있습니다. 따라서 위의 명령문 끝에 GEXEC를 추가하면 필요에 따라 실행됩니다.

다른 예외 오류와 함께 CREATE ROLE 사용

암호:

    DO
        $body$
        BEGIN
            CREATE ROLE mark LOGIN PASSWORD 'mark123';
        EXCEPTION WHEN others THEN
            RAISE NOTICE 'exception others not detected';
        END
        $body$

위의 쿼리를 실행하면 모든 EXCEPTION 오류에 대해 작동합니다. 첫 번째 솔루션은 이 쿼리를 수정하여 DUPLICATION ERROR에 대해서만 작동하도록 만드는 경향이 있습니다.

사용자 지정 함수에서 CREATE ROLE에 대한 DUPLICATE EXCEPTION을 래핑합니다.

서버 데이터베이스에 액세스하는 사용자가 사용할 수 있는 간단한 기능을 만들 수 있습니다.

암호:

        CREATE OR REPLACE FUNCTION create_role_ifn(rname NAME) RETURNS TEXT AS
$$
BEGIN
    IF NOT EXISTS (SELECT * FROM pg_user WHERE usename = rname) THEN
        EXECUTE format('CREATE ROLE %I', rname);
        RETURN 'ROLE CREATED SUCCESSFULLY';
    ELSE
        RETURN format('ROLE ''%I'' EXISTING ALREADY', rname);
    END IF;
END;
$$
LANGUAGE plpgsql;

이 코드는 역할 이름을 RNAME 매개변수로 사용하고 역할이 존재하지 않는 경우 EXECUTE 문에서 CREATE ROLE을 호출합니다. 그렇다면 ELSE 블록에 지정된 메시지를 반환합니다.

출력:

PG_CATALOG PG_ROLES 테이블에 관계가 있으면 쿼리를 수정할 수 있습니다.

암호:

        CREATE OR REPLACE FUNCTION create_role_ifn(rname NAME) RETURNS TEXT AS
$$
BEGIN
    IF NOT EXISTS (SELECT * FROM pg_catalog.pg_roles WHERE rolname = rname) THEN
        EXECUTE format('CREATE ROLE %I', rname);
        RETURN 'ROLE CREATED SUCCESSFULLY';
    ELSE
        RETURN format('ROLE ''%I'' EXISTING ALREADY', rname);
    END IF;
END;
$$
LANGUAGE plpgsql;

IF EXISTS가 아닌 발생을 찾기 위해 SQL 문 수정

이전 버전의 PostgreSQL의 경우 역할이 테이블에 나타나는 횟수를 확인한 다음 문제가 발생하는 경우 IF EXISTS 대신 CREATE ROLE 문을 실행할 수 있습니다.

암호:

do
$body$
declare
    occurences integer;
begin
    SELECT count(*)
        into occurences
    FROM pg_user
    WHERE usename = 'mark';

    IF occurences = 0 THEN
        CREATE ROLE mark LOGIN PASSWORD 'mark123';
    ELSE
        BEGIN
        raise exception 'ALREADY EXISTS!';
    END;
    END IF;
end
$body$

쿼리를 실행할 때마다 발생 횟수는 1을 초과하지 않습니다. 역할이 발견되면 ELSE 문이 실행됩니다.

출력:

ERROR:  ALREADY EXISTS!
CONTEXT:  PL/pgSQL function inline_code_block line 14 at RAISE
SQL state: P0001

PG_USER 테이블에 역할이 없는 조건의 경우 PG_CATALOG 테이블로 이동하여 쿼리를 실행할 수 있습니다.

암호:

do
$body$
declare
    occurrences integer;
begin
    SELECT count(*)
        into occurences
    FROM pg_catalog.pg_roles
    WHERE rolname = 'mark';

    IF occurences = 0 THEN
        CREATE ROLE mark LOGIN PASSWORD 'mark123';
    ELSE
        BEGIN
        raise exception 'ALREADY EXISTS!';
    END;
    END IF;
end
$body$

결론

PostgreSQL에서 역할 생성 시 중복 오류에 접근하는 다양한 방법을 살펴보았습니다. 그러나 다른 솔루션은 다른 상황에서 작동합니다.

여러 클라이언트가 서버의 데이터베이스에 액세스한 다음 쿼리를 실행하는 경우 피하고 싶은 RACE 조건이 있을 수 있습니다. 이러한 조건에서 단일 자세로 쿼리를 실행하는 트랜잭션 또는 코드 블록을 사용하는 것이 좋습니다.

다른 많은 인스턴스에서 다른 버전의 PostgreSQL에 문제가 있을 수 있습니다. 중복 예외 오류를 해결하는 다양한 방법을 제공했습니다.

위에서 가장 잘 선택된 솔루션에서 함수를 만드는 것이 권장되는 방법입니다. 쿼리를 단순화하는 데 도움이 되며 다른 클라이언트에서 간단하게 실행할 수 있습니다.