Re: Changing table owner to db owner.

On Tuesday 14 December 2010 5:58:16 pm Paul McGarry wrote:
> On Wed, Dec 15, 2010 at 12:39 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
wrote:
> > You did not say what version you are using. In 9.0 you have this option:
> > http://www.postgresql.org/docs/9.0/interactive/sql-alterdefaultprivileges
> >.html
>
> Our servers are a mix of 8.3 and 8.4.
>
> > I generally do as superuser;
> >
> > SET ROLE user;
> > CREATE TABLE table_name;
> >
> > It requires you knowing the user_name/role_name for each db, though it
> > would be possible to create a script that automates that by doing a
> > lookup.
>
> It's a pity something nasty like:
> SET ROLE (select pg_get_userbyid(relowner) from pg_class where
> relname='existingtable');
> doesn't work.
>
> Using SET ROLE like that is certainly helpful though, I have about ~30
> to update so anything that makes it easier is a help.
>
> Paul

Well you could wrap it in a function and add the function to your dbs. For new
databases add it to template1 and it will automatically be added, assuming you
are not overriding the default template on CREATE DATABASE. See here for system
functions that might help:
http://www.postgresql.org/docs/9.0/interactive/functions-info.html

Also you can get the db owner for the db by querying pg_database and pg_user
where datdba in pg_database maps to usesysid in pg_user.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com