Créer un rôle PostgreSQL (utilisateur) s'il n'existe pas

Question

Créer un rôle PostgreSQL (utilisateur) s'il n'existe pas

Comment écrire un script SQL pour créer un rôle dans PostgreSQL 9.1, mais sans créer d'erreur s'il existe déjà?

le script actuel a simplement:"

CREATE ROLE my_user LOGIN PASSWORD 'my_password';

Cela échoue si l'utilisateur existe déjà. J'aimerais quelque chose comme:

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

... mais cela ne fonctionne pas - IF ne semble pas être supporté en SQL simple.

j'ai un fichier batch qui crée un PostgreSQL 9.1 base de données, rôle et quelques autres choses. Il appelle psql.exe, en passant au nom d'un script SQL à exécuter. Jusqu'à présent, tous ces scripts sont des SQL simples et j'aimerais éviter PL/pgSQL et autres, si possible.

71

dynamic-sql postgresql roles sql

demandé sur Erwin Brandstetter 2011-11-11 13:39:27

8 réponses

score 101 · Answer 1

Simplifier d'une manière similaire à ce que vous aviez à l'esprit:

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

(sur @a_horse_with_no_name 's réponse et amélioré après @Grégoire le commentaire de .)

contrairement, par exemple, à CREATE TABLE il n'y a pas de clause IF NOT EXISTS pour CREATE ROLE (pourtant). Et vous ne peut pas exécuter dynamique Les déclarations DDL en SQL simple.

votre requête pour "éviter PL / pgSQL" est impossible sauf en utilisant un autre PL. Le DO statement utilise plpgsql comme langage de procédure par défaut. La syntaxe permet d'omettre la déclaration explicite:

DO [ LANGUAGE lang_name ] code

...

lang_name

Le nom du langage procédural dans lequel le code est écrit. Si omise, la valeur par défaut est plpgsql .

score 33 · Answer 2

ou si le rôle n'est pas le propriétaire de tout objet db on peut utiliser:

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

mais seulement si l'abandon de cet utilisateur ne fera pas de mal.

score 8 · Answer 3

Voici une solution générique utilisant 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;

Utilisation:

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)

score 7 · Answer 4

comme vous êtes sur la 9.x, vous pouvez envelopper cela dans une déclaration de 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$
;

score 6 · Answer 5

mon équipe rencontrait une situation avec plusieurs bases de données sur un serveur, en fonction de la base de données à laquelle vous vous êtes connecté, le rôle en question n'a pas été retourné par SELECT * FROM pg_catalog.pg_user , comme proposé par @erwin-brandstetter et @a_horse_with_no_name. Le bloc conditionnel exécuté, et nous avons frappé role "my_user" already exists .

Malheureusement, nous ne sommes pas sûrs des conditions exactes, mais cette solution fonctionne autour du problème:

        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$

Il pourrait probablement être fait plus précis pour exclure d'autres exceptions.

score 5 · Answer 6

Bash alternative (pour 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';"

(n'est pas la réponse à la question! c'est seulement pour ceux qui peuvent être utiles)

score 5 · Answer 7

la réponse acceptée souffre d'une condition de course si deux scripts de ce type sont exécutés simultanément sur le même cluster de Postgres (serveur DB), comme c'est commun dans les environnements d'intégration continue .

il est généralement plus sûr d'essayer de créer le rôle et de traiter avec élégance les problèmes lors de sa création:

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

score 3 · Answer 8

vous pouvez le faire dans votre fichier batch en analysant la sortie de:

SELECT * FROM pg_user WHERE usename = 'my_user'

puis psql.exe encore une fois si le rôle n'existe pas.

Las etiquetas más populares

Créer un rôle PostgreSQL (utilisateur) s'il n'existe pas

8 réponses