Re: function to grant select on all tables in several schemas - Mailing list pgsql-general
From | Andreas Kretschmer |
---|---|
Subject | Re: function to grant select on all tables in several schemas |
Date | |
Msg-id | 20100120155957.GA5027@tux Whole thread Raw |
In response to | function to grant select on all tables in several schemas (Gerd Koenig <koenig@transporeon.com>) |
Responses |
Re: function to grant select on all tables in several schemas
|
List | pgsql-general |
Gerd Koenig <koenig@transporeon.com> wrote: > Hello, > > I'm looking for a solution to grant select to a group to have "read-only" > group across all tables/views/.. in several schemas. I already found some > workarounds and I decided to create a function to cover this topic. > But calling this function throws the error: > "" > ERROR: column "´r´" does not exist > LINE 1: ...OM pg_class t, pg_namespace s WHERE t.relkind IN (´r´, ´v´,´... > ^ > QUERY: SELECT pg_catalog.quote_ident(s.nspname) AS schema_name, > pg_catalog.quote_ident(t.relname) AS relation_name FROM pg_class t, > pg_namespace s WHERE t.relkind IN (´r´, ´v´,´S´) AND t.relnamespace=s.oid AND > s.nspname = ´tisys´ order by s.nspname > CONTEXT: PL/pgSQL function "grant_select_to_ro_group" line 5 at FOR over > SELECT rows > "" > > The function was created by: > "" > CREATE OR REPLACE FUNCTION grant_select_to_ro_group() > RETURNS TEXT AS ' > DECLARE > sql text; > rel record; > BEGIN > FOR rel IN SELECT pg_catalog.quote_ident(s.nspname) AS schema_name, > pg_catalog.quote_ident(t.relname) AS relation_name > FROM pg_class t, pg_namespace s > WHERE t.relkind IN (´r´, ´v´,´S´) > AND t.relnamespace=s.oid AND s.nspname = ´tisys´ order by s.nspname > LOOP sql := ''GRANT SELECT ON '' || rel.schema_name || ''.'' || > rel.relation_name || '' TO ro_group''; > RAISE NOTICE ''%'', sql; > EXECUTE sql; > END LOOP; > RETURN ''OK''; > END; > ' LANGUAGE 'plpgsql'; > COMMENT ON FUNCTION grant_select_to_ro_group() > IS 'Give select privilege ON all relations in the given schema TO ro_group.'; > "" > > ...and has been called by: > "" > select grant_select_to_ro_group(); > "" > > any hints appreciated......GERD.... You should better use $$ instead of ' for the function-body. (unless you have a very old pg-version ...) I think the ´ as quoting-sign is also wrong... Rewrite your function to: CREATE OR REPLACE FUNCTION grant_select_to_ro_group() RETURNS TEXT AS $$ DECLARE sql text; rel record; BEGIN FOR rel IN SELECT pg_catalog.quote_ident(s.nspname) AS schema_name, pg_catalog.quote_ident(t.relname) AS relation_name FROM pg_class t, pg_namespace s WHERE t.relkind IN ('r', 'v','S') AND t.relnamespace=s.oid AND s.nspname = 'tisys' order by s.nspname LOOP sql := 'GRANT SELECT ON ' || rel.schema_name || '.' || rel.relation_name || ' TO ro_group'; RAISE NOTICE '%', sql; EXECUTE sql; END LOOP; RETURN 'OK'; END; $$ LANGUAGE 'plpgsql'; now it works: (i have copy&paste your function into a file and edit it there) kretschmer@tux:~$ psql test Zeitmessung ist an. psql (8.4.2) Geben Sie »help« für Hilfe ein. test=# \i grant.sql CREATE FUNCTION Zeit: 239,453 ms test=*# select grant_select_to_ro_group(); grant_select_to_ro_group -------------------------- OK (1 Zeile) Zeit: 48,836 ms Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
pgsql-general by date: