Crear rol o usuario si no existe en PostgreSQL

1. Use el método CREATE ROLE para crear un rol si no existe en PostgreSQL
2. Conclusión

Hoy aprenderemos cómo crear un rol en PostgreSQL si no existe.

Un rol es una entidad que posee objetos en la base de datos definida por nosotros y puede tener diferentes privilegios. Dependiendo del sistema requerido, puede ser necesario tener múltiples usuarios o roles en una base de datos PostgreSQL.

Use el método CREATE ROLE para crear un rol si no existe en PostgreSQL

PostgreSQL nos proporciona un método para definir nuevos roles.

Sintaxis:

CREATE ROLE name [ [ WITH ] option [ ... ] ]

where option can be:

        SUPERUSER | NOSUPERUSER ...

Se han omitido otras opciones en la sintaxis y se pueden ver aquí.

Sigamos adelante y creemos un usuario MARK con una contraseña para iniciar sesión en nuestra base de datos.

Código:

create role MARK LOGIN PASSWORD 'mark123';

Si el usuario aún no existe, ejecutar lo anterior devolverá SUCCESSFUL.

Esto permite que nuestro rol, MARK, tenga una contraseña. En lugar de INICIAR SESIÓN, puede incluso elegir CON para definir una contraseña para este usuario.

Si el usuario ya existe, obtendrá el siguiente error.

Producción :

ERROR:  role "mark" already exists
SQL state: 42710

Para resolver esto, podemos usar algunos de los métodos definidos a continuación:

Utilice CREATE ROLE con una EXCEPCIÓN en PL/PgSQL

PL/PgSQL se usa para tener bucles FOR y estructuras de control en lugar de una simple instrucción SQL. Podemos usar esto a nuestro favor y emitir una EXCEPCIÓN en la duplicación para evitar el error, en lugar de que todo el script desaparezca.

Código:

 DO $$
BEGIN
CREATE ROLE MARK;
EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, moving to next statement', SQLERRM USING ERRCODE = SQLSTATE;
END
$$;

Si se encuentra un rol duplicado, la consulta anterior emitirá una declaración simple (que se muestra a continuación) y luego pasará a la siguiente consulta.

Producción :

NOTICE:  role "mark" already exists, moving to the next statement
DO

La consulta se devolvió correctamente en 168 mseg.

Para comprender cómo funciona la consulta, observe su estructura. Notará el uso de la cláusula EXCEPCIÓN, que se puede encontrar en ERRORES y MENSAJES en la documentación de PostgreSQL.

Luego puede usar la cláusula CASE para especificar el resultado que desea lograr una vez que se cumpla la condición deseada.

Sintaxis de la sentencia CASE:

CASE WHEN condition THEN result
    [WHEN ...]
    [ELSE result]
END

En nuestra declaración, lanzamos un AVISO cuando se detecta una EXCEPCIÓN en un objeto_duplicado. Lo que significa que solo arrojará un error si se encuentra una duplicación.

Se omitirán otros errores o fallas y no se generará AVISO. SQLERRM es la abreviatura de MENSAJE DE ERROR DE SQL y contiene una cadena que describe el mensaje de error con SQLSTATE devuelto.

DO inicia un bloque de código, principalmente un procedimiento, dentro de una transacción.

Esta solución ayuda a prevenir las condiciones de RACE. Funciona perfectamente ya que no tiene demora entre verificar y llamar a la excepción duplicada.

Además, si llama a esto desde un idioma diferente o desde un script PL/SQL, seguirá emitiendo el retorno correcto SQLSTATE. A diferencia de las soluciones proporcionadas a continuación, esto funciona perfectamente considerando otras circunstancias.

Utilice SI EXISTE para definir la condición de infracción de duplicación

Podemos crear un procedimiento con la condición SI EXISTE para LEVANTAR AVISO si se encuentra una infracción duplicada. Esto puede ser una modificación de la solución proporcionada anteriormente.

Código:

DO
$do$
BEGIN
    IF EXISTS (SELECT FROM pg_user
        WHERE  usename = 'mark'
        ) THEN

        RAISE NOTICE 'SKIP ROLE MAKER!';
    ELSE
        CREATE ROLE MARK LOGIN PASSWORD 'mark123';
    END IF;
END
$do$;

Producción :

NOTICE:  SKIP ROLE MAKER!
DO

La consulta se devolvió correctamente en 109 mseg.

Debido a que creamos el rol dentro de la tabla PG_USER, llamamos a IF EXISTS en la instrucción SELECT de esta tabla. Sin embargo, otras veces, es posible que tenga roles definidos en PG_ROLES en el catálogo, y es posible que deba cambiar su código a lo siguiente.

