Re: Scripting GRANT on functions

You could continue with this function, with an additional cursor to get the parameters for the function. If this is a one off thing, that you just need to do once, you could use pg_dump to get the create function statements and then simply alter them with an re in your favorite editor.

You should consider using groups. Then you could just add the new user to the existing group that has execute access.

hope that helps....

Russ
----- Original Message -----
From: Stewart Ben (RBAU/EQS4) *
To: pgsql-sql(at)postgresql(dot)org
Sent: Thursday, October 06, 2005 1:27 AM
Subject: [SQL] Scripting GRANT on functions

Is there any easy way to script granting privileges to a number of
functions? I've got as far as the following code before realising that
I'll need to pass in the arguments, and the arguments are stored as OIDs
in pg_proc.

Is there any easy way, such as GRANT .... FUNCTION OID 12345?

---CODE---

DECLARE
curs REFCURSOR;
funcname VARCHAR;
BEGIN
OPEN foo FOR
SELECT proname FROM pg_proc
WHERE proname LIKE 'tr\\_%'
OR proname LIKE 'tt\\_%'
OR proname LIKE 'v\\_%'
OR proname LIKE 'vui\\_%';

FETCH curs INTO funcname;

WHILE FOUND LOOP
FETCH curs INTO funcname;
EXECUTE 'GRANT EXECUTE ON FUNCTION ' || funcname || ' TO myuser';
END LOOP;

CLOSE curs;
END;

---END CODE---

Best regards,

Ben Stewart

--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
mailto:ben(dot)stewart(at)au(dot)bosch(dot)com
http://www.bosch.com.au/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org