Re: Change onership of database and all tables? (fwd)

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:
> 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:
>>
>> 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 ]
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 7: don't forget to increase your free space map settings
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>