Chris
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majo...@postgresql.org)
No. People have asked about similar ways to do lots of grants at once,
but I haven't seen anyone claim to be working on this.
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
select 'grant all on '||n.nspname||'.'||c.relname||' to MYUSERNAME;'
from pg_class c, pg_namespace n
where n.oid = c.relnamespace
and c.relkind IN ('S')
and n.nspname in ('SOMESCHEMA1', 'SOMESCHEMA2');
John Sidney-Woollett
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster