rol - postgresql lista de usuarios

Crear ROLE de PostgreSQL(usuario) si no existe

Question

¿Cómo escribo un script SQL para crear un ROLE en PostgreSQL 9.1, pero sin generar un error si ya existe?

El script actual simplemente tiene:

CREATE ROLE my_user LOGIN PASSWORD ''my_password'';

Esto falla si el usuario ya existe. Me gustaría algo como:

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

... pero eso no funciona - IF no parece ser compatible con SQL simple.

Tengo un archivo por lotes que crea una base de datos PostgreSQL 9.1, un rol y algunas otras cosas. Llama a psql.exe, pasando el nombre de un script SQL para ejecutar. Hasta ahora, todos estos scripts son SQL simples y me gustaría evitar PL / pgSQL y tal, si es posible.

Answer 1

Alternativa Bash (para scripts 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'';"

(¡No es la respuesta para la pregunta! Es solo para aquellos que pueden ser útiles)

Answer 2

Aquí hay una solución genérica usando 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;

Uso:

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)

Answer 3

Como está en 9.x, puede incluirlo en una declaración 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$
;

Answer 4

La respuesta aceptada adolece de una condición de carrera si dos de dichos scripts se ejecutan simultáneamente en el mismo clúster de Postgres (servidor de base de datos), como es habitual en entornos de integración continua .

Por lo general, es más seguro intentar crear el rol y tratar con gracia los problemas al crearlo:

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

Answer 5

Mi equipo estaba solucionando una situación con varias bases de datos en un servidor, dependiendo de la base de datos a la que se conectara, el PAPEL en cuestión no fue devuelto por SELECT * FROM pg_catalog.pg_user , tal como lo propusieron @ erwin-brandstetter y @a_horse_with_no_name. El bloque condicional ejecutado, y llegamos al role "my_user" already exists .

Lamentablemente, no estamos seguros de las condiciones exactas, pero esta solución funciona alrededor del problema:

        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$

Probablemente podría hacerse más específico para descartar otras excepciones.

Answer 6

O si el rol no es el propietario de ningún objeto db uno puede usar:

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

Pero solo si dejar caer este usuario no hará ningún daño.

Answer 7

Puede hacerlo en su archivo por lotes analizando la salida de:

SELECT * FROM pg_user WHERE usename = ''my_user''

y luego ejecutar psql.exe una vez más si el rol no existe.

Answer 8

Simplifica de manera similar a lo que tenías en mente:

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

(Basándose en la respuesta de @a_horse_with_no_name y mejorado después del comentario de @a_horse_with_no_name ).

A diferencia, por ejemplo, con CREATE TABLE no existe la cláusula IF NOT EXISTS para CREATE ROLE (todavía). Y no puede ejecutar sentencias DDL dinámicas en SQL puro.

Su solicitud para "evitar PL / pgSQL" es imposible, excepto al utilizar otro PL. La declaración DO utiliza plpgsql como lenguaje de procedimiento predeterminado. La sintaxis permite omitir la declaración explícita:

] code DO [ LANGUAGE lang_name ] code
...
lang_name
El nombre del lenguaje de procedimiento en el que está escrito el código. Si se omite, el valor predeterminado es plpgsql .