function to grant select on all tables in several schemas

From: Gerd Koenig <koenig(at)transporeon(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: function to grant select on all tables in several schemas
Date: 2010-01-20 15:20:04
Message-ID: 201001201620.04280.koenig@transporeon.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dimitri Fontaine 2010-01-20 15:56:04 Re: more docs on extending postgres in C
Previous Message Pavel Stehule 2010-01-20 14:56:23 Re: more docs on extending postgres in C