grant select on all tables of schema or database

Поиск
Список
Период
Сортировка
От Gene
Тема grant select on all tables of schema or database
Дата
Msg-id 430d92a20609272121j2639ba7enf687c0be7523074a@mail.gmail.com
обсуждение исходный текст
Ответы Re: grant select on all tables of schema or database  (Najib Abi Fadel <nabifadel@yahoo.com>)
Список pgsql-general
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;

В списке pgsql-general по дате отправления:

Предыдущее
От: "Bob"
Дата:
Сообщение: Re: continued segmentation fault
Следующее
От: John Sidney-Woollett
Дата:
Сообщение: Re: cyclical redundancy checksum algorithm(s)?