Re: grant select on all tables of schema or database

2006/9/28, Najib Abi Fadel <nabifadel(at)yahoo(dot)com>:
> when u connect to the database type:
> \h GRANT
> and you will get all the Grant options:
>
> GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
> ON DATABASE dbname [, ...]
> TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
>
> This will grant the privileges on all tables under the database ....
>
> HTH
>
>
>
>
>
> Gene <genekhart(at)gmail(dot)com> wrote:
> It seems like it should be a very easy problem to solve I just need one
> role to have select privileges on all the tables of a particular schema or
> database including any new tables that are created since they are created
> programmatically daily. I've combed google and the docs to no avail. Do I
> need to write pg/plsql scripts just to so something like that?? I believe on
> other dbms you can just say grant all on schema.* or something to that
> effect. The script i found below would be ok if tables weren't being created
> constantly.
>
> using version 8.1.4
>
> thanks,
> Gene
>
> ----
> CREATE OR REPLACE FUNCTION pgx_grant(text, text, text)
> RETURNS int4 AS
> $BODY$
> DECLARE
> priv ALIAS FOR $1;
> patt ALIAS FOR $2;
> user ALIAS FOR $3;
> obj record;
> num integer;
> BEGIN
> num:=0;
> FOR obj IN SELECT relname FROM pg_class
> WHERE relname LIKE patt || '%' AND relkind in ('r','v','S') LOOP
> EXECUTE 'GRANT ' || priv || ' ON ' || obj.relname || ' TO ' || user;
> num := num + 1;
> END LOOP;
> RETURN num;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
> ALTER FUNCTION pgx_grant(text, text, text) OWNER TO root;

I don't get it. I grant all privileges on a database to a role, but
the server won't let it access the schemas. I grant all privileges on
the schema to the same role, but the server won't let it access the
relations in the schema.

GRANT ALL ON DATABASE testdb TO myuser
GRANT ALL ON SCHEMA testschema TO myuser;

Any idea what I'm doing wrong?

TIA,
t.n.a.