Re: GRANT question

Chris Ochs said:
> Is there a shortcut to grant all privileges on a schema and it's objects
> to
> a user without having to issue GRANT statements for each individual
> object
> in the schema? Given all the possible types of objects I doubt it but
> thought I would ask just in case there was a way to do it.

There is a roundabout way, but it's a bit cludgey - but I use it a lot as
we add new tables, views and functions to our development db.

There are some examples below.

For example, select 'grant all on '||schemaname||'.'||tablename||' to
MYUSERNAME;'
from pg_tables
where schemaname in ('SOMESCHEMA1', 'SOMESCHEMA2', ... etc)
order by schemaname, tablename;

This creates a series of grant statements which you can then apply against
the database (using psql for example).

I guess that there is no reason why you couldn't create a function (using
a temp table, and the execute statement) which you passed the schema name
to grant rights for, and the user to receive the rights, and possibly
specify the kind of rights to grant. Invoking the function would then
apply the grants for you. I haven't bothered with this as the simple
queries below work fine for me.

Here are the queries I use:

For TABLES
==========
select 'grant all on '||schemaname||'.'||tablename||' to SOMEUSERNAME;'
from pg_tables
where schemaname in ('SOMESCHEMA1', 'SOMESCHEMA2')
order by schemaname, tablename;

For VIEWS
=========
SELECT 'grant all on '||n.nspname||'.'||c.relname||' to SOMEUSERNAME;'
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname in ('SOMESCHEMA1', 'SOMESCHEMA2')
and c.relkind = 'v'
ORDER BY n.nspname, c.relname;

For FUNCTIONS
=============
select 'grant all on function
'||n.nspname||'.'||p.proname||'('||oidvectortypes(p.proargtypes)||') to
SOMEUSERNAME;'
from pg_proc p, pg_namespace n
where n.oid = p.pronamespace
and n.nspname in ('SOMESCHEMA1', 'SOMESCHEMA2')
order by n.nspname, p.proname;

Hope that helps some.

John Sidney-Woollett