pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedScripting GRANT on functions
3+ messages / 3 participants
[nested] [flat]
* Scripting GRANT on functions
@ 2005-10-06 05:27 Stewart Ben (RBAU/EQS4) * <Ben.Stewart@au.bosch.com>
0 siblings, 2 replies; 3+ messages in thread
From: Stewart Ben (RBAU/EQS4) * @ 2005-10-06 05:27 UTC (permalink / raw)
To: pgsql-sql
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.stewart@au.bosch.com
http://www.bosch.com.au/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Scripting GRANT on functions
@ 2005-10-06 13:06 Russell Simpkins <russellsimpkins@hotmail.com>
parent: Stewart Ben (RBAU/EQS4) * <Ben.Stewart@au.bosch.com>
1 sibling, 0 replies; 3+ messages in thread
From: Russell Simpkins @ 2005-10-06 13:06 UTC (permalink / raw)
To: Stewart Ben (RBAU/EQS4) * <Ben.Stewart@au.bosch.com>; pgsql-sql
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@postgresql.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.stewart@au.bosch.com
http://www.bosch.com.au/
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Scripting GRANT on functions
@ 2005-10-06 13:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stewart Ben (RBAU/EQS4) * <Ben.Stewart@au.bosch.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-10-06 13:33 UTC (permalink / raw)
To: Stewart Ben (RBAU/EQS4) * <Ben.Stewart@au.bosch.com>; +Cc: pgsql-sql
"Stewart Ben (RBAU/EQS4) *" <Ben.Stewart@au.bosch.com> writes:
> 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?
Coerce the OID to regprocedure to generate a usable name for the
function.
regression=# select oid from pg_proc limit 5;
oid
------
1242
1243
1244
31
1245
(5 rows)
regression=# select oid::regprocedure from pg_proc limit 5;
oid
------------------
boolin(cstring)
boolout(boolean)
byteain(cstring)
byteaout(bytea)
charin(cstring)
(5 rows)
regression=#
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-10-06 13:33 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-06 05:27 Scripting GRANT on functions Stewart Ben (RBAU/EQS4) * <Ben.Stewart@au.bosch.com>
2005-10-06 13:06 ` Russell Simpkins <russellsimpkins@hotmail.com>
2005-10-06 13:33 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox