Utwórz rolę PostgreSQL (użytkownika), jeśli nie istnieje

Jak napisać skrypt SQL w celu utworzenia roli w PostgreSQL 9.1, ale bez zgłaszania błędu, jeśli już istnieje?

Obecny skrypt ma po prostu:

CREATE ROLE my_user LOGIN PASSWORD 'my_password';

Nie powiedzie się, jeśli użytkownik już istnieje. Chciałbym coś takiego:

IF NOT EXISTS (SELECT * FROM pg_user WHERE username = 'my_user')
BEGIN
    CREATE ROLE my_user LOGIN PASSWORD 'my_password';
END;

... ale to nie działa - IFnie wydaje się być obsługiwane w zwykłym języku SQL.

Mam plik wsadowy, który tworzy bazę danych PostgreSQL 9.1, rolę i kilka innych rzeczy. Wywołuje psql.exe, przekazując nazwę skryptu SQL do uruchomienia. Jak dotąd wszystkie te skrypty to zwykły SQL i chciałbym unikać PL / pgSQL i tym podobnych, jeśli to możliwe.

Uprość w podobny sposób, jak miałeś na myśli:

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

(Opierając się na odpowiedzi @a_horse_with_no_name i ulepszonym komentarzem @ Gregory .)

W przeciwieństwie na przykład do CREATE TABLEbraku IF NOT EXISTSklauzuli for CREATE ROLE(do co najmniej str. 12). Nie można też wykonywać dynamicznych instrukcji DDL w zwykłym języku SQL.

Twoja prośba o „uniknięcie PL / pgSQL” jest niemożliwa z wyjątkiem użycia innego PL. Do DOrachunku zastosowania plpgsql jako domyślny język proceduralny. Składnia pozwala na pominięcie jawnej deklaracji:

DO [ LANGUAGE lang_name ] code
... Nazwa języka proceduralnego, w którym zapisywany jest kod. Jeśli zostanie pominięty, wartością domyślną jest .
lang_name
plpgsql

Zaakceptowana odpowiedź cierpi z powodu sytuacji wyścigu, jeśli dwa takie skrypty są wykonywane jednocześnie w tym samym klastrze Postgres (serwerze DB), co jest powszechne w środowiskach ciągłej integracji .

Generalnie bezpieczniej jest spróbować utworzyć rolę i z wdziękiem rozwiązywać problemy podczas jej tworzenia:

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

Lub jeśli rola nie jest właścicielem żadnych obiektów bazy danych, których można użyć:

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

Ale tylko wtedy, gdy upuszczenie tego użytkownika nie spowoduje żadnej szkody.

Alternatywa Bash (dla skryptów 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';"

(nie jest odpowiedzią na pytanie! jest tylko dla tych, którzy mogą się przydać)

Oto ogólne rozwiązanie wykorzystujące 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;

Stosowanie:

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)

Niektóre odpowiedzi sugerowały użycie wzorca: sprawdź, czy rola nie istnieje, a jeśli nie, wydaj CREATE ROLEpolecenie. Ma to jedną wadę: stan wyścigu. Jeśli ktoś inny utworzy nową rolę między sprawdzeniem a wydaniem CREATE ROLEpoleceniaCREATE ROLE oczywiście nie powiedzie się z błędem krytycznym.

Aby rozwiązać powyższy problem, więcej innych odpowiedzi wspominało już o użyciu PL/pgSQL, wydawaniu CREATE ROLEbezwarunkowym, a następnie wyłapywaniu wyjątków od tego połączenia. Z tymi rozwiązaniami jest tylko jeden problem. Po cichu odrzucają wszelkie błędy, w tym te, które nie są generowane przez fakt, że rola już istnieje. CREATE ROLEmoże generować również inne błędy, a symulacja IF NOT EXISTSpowinna wyciszać błędy tylko wtedy, gdy rola już istnieje.

CREATE ROLEzgłosić duplicate_objectbłąd, gdy rola już istnieje. Program obsługi wyjątków powinien wychwycić tylko ten jeden błąd. Jak wspomniały inne odpowiedzi, dobrym pomysłem jest przekształcenie błędu krytycznego w proste powiadomienie. Inne IF NOT EXISTSpolecenia PostgreSQL są dodawane , skippingdo ich wiadomości, więc dla spójności dodam je tutaj.

Oto pełny kod SQL do symulacji CREATE ROLE IF NOT EXISTSz poprawnym wyjątkiem i propagacją stanu sql :

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

Wyjście testowe (wywoływane dwukrotnie przez DO, a następnie bezpośrednio):

$ 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

Ponieważ korzystasz z 9.x, możesz zawrzeć to w oświadczeniu 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$
;

Mój zespół miał problem z wieloma bazami danych na jednym serwerze, w zależności od tego, z którą bazą danych się SELECT * FROM pg_catalog.pg_userłączyłeś , dana ROLA nie została zwrócona , zgodnie z propozycjami @ erwin-brandstetter i @a_horse_with_no_name. Blok warunkowy został wykonany i trafiliśmy role "my_user" already exists.

Niestety nie jesteśmy pewni dokładnych warunków, ale to rozwiązanie pozwala obejść 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$

Prawdopodobnie można by było doprecyzować, aby wykluczyć inne wyjątki.

Możesz to zrobić w swoim pliku wsadowym, analizując dane wyjściowe:

SELECT * FROM pg_user WHERE usename = 'my_user'

a następnie uruchamiany psql.exeponownie, jeśli rola nie istnieje.

Takie samo rozwiązanie jak w przypadku Symuluj STWÓRZ BAZY DANYCH, JEŚLI NIE ISTNIEJE dla PostgreSQL? powinno działać - wyślij CREATE USER …do \gexec.

Obejście z poziomu psql

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

Obejście z powłoki

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

Zobacz zaakceptowaną odpowiedź, aby uzyskać więcej informacji.