Re: Changing table owner to db owner.

From: Radosław Smogura <rsmogura(at)softperience(dot)eu>
To: pgsql-general(at)postgresql(dot)org
Cc: Paul McGarry <paul(at)paulmcgarry(dot)com>
Subject: Re: Changing table owner to db owner.
Date: 2010-12-15 17:51:26
Message-ID: 201012151851.26201.rsmogura@softperience.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)paulmcgarry(dot)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

Browse pgsql-general by date

  From Date Subject
Next Message Jan Kesten 2010-12-15 18:12:46 Re: Searing array fields - or should I redesign?
Previous Message Merlin Moncure 2010-12-15 17:25:20 Re: Fast Insert and Update (through COPY)