Utwórz rolę PostgreSQL (user), jeśli nie istnieje
Jak napisać skrypt SQL, aby utworzyć rolę w PostgreSQL 9.1, ale bez podnoszenia błędu, jeśli już istnieje?
Obecny skrypt ma po prostu:
CREATE ROLE my_user LOGIN PASSWORD 'my_password';
To 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 - IF
nie wydaje się być obsługiwany w zwykłym SQL.
Mam plik wsadowy, który tworzy bazę danych PostgreSQL 9.1, rolę i kilka innych rzeczy. Nazywa psql.exe, podanie nazwy skrypt SQL do uruchomienia. Na razie wszystkie te skrypty to zwykły SQL i chciałbym unikać PL / pgSQL i takich, jeśli to możliwe.
10 answers
Uprość w podobny sposób do tego, co 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$;
(bazując na @a_horse_with_no_name's answer and improved with @Gregory' s comment.)
W przeciwieństwie np. do CREATE TABLE
nie ma IF NOT EXISTS
klauzuli dla CREATE ROLE
(do co najmniej pg 12). A Ty nie możesz wykonywać dynamicznych instrukcji DDL w zwykłym SQL.
Twoja prośba o "unikanie PL / pgSQL" jest niemożliwa z wyjątkiem użycia innego PL. DO
statement używa plpgsql jako domyślnego języka proceduralnego. Składnia pozwala na pominięcie jawnej deklaracji:
DO [ LANGUAGE
lang_name
] code
...lang_name
Nazwa języka proceduralnego, w którym napisany jest kod. Jeśli pominięte, domyślną wartością jestplpgsql
.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-04-24 00:13:05
Akceptowana odpowiedź jest uwarunkowana rasą, jeśli dwa takie skrypty są wykonywane jednocześnie na tym samym klastrze Postgres (DB server), Jak to jest powszechne w środowiskach ciągłej integracji.
Ogólnie bezpieczniej jest próbować stworzyć rolę i z wdziękiem radzić sobie z problemami 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
$$;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-02-10 10:06:20
Lub jeśli rola nie jest właścicielem żadnych obiektów db można użyć:
DROP ROLE IF EXISTS my_user;
CREATE ROLE my_user LOGIN PASSWORD 'my_password';
Ale tylko w przypadku porzucenia tego użytkownika nie wyrządzi żadnej szkody.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-12-13 16:09:14
Bash alternative (for Bash scripting):
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ą być przydatni)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-06-04 22:05:34
Niektóre odpowiedzi sugerowały użycie pattern: sprawdź, czy rola nie istnieje, a jeśli nie, wydaj polecenie CREATE ROLE
. Ma to jedną wadę: stan rasy. Jeśli ktoś inny stworzy nową rolę pomiędzy sprawdzeniem a wydaniem polecenia CREATE ROLE
, to CREATE ROLE
oczywiście zawiedzie z błędem krytycznym.
Aby rozwiązać powyższy problem, więcej innych odpowiedzi już wspomniano użycie PL/pgSQL
, wydanie CREATE ROLE
bezwarunkowo, a następnie wyłapanie wyjątków od tego wywołania. Jest tylko jeden problem z tymi rozwiązaniami. Oni po cichu porzuć wszelkie błędy, w tym te, które nie są generowane przez fakt, że rola już istnieje. CREATE ROLE
może rzucać również inne błędy i symulacja {[8] } powinna wyciszyć tylko błąd, gdy rola już istnieje.
CREATE ROLE
Błąd throw duplicate_object
, gdy rola już istnieje. A obsługa wyjątków powinna wychwycić tylko ten jeden błąd. Jak wspomniano w innych odpowiedziach, dobrym pomysłem jest konwersja błędu krytycznego na zwykłe powiadomienie. Inne komendy PostgreSQL IF NOT EXISTS
dodają , skipping
do swojej wiadomości, więc dla spójności Ja też go tu dodaję.
Oto Pełny kod SQL do symulacji CREATE ROLE IF NOT EXISTS
z poprawnym wyjątkiem i propagacją SQLSTATE:
DO $$
BEGIN
CREATE ROLE test;
EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
END
$$;
Wyjście testowe (wywoływane dwa razy 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-05-02 14:20:00
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;
Użycie:
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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-02-20 12:38:18
Jak na 9.x, można to zawinąć w instrukcję 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$
;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-11-11 10:55:17
Mój zespół uderzył w sytuację z wieloma bazami danych na jednym serwerze, w zależności od tego, do której bazy danych się podłączyłeś, Rola, o której mowa, nie została zwrócona przez SELECT * FROM pg_catalog.pg_user
, Jak zaproponowali @erwin-brandstetter i @a_horse_with_no_name. Blok warunkowy został wykonany i trafiamy role "my_user" already exists
.
Niestety nie jesteśmy pewni dokładnych warunków, ale to rozwiązanie działa wokół problemu:
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łoby bardziej szczegółowo wykluczyć inne wyjątki.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-08-15 22:35:56
To samo rozwiązanie co dla symulować tworzenie bazy danych, jeśli nie istnieje dla PostgreSQL? powinno zadział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 accepted answer there Po Więcej Szczegółów.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-05-08 11:51:58
Możesz to zrobić w pliku wsadowym, analizując Wyjście:
SELECT * FROM pg_user WHERE usename = 'my_user'
A następnie uruchamia psql.exe
ponownie, jeśli rola nie istnieje.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-12-12 19:18:09