Re: function to grant select on all tables in several schemas

Gerd Koenig <koenig(at)transporeon(dot)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(at)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°