PostgreSQL 역할 또는 사용자가 존재하지 않을 때 만드는 방법

PostgreSQL에는 CREATE ROLE IF NOT EXISTS 구문이 없습니다. 로그인할 수 있는 역할을 필요할 때 생성하려면 duplicate_object만 처리하는 PL/pgSQL 블록을 사용하거나, 동시성에 따른 절충을 감수할 수 있을 때 카탈로그를 미리 확인할 수 있습니다. PostgreSQL의 “사용자”는 LOGIN 속성이 있는 역할입니다. 역할은 객체를 소유하고 권한을 부여받을 수도 있습니다.

존재하지 않을 때만 PostgreSQL 역할 만들기

CREATE ROLE로 역할을 정의합니다. 데이터베이스에 연결해야 한다면 LOGIN을 추가하고, 비밀번호 인증을 사용한다면 비밀번호도 지정합니다. 예를 들면 SQL 명령은 CREATE ROLE app_user LOGIN PASSWORD 'replace-with-a-secret';입니다. 안전한 비밀 값을 선택하고, 공유 스크립트나 셸 기록에 운영 환경의 비밀번호를 남기지 마세요.

app_user가 이미 있으면 PostgreSQL은 SQLSTATE 42710(duplicate_object)과 함께 역할이 이미 존재한다고 보고합니다. 일반적인 CREATE ROLE 실행 결과로 문자 그대로 SUCCESSFUL이 반환되지는 않습니다. 사용 가능한 속성은 공식 CREATE ROLE 문서에서 확인할 수 있습니다. WITH는 역할 속성 앞에 선택적으로 쓸 수 있는 구문이며, LOGIN을 대신하지 않습니다.

여기 나오는 SQL 예제를 사용하려면 PostgreSQL 서버가 필요합니다. 구성된 문서 런타임에서는 이 SQL 예제들을 실행하지 않습니다. 적절한 권한이 있는 PostgreSQL 클라이언트에서 실행할 SQL입니다.

아래의 간단한 Python 예제는 검토할 기본 SQL 명령을 출력할 뿐, PostgreSQL에 연결하거나 명령을 실행하지 않습니다. 예제를 실행해 보세요.

sql = "CREATE ROLE app_user LOGIN;"
print(sql)

CREATE ROLE app_user LOGIN;

PL/pgSQL에서 중복 역할 오류를 안전하게 처리하기

다른 세션에서 역할을 이미 만든 경우에도 조용히 계속 진행하려면, 직접 생성 시도에서 중복 객체 조건만 처리합니다.

DO $$ BEGIN CREATE ROLE app_user LOGIN PASSWORD 'replace-with-a-secret'; EXCEPTION WHEN duplicate_object THEN RAISE NOTICE 'Role app_user already exists; leaving it unchanged.'; END; $$;

역할이 이미 있으면 처리기가 알림을 출력하고 블록은 완료됩니다. 역할이 없다면 명령이 역할을 만듭니다. duplicate_object는 SQLSTATE 42710에 해당하는 정확한 조건입니다. 권한 부족이나 잘못된 명령과 같은 오류는 처리되지 않고 호출자에게 전달됩니다. 이렇게 하면 실제 실패가 드러나며, 모든 오류를 잡는 처리기로 관계없는 문제를 숨기지 않습니다.

이 예외 블록은 다른 세션의 동시 생성 시도가 중복 조건에 부딪혔을 때 처리하므로, 별도의 확인 후 생성 사이에 생기는 경쟁 구간을 피합니다. 기존 역할의 속성을 갱신하지는 않습니다. 기존 역할의 비밀번호나 권한을 바꿔야 한다면 적절한 ALTER ROLE 명령으로 명시적으로 변경하세요.

사전 확인이 적절한 경우 기존 역할 확인하기

카탈로그를 확인하면 역할이 이미 있을 때 생성 명령을 건너뛸 수 있습니다. 역할 카탈로그는 pg_catalog.pg_roles이고, rolname은 역할 이름을 나타냅니다. PL/pgSQL에서는 다음처럼 확인할 수 있습니다. DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_roles WHERE rolname = 'app_user') THEN CREATE ROLE app_user LOGIN PASSWORD 'replace-with-a-secret'; END IF; END; $$;.

역할의 존재 여부에 따라 다른 작업을 해야 할 때 유용합니다. 그러나 이 확인만으로는 동시성 문제가 해결되지 않습니다. 두 세션이 모두 일치하는 행이 없다고 확인한 뒤 역할을 만들려고 할 수 있으며, 그중 한 세션은 여전히 SQLSTATE 42710을 받을 수 있습니다. 동시 생성 시도를 허용해야 한다면 위의 중복 전용 처리기를 사용하세요.

