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

Как написать SQL-скрипт для создания роли в PostgreSQL 9.1, но без возникновения ошибки, если она уже существует?

текущий скрипт просто имеет:

CREATE ROLE my_user LOGIN PASSWORD 'my_password';

Это не срабатывает, если пользователь уже существует. Я бы хотел что-то вроде:

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

... но это не работает -IF не поддерживается в обычном SQL.

у меня есть пакетный файл, который создает базу данных PostgreSQL 9.1, роль и несколько других вещей. Он вызывает psql.исполняемый, передача имени сценария SQL для запуска. До сих пор все эти скрипты являются простыми SQL, и я хотел бы избежать PL/pgSQL и таких, если это возможно.

8 71
sql postgresql roles dynamic-sql

8 ответов:

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

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 ответ и улучшен после @Григорий комментарий.)

в отличие от, например,CREATE TABLE нет IF NOT EXISTS статьи CREATE ROLE (пока). А ты не может выполнение динамических инструкций DDL в обычном SQL.

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

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

101

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

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

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

33

вот общее решение с использованием 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)
8

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

моя команда столкнулась с ситуацией с несколькими базами данных на одном сервере, в зависимости от того, к какой базе данных вы подключились, роль, о которой идет речь, не была возвращена 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$

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

6

Баш Альтернативы (для Баш скрипта):

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

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

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

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

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

SELECT * FROM pg_user WHERE usename = 'my_user'

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

3