Try this:
1. Open psql console as user postgres.
2. Execute
SELECT ('\\c ' || datname || '\nALTER TABLE the_table_name OWNER TO ' ||
rolname || ';\n') from pg_database join pg_authid on datdba = pg_authid.oid;
3. Check results.
4. Select results with mouse.
5. Paste it to postgres console (Middle Button on Linux :))
Kind regards,
Radosław Smogura
http://www.softperience.eu
Paul McGarry <paul@paulmcgarry.com> Wednesday 15 December 2010 02:02:56
> 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