It is possible to effectively use CREATE ROLE and its privileges, GRANT and REVOKE within a function in postgresql (version 9.4))

0

I have to build two functions to use in the creation of roles and in assigning a role to a group according to its category in postgresql.

Here's the code: The first function is to create a user and call my second function that will assign that user to a group according to their category.

CREATE OR REPLACE FUNCTION sp_addlogin(
IN _usuario character varying(30),
IN _contraseña character varying(4),
IN _categoria integer)
RETURNS text AS
$BODY$
declare mensaje text;
BEGIN
IF(select count(rolname) FROM pg_roles where rolname = _usuario) = 0 THEN
    EXECUTE FORMAT ('CREATE USER %I', _usuario, 'LOGIN PASSWORD %I',    _contraseña);
    PERFORM sp_adduser(_usuario, _categoria);
    mensaje = 'Usuario de BD agregado con exito';
ELSE
    RAISE 'Ya existe un rol con el mismo nombre, especifique';
END IF;
return mensaje;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION sp_adduser(
IN _usuario character varying(30),
IN _categoria integer)
RETURNS text AS
$BODY$
declare
mensaje text;
BEGIN
IF(SELECT count(rolname) FROM pg_roles WHERE rolname = _usuario) = 1 THEN
    IF(_categoria = 1 or _categoria = 2)THEN
        EXECUTE FORMAT ('GRANT', gestores, 'TO', _usuario);
        mensaje = 'Usuario añadido al grupo gestores';
    END IF;

    IF(_categoria = 3)THEN
        EXECUTE FORMAT ('GRANT', administradores, 'TO', _usuarrio);
        mensaje = 'Usuario añadido al grupo administradores';
    END IF;
 ELSE
    RAISE 'El usuario indicado no existe, reintente';
 END IF;
 return mensaje;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

When executing the first function, it generates this error:

ERROR:  column "gestores" does not exist
LINE 1: SELECT FORMAT ('GRANT', gestores, 'TO', _usuario)
                            ^
QUERY:  SELECT FORMAT ('GRANT', gestores, 'TO', _usuario)
CONTEXT:  PL/pgSQL function sp_adduser(character varying,integer) line 7     at EXECUTE statement
SQL statement "SELECT sp_adduser(_usuario, _categoria)"
PL/pgSQL function sp_addlogin(character varying,character varying,integer)     line 6 at PERFORM

********** Error **********

ERROR: column "gestores" does not exist
SQL state: 42703
Context: PL/pgSQL function sp_adduser(character varying,integer) line 7 at     EXECUTE statement
SQL statement "SELECT sp_adduser(_usuario, _categoria)"
PL/pgSQL function sp_addlogin(character varying,character varying,integer)    line 6 at PERFORM

I've been looking for a solution for a couple of hours, but frankly I do not know what I'm doing wrong. I am a beginner in SQL and postgres, I hope you can help me. Greetings.

    
asked by Osvaldo 04.07.2017 в 00:20
source

1 answer

0

Yes, you can. The problem with your code is that gestores is an identifier, the postgres understands that it is a variable or the name of a column, but there is no defined variable (in the declare section) with that name, nor is the EXECUTE in a SELECT with a FROM whose table (or FROM clause) has a column called managers.

So if managers is the name of the table, you should put

EXECUTE FORMAT('GANT gestores ON'...

if you do not have to declare the variable

    
answered by 04.07.2017 / 03:28
source