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....
