Change onership of database and all tables?

On Sun, 28 Dec 2003, Russ Schneider wrote:

In 7.2, how would you change ownership of a database and all its tables
and sequences?

Right now everything is owned by postgres and I want to change ownership
to a regualar user.

Or if no one can answer the above question, what about a way to grant
permissions on more than one table at a time? Like GRANT ALL PRIVILEGES
ON [ALL TABLES] TO [USER]?

--
[ Russ Schneider (a.k.a. Sugapablo) ]
[ http://www.sugapablo.com <--music ]
[ http://www.sugapablo.net <--personal ]
[ sugapablo@12jabber.com <--jabber IM ]

On Sun, Dec 28, 2003 at 20:50:47 -0500,
Russ Schneider <russ@sugapablo.com> wrote:

Or if no one can answer the above question, what about a way to grant
permissions on more than one table at a time? Like GRANT ALL PRIVILEGES
ON [ALL TABLES] TO [USER]?

You can do this with a script that gets the names of all of the tables
and then does a grant on each one.

If you use the following query:

select 'grant all on '||schemaname||'.'||tablename||' to NEWOWNER;'
from pg_tables
where schemaname in ('schema1', 'schema2', 'schema3', 'schemaN');

You will generate the appropriate scripts to grant ALL rights on tables
belonging to 'schema1', 'schema2', 'schema3', 'schemaN' to the user
NEWUSER.

Copy the resulting rows, and run them against the database. Simple, and
effective.

Hope that helps.

John Sidney-Woollett

Russ Schneider said:

Russ

I should have added (maybe you know already), that you need to grant USAGE
on the schemas containing the tables to your (new) user.

ie, grant usage on schema schema1 to NEWUSER;

And you can also modify the user's search_path so that it includes the
schemas that you are granting access to.

ie, alter user NEWUSER set search_path=schema1, schema2, schema3, schemaN;

Doing this means that you can refer to the tables without having to prefix
them with the schema name.

Here are some other scripts that might come in handy:

-- used to generate grants on FUNCTIONS
select 'grant all on function
'||n.nspname||'.'||p.proname||'('||oidvectortypes(p.proargtypes)||') to
NEWUSER;'
from pg_proc p, pg_namespace n
where n.oid = p.pronamespace
and n.nspname in ('schema1', 'schema2', 'schema3', 'schemaN');

-- used to generate grants on SEQUENCES
select 'grant all on '||n.nspname||'.'||c.relname||' to NEWUSER;'
from pg_class c, pg_namespace n
where n.oid = c.relnamespace
and c.relkind IN ('S')
and n.nspname in ('schema1', 'schema2', 'schema3', 'schemaN');

Hope that helps too.

John

John Sidney-Woollett said: