Re: Postgresql -- initial impressions and comments

Bruno Wolff III wrote:
> On Mon, Dec 02, 2002 at 18:44:03 -0800,
> "j.random.programmer" <javadesigner(at)yahoo(dot)com> wrote:
>>5) There is no way to grant permissions on all tables
>>within a database to some user. You have to grant
>>permissions on each table one-by-one. What I want
>>to do (and mysql allows this) is something like:
>>
>>GRANT ALL on foodb.* to user_bar;
>
> You can get the list of tables from the system catalog and then issue
> grant statements for them. You could write a function to do this,
> do it in an application or write sql query output to a script which
> you then execute.

For example (not heavily tested!)

CREATE OR REPLACE FUNCTION grant_all(text) RETURNS TEXT AS '
DECLARE
rel record;
sql text;
BEGIN
FOR rel IN SELECT pg_catalog.quote_ident(c.relname) AS relname FROM
pg_catalog.pg_class c WHERE c.relkind = ''r'' AND c.relnamespace NOT IN
(select oid from pg_catalog.pg_namespace where nspname like ''pg\_%'') AND
pg_catalog.pg_table_is_visible(c.oid) LOOP
sql := ''grant all on '' || rel.relname || '' to '' || $1;
RAISE NOTICE ''%'', sql;
EXECUTE sql;
END LOOP;
RETURN ''OK'';
END;
' LANGUAGE 'plpgsql';

create user foo;
select grant_all('foo');

HTH,

Joe