Detect role in Postgresql dynamically

Question

I have been trying to create a script that detects that a role already excists and if it does it should revoke all privileges. This works fine doing it like this:

DO $$DECLARE count int;
BEGIN
SELECT count(*) INTO count FROM pg_roles WHERE rolname = 'superman';
IF count > 0 THEN
    REVOKE ALL PRIVILEGES ON TABLE FROM superman;
END IF;
END$$;

But now I want this to be dynamic per environment since I will be using different role names per environment. So I tried to use the \set mechanism but that doesn't seem to work when using pl/sql so if I would do something like the following Postgresql is complaining with syntax errors:

/set environment _int

DO $$DECLARE count int;
BEGIN
SELECT count(*) INTO count FROM pg_roles WHERE rolname = 'superman';
IF count > 0 THEN
    REVOKE ALL PRIVILEGES ON TABLE FROM superman:environment;
END IF;
END$$;

Although if I would not do it in pl/sql the revoke statment works just fine. So my question is how can I make my script dynamic by passing parameters to it so they will be replaced?

Milen A. Radev · Accepted Answer · 2012-07-10 19:53:19Z

4

You have to use EXECUTE for dynamic SQL. Also, a DO statement cannot take parameters. Create a plpgsql function:

CREATE OR REPLACE FUNCTION f_revoke_all_from_role(_role text)
  RETURNS void AS
$BODY$
BEGIN

IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = _role) THEN
    EXECUTE 'REVOKE ALL PRIVILEGES ON TABLE x FROM ' || quote_ident(_role);
END IF;

END;
$BODY$ LANGUAGE plpgsql;

Call:

SELECT f_revoke_all_from_role('superman');

IF block is simpler with EXISTS.
I use quote_ident() to avoid SQLi.
The table name could be the second parameter of the function ...

edited Jul 10, 2012 at 19:53

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

answered Jul 10, 2012 at 19:05

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tranquilized Over a year ago

Ah ok.. Thnx!! I will try this out. I was already afraid to have to do it like this. I find it weird though since in Oracle it is possible to pass variables from the outside to a plsql script without having to write a function. I just want this logic in there so I can do a deployment of my database. Why do I have to create a function for this? Isn't there any other way of doing it like in Oracle?

Erwin Brandstetter Over a year ago

@user1515731: I wouldn't know of any. The plpgsql function doesn't seem to complicated, though, or does it?

Tranquilized Over a year ago

No it's not indeed but I don't like the fact that I have to create a function just to detect if a role exists and basically drop it afterwords since I won't be using it in my application. But I will try it out thnx for your help :-)

Collectives™ on Stack Overflow

Detect role in Postgresql dynamically

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related