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

function to grant select on all tables in several schemas

From

Gerd Koenig

Date:

20 January 2010, 14:45:23

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

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

From

Pavel Stehule

Date:

20 January 2010, 14:59:57

Hello

I am not sure, but maybe .. you are doesn't use correct quotes:

you use ´´, but you have to use ''

Regards
Pavel Stehule


2010/1/20 Gerd Koenig <koenig@transporeon.com>:
> 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....
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

From

Andreas Kretschmer

Date:

20 January 2010, 15:00:16

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°

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

From

Gerd Koenig

Date:

21 January 2010, 06:03:09

Hello Andreas,

thanks for your support, perfect :-)

regards....GERD....

On Wednesday 20 January 2010 4:59:57 pm Andreas Kretschmer wrote:
> 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°



--
/====================================\
| Gerd König
| - Infrastruktur -
|
| TRANSPOREON GmbH
| Magirus-Deutz-Str. 16 | Stadtregal
| DE - 89077 Ulm
|
| Tel: +49 [0]731 16906 106
| Fax: +49 [0]731 16906 99
| koenig@transporeon.com
| www.transporeon.com
|
\====================================/


TRANSPOREON GmbH, Amtsgericht Ulm, HRB 722056
Geschäftsf.: Peter Förster, Roland Hötzl, Marc-Oliver Simon