Re: Changing table owner to db owner.

On Tuesday 14 December 2010 5:02:56 pm Paul McGarry wrote:
> I have a number of DBs that are all the same structure but are owned
> by different users and I want to add a table to each of those DBs.
>
> At the moment I have to either connect to each DB as it's user (which
> means digging up it's password) or do it as the DB super user and run
> an ALTER table ... OWNER specifying the specific user for each of the
> DBs.
>
> My task would be easier if I could do something like:
>
> =====
>
> CREATE TABLE test
>
> (
> test_id integer NOT NULL,
> test_string text
> };
>
> ALTER TABLE test OWNER DBOWNER;
>
> ======
>
> Where DBOWNER magically resolved to the owner of the DB, so I wouldn't
> have to futz with different users, I could just use the same SQL
> script on every DB without caring about the specific users.
>
> Anyone have any ideas on how something like this could be done?
>
> Paul

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

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.

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