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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings