Создайте PostgreSQL ROLE (пользователь), если он не существует

Ответ 1

Упростите аналогично тому, что вы имели в виду:

DO
$do$
BEGIN
   IF NOT EXISTS (
      SELECT                       -- SELECT list can stay empty for this
      FROM   pg_catalog.pg_roles
      WHERE  rolname = 'my_user') THEN

      CREATE ROLE my_user LOGIN PASSWORD 'my_password';
   END IF;
END
$do$;

(Построен на ответе @a_horse_with_no_name и улучшен после @Gregory комментария.)

В отличие, например, от CREATE TABLE нет условия IF NOT EXISTS для CREATE ROLE (пока). И вы не можете выполнять динамические операторы DDL в простом SQL.

Ваш запрос "избежать PL/pgSQL" невозможен, кроме как с использованием другого PL. Оператор DO использует plpgsql в качестве процедурного языка по умолчанию. Синтаксис позволяет опустить явное объявление:

DO [ LANGUAGE lang_name ] code
...
lang_name
Имя процедурного языка, на котором написан код. Если оно опущено, по умолчанию используется plpgsql.

Ответ 2

Или, если роль не является владельцем каких-либо объектов db, которые можно использовать:

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

Но только если этот пользователь не наносит вреда.

Ответ 3

Принятый ответ страдает от состояния гонки, если два таких сценария выполняются одновременно на одном и том же кластере Postgres (сервер БД), как это обычно бывает в средах с непрерывной интеграцией.

Как правило, безопаснее попытаться создать роль и изящно справиться с проблемами при ее создании:

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

Ответ 4

Альтернатива 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';"

(не ответ на вопрос! это только для тех, кто может быть полезным)

Ответ 5

Вот общее решение, использующее 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)

Ответ 6

Как вы на 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$
;

Ответ 7

Моя команда столкнулась с ситуацией с несколькими базами данных на одном сервере, в зависимости от того, к какой базе данных вы подключились, вопрос ROLE не был возвращен SELECT * FROM pg_catalog.pg_user, как было предложено @erwin-brandstetter и @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$

Возможно, более конкретным было бы исключить другие исключения.

Ответ 8

Вы можете сделать это в своем пакетном файле, разобрав вывод:

SELECT * FROM pg_user WHERE usename = 'my_user'

а затем запустите psql.exe еще раз, если роль не существует.

Ответ 9

В некоторых ответах предлагается использовать шаблон: проверьте, не существует ли роль, а если нет, CREATE ROLE команду CREATE ROLE. Это имеет один недостаток: состояние гонки. Если кто-то еще создает новую роль между проверкой и CREATE ROLE команды CREATE ROLE тогда CREATE ROLE явно завершается с фатальной ошибкой.

Чтобы решить вышеупомянутую проблему, в других ответах уже упоминалось использование PL/pgSQL, безоговорочно выдавая CREATE ROLE а затем перехватывая исключения из этого вызова. Есть только одна проблема с этими решениями. Они молча отбрасывают любые ошибки, в том числе те, которые не вызваны тем, что роль уже существует. CREATE ROLE может генерировать и другие ошибки и симуляцию, IF NOT EXISTS должен игнорировать только ошибку, когда роль уже существует.

CREATE ROLE duplicate_object ошибку duplicate_object когда роль уже существует. И обработчик исключений должен отлавливать только одну эту ошибку. Как уже упоминалось в других ответах, хорошей идеей является преобразование фатальной ошибки в простое уведомление. Другие команды PostgreSQL IF NOT EXISTS добавляются , skipping их сообщения, поэтому для согласованности я добавляю их и здесь.

Вот полный код SQL для моделирования CREATE ROLE IF NOT EXISTS с правильным исключением и распространением sqlstate:

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

Ответ 10

То же решение, что и для Simulate CREATE DATABASE, ЕСЛИ НЕ СУЩЕСТВУЕТ для PostgreSQL? должно работать - отправьте CREATE USER … в \gexec.

Обходной путь изнутри PSQL

SELECT 'CREATE USER my_user'
WHERE NOT EXISTS (SELECT FROM pg_user WHERE usename = 'my_user')\gexec

Обходной путь из оболочки

echo "SELECT 'CREATE USER my_user' WHERE NOT EXISTS (SELECT FROM pg_user WHERE usename = 'my_user')\gexec" | psql

Смотрите принятый ответ там для более подробной информации.