Código:

DO
$do$
BEGIN
    IF EXISTS (SELECT FROM pg_catalog.pg_roles
        WHERE  rolname = 'mark'
        ) THEN

        RAISE NOTICE 'SKIP ROLE MAKER!';
    ELSE
        CREATE ROLE MARK LOGIN PASSWORD 'mark123';
    END IF;
END
$do$;

Sin embargo, esto puede generar un error si su función no está definida en la tabla PG_ROLES. Así que asegúrese de saber dónde define sus roles, luego modifique el código para que funcione adecuadamente con sus condiciones.

Las condiciones RACE tienden a ser un problema en esta solución porque si llama a IF EXISTS verifique y espere a que la consulta genere el aviso si devuelve TRUE. Otra transacción ya habría llegado aquí y levantó el aviso, lo que puede invalidar esta operación.

Para evitar la condición RACE, podemos usar la solución dada arriba y agregarla a la consulta que hemos usado aquí. La solución anterior tiende a ser costosa debido a la cláusula de EXCEPCIÓN agregada dentro de un bloque de código.

Debido a la dificultad para entrar y salir de este bloque de código que contiene la cláusula EXCEPCIÓN, esto tiende a suceder. Por lo tanto, ahora podemos hacer un ajuste simple.

En lugar de ingresar este bloque de código todo el tiempo para verificar, podemos asegurarnos de que nuestras consultas solo usen este bloque de código si existe una CONDICIÓN DE CARRERA; de lo contrario, sáltelo y camine. El ajuste sería de la siguiente manera.

Código:

DO
$do$
BEGIN
    IF EXISTS (SELECT FROM pg_user
        WHERE  usename = 'mark'
        ) THEN

        RAISE NOTICE 'SKIP ROLE MAKER!';
    ELSE
        BEGIN
CREATE ROLE mark;
EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, moving to next statement', SQLERRM USING ERRCODE = SQLSTATE;
END;
END IF;
END
$do$;

Por lo tanto, se ingresará el bloque SINO en caso de que no haya duplicación, y podemos evitar la CONDICIÓN DE CARRERA como tal. Si desea asegurarse de que su consulta verifique su rol en diferentes tablas, se puede realizar otra pequeña modificación.

Código:

DO
$do$
BEGIN
    IF EXISTS (SELECT FROM pg_user
        WHERE  usename = 'mark'
        ) THEN

        RAISE NOTICE 'SKIP ROLE MAKER!';
    ELSE
        BEGIN
        IF EXISTS (SELECT FROM pg_catalog.pg_roles
        WHERE  rolname = 'mark'
        ) THEN

        RAISE NOTICE 'SKIP ROLE MAKER!';
        ELSE
        BEGIN
    CREATE ROLE mark;
    EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, moving to next statement', SQLERRM USING ERRCODE = SQLSTATE;
    END;
        END IF;
    END;
    END IF;
END;
$do$;

La consulta anterior verificará dos veces la existencia del rol y luego generará el aviso si hay una infracción duplicada en cualquiera de las tablas.

Use DROP IF combinado con CREATE para evitar una infracción duplicada

Código:

DROP ROLE IF EXISTS mark;
CREATE ROLE mark LOGIN PASSWORD 'mark123';

Este código es otra variación directa de la creación de un rol. Ejecutar las dos consultas como un grupo tiende a “ELIMINAR” el rol si existe, eliminando todas las ocurrencias posibles, y luego “CREAR” el rol sin violación duplicada.

Sin embargo, esta solución funciona mejor si no desea destruir un rol preexistente al agregar uno nuevo. En los casos en los que llamar a CREATE ROLE significa que desea CREAR el rol esta vez, también se puede usar lo anterior.

Use la consulta CREATE ROLE con GEXEC para evitar una infracción duplicada

Otro método eficiente para crear un rol sin duplicación es usar la siguiente consulta dentro del shell de PSQL, una vez conectado como raíz/superusuario a la sesión de la base de datos de PostgreSQL.

Código:

 SELECT 'CREATE USER mark' where not exists (select from pg_user where usename = 'mark')\gexec

Si el rol ya existe, no se devolverá nada. Pero si no es así, se devolverá el resultado a continuación.

Producción :

CREATE ROLE

Este resultado significa que la creación de su función se ha realizado correctamente.

Se supone que \GEXEC en PSQL ejecuta las sentencias escritas en grupos. Entonces, agregar GEXEC al final de la declaración anterior lo ejecutaría según sea necesario.