역할이 pg_user에 저장되어 있다고 보고 그 테이블을 조회하지 마세요. 문서화된 역할 카탈로그인 pg_catalog.pg_roles를 사용하고 rolname 열을 역할 이름과 비교하세요.

조건부 역할 생성에 psql \gexec 사용하기

스크립트를 psql에서 실행한다면 \gexec로 쿼리가 반환한 SQL 텍스트를 실행할 수 있습니다. psql 프롬프트에서 다음 쿼리를 한 줄로 입력하세요.

SELECT 'CREATE ROLE app_user LOGIN' WHERE NOT EXISTS (SELECT 1 FROM pg_catalog.pg_roles WHERE rolname = 'app_user')

그런 다음 다음 줄에 psql 메타 명령 \gexec를 입력합니다.

역할이 없으면 쿼리가 CREATE ROLE 명령을 반환하고 psql이 이를 실행합니다. 역할이 있으면 쿼리가 행을 반환하지 않으므로 실행할 명령도 없습니다. \gexec는 PostgreSQL SQL이 아니라 psql 메타 명령입니다. 같은 텍스트를 일반 데이터베이스 드라이버나 다른 SQL 클라이언트에 보내면 작동하지 않습니다. 사전 확인은 다른 세션과 경쟁할 수도 있으므로, 동시 생성 가능성이 있다면 중복 전용 예외 처리를 사용하세요. 역할 생성 권한이 있는 데이터베이스 계정으로 실행해야 하며, 운영체제의 root 계정은 필요하지 않습니다.

파괴적이거나 오해를 부르는 방법 피하기

DROP ROLE IF EXISTS app_user를 실행한 뒤 CREATE ROLE app_user를 실행하는 방식을 멱등적인 생성 패턴으로 사용하지 마세요. 먼저 기존 역할을 삭제하므로 소유권, 권한 부여, 종속 객체에 문제가 생길 수 있습니다. 기존 역할의 속성도 보존되지 않습니다.

중복 오류만 억제하려고 WHEN OTHERS를 처리하지 마세요. 이 처리기는 권한, 구문 및 그 밖의 운영 오류를 가릴 수 있습니다. 역할 존재 여부 확인, 일치하는 카탈로그 행 개수 세기, 사용자 지정 함수 사용도 확인과 생성 사이의 경쟁을 없애지 못합니다. 애플리케이션에 재사용 가능한 데이터베이스 함수가 필요한 구체적인 이유가 없다면 작업을 단순하게 유지하고, 동시성이 중요할 때는 중복 조건만 좁게 처리하세요.

역할 확인 및 접근 방식 선택하기

SELECT rolname, rolcanlogin FROM pg_catalog.pg_roles WHERE rolname = 'app_user';로 역할이 있는지 확인하세요. 행이 반환되면 이름과 로그인 가능 여부를 확인할 수 있습니다. 행이 반환되지 않으면 조회한 데이터베이스에 해당 역할이 없습니다. 이 쿼리는 확인 단계일 뿐, 뒤이어 실행할 생성 명령의 경쟁 상태가 없음을 보장하지 않습니다.

작업을 실행하는 방식에 따라 선택하세요.

• 동시 생성 시도를 허용하면서 기존 역할을 그대로 두고 직접 생성하려면 duplicate_object 처리기를 사용하세요.
• 주변 로직에서 존재 여부에 따라 분기해야 하고 동시 생성이 문제가 되지 않거나 중복 오류를 별도로 처리한다면 카탈로그 사전 확인을 사용하세요.
• psql에서 간결한 조건부 명령이 필요하다면 \gexec를 사용하되, 동일한 사전 확인 경쟁이 생길 수 있음을 고려하세요.

생성에 성공하려면 필요한 권한을 가진 데이터베이스 역할이 필요합니다. 기존 역할을 변경해야 한다면 삭제한 뒤 다시 만드는 대신 명시적인 역할 변경 명령을 사용하세요.

결론

PostgreSQL에는 CREATE ROLE IF NOT EXISTS 형식이 없습니다. 역할이 없을 때 안전하게 생성하려면 PL/pgSQL에서 duplicate_object만 처리하세요. 카탈로그 사전 확인이나 psql의 \gexec는 클라이언트와 동시성에 따른 제약이 사용 사례에 맞을 때 편리할 수 있습니다. pg_catalog.pg_roles로 역할을 확인하고, 의도적으로 삭제하거나 변경하려는 경우가 아니라면 기존 역할을 보존하세요.