Use CREATE ROLE con diferentes errores de excepción

Código:

    DO
        $body$
        BEGIN
            CREATE ROLE mark LOGIN PASSWORD 'mark123';
        EXCEPTION WHEN others THEN
            RAISE NOTICE 'exception others not detected';
        END
        $body$

Ejecutar la consulta anterior funcionará para todos los errores de EXCEPCIÓN. La primera solución tiende a modificar esta consulta y hacer que funcione solo para el ERROR DE DUPLICACIÓN.

Envuelva la EXCEPCIÓN DUPLICADA para CREAR ROL en una función personalizada

Podemos crear una función simple que pueden usar los usuarios que acceden a una base de datos del servidor.

Código:

        CREATE OR REPLACE FUNCTION create_role_ifn(rname NAME) RETURNS TEXT AS
$$
BEGIN
    IF NOT EXISTS (SELECT * FROM pg_user WHERE usename = rname) THEN
        EXECUTE format('CREATE ROLE %I', rname);
        RETURN 'ROLE CREATED SUCCESSFULLY';
    ELSE
        RETURN format('ROLE ''%I'' EXISTING ALREADY', rname);
    END IF;
END;
$$
LANGUAGE plpgsql;

Este código toma el nombre del rol como el parámetro RNAME y llama a CREATE ROLE en la instrucción EXECUTE si el rol no existe. Si lo hace, nos devuelve el mensaje tal y como se especifica en el bloque SINO.

Producción:

Si la relación existe en la tabla PG_CATALOG PG_ROLES, puede modificar la consulta.

Código:

        CREATE OR REPLACE FUNCTION create_role_ifn(rname NAME) RETURNS TEXT AS
$$
BEGIN
    IF NOT EXISTS (SELECT * FROM pg_catalog.pg_roles WHERE rolname = rname) THEN
        EXECUTE format('CREATE ROLE %I', rname);
        RETURN 'ROLE CREATED SUCCESSFULLY';
    ELSE
        RETURN format('ROLE ''%I'' EXISTING ALREADY', rname);
    END IF;
END;
$$
LANGUAGE plpgsql;

Modifique la declaración SQL para encontrar ocurrencias en lugar de SI EXISTE

En el caso de versiones anteriores de PostgreSQL, puede encontrar la cantidad de veces que aparece el rol en una tabla y luego emitir la declaración CREATE ROLE en lugar de IF EXISTS si crea problemas.

Código:

do
$body$
declare
    occurences integer;
begin
    SELECT count(*)
        into occurences
    FROM pg_user
    WHERE usename = 'mark';

    IF occurences = 0 THEN
        CREATE ROLE mark LOGIN PASSWORD 'mark123';
    ELSE
        BEGIN
        raise exception 'ALREADY EXISTS!';
    END;
    END IF;
end
$body$

Cada vez que ejecute la consulta, las ocurrencias no excederán de 1. Y la instrucción ELSE se ejecutará si se encuentra el rol.

Producción :

ERROR:  ALREADY EXISTS!
CONTEXT:  PL/pgSQL function inline_code_block line 14 at RAISE
SQL state: P0001

Para condiciones en las que no tenemos el rol en la tabla PG_USER, podemos ir a la tabla PG_CATALOG y ejecutar la consulta.

Código:

do
$body$
declare
    occurrences integer;
begin
    SELECT count(*)
        into occurences
    FROM pg_catalog.pg_roles
    WHERE rolname = 'mark';

    IF occurences = 0 THEN
        CREATE ROLE mark LOGIN PASSWORD 'mark123';
    ELSE
        BEGIN
        raise exception 'ALREADY EXISTS!';
    END;
    END IF;
end
$body$

Conclusión

Hemos analizado varias formas de abordar el error duplicado al crear un rol en PostgreSQL. Sin embargo, diferentes soluciones funcionan para diferentes circunstancias.

En el caso de que varios clientes accedan a una base de datos en un servidor y luego ejecuten una consulta, puede haber condiciones de RACE que queramos evitar. Es mejor usar bloques de transacciones o códigos que ejecuten consultas en una sola instancia en esas condiciones.

Puede haber problemas con diferentes versiones de PostgreSQL en muchos otros casos. Hemos proporcionado diferentes formas de eliminar el error de excepción duplicada.

Crear una función a partir de la solución mejor elegida de arriba sería la forma recomendada de hacerlo. Ayuda a simplificar las consultas y puede ser ejecutado simplemente por diferentes clientes.