Thread: Change Ownership Recursively
I have a database that I must assign ownership to a new role. I want this new role to own the entire database and all of it's tables, views, triggers, & all. When I run the ALTER DATABASE command below, it only changes the database role but the tables are all still owned by the previous role. Is there a way I can assign the 27 tables to Lauren rather than doing the command one by one for each table? postgres=# ALTER DATABASE iamunix OWNER TO lauren; ALTER DATABASE postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- iamunix | lauren | UTF8 | en_US.UTF-8| en_US.UTF-8 | All tables still owned by Carlos: iamunix=# \d List of relationsSchema | Name | Type | Owner --------+------------------+----------+--------public | dept | table | carlospublic | dept_id_seq | sequence| carlospublic | employees | table | carlospublic | employees_id_seq | sequence | carlospublic | manager_lookup | view | carlospublic | managers | table | carlospublic | managers_id_seq | sequence | carlos **PS** I did do a Google search for "PostgreSQL 9.1 change ownership recursively" but either couldn't find what I was looking for or missed it.
On Thu, Mar 1, 2012 at 11:38 AM, Eric Ndengang <eric.ndengang_foyet@affinitas.de> wrote: > Hi > You can try this command "REASSIGN OWNED BY .... TO ..." like this: > REASSIGN OWNED BY previous_role TO new_role; > DROP OWNED previous_role; I did as follows: iamunix=# \c postgres SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) You are now connected to database "postgres" as user "carlos". postgres=# REASSIGN OWNED BY carlos TO lauren; REASSIGN OWNED postgres=# DROP OWNED BY carlos; DROP OWNED iamunix=# \d List of relationsSchema | Name | Type | Owner --------+------------------+----------+--------public | dept | table | carlospublic | dept_id_seq | sequence| carlospublic | employees | table | carlospublic | employees_id_seq | sequence | carlospublic | manager_lookup | view | carlospublic | managers | table | carlospublic | managers_id_seq | sequence | carlos That didn't work for some reason but mostly because I don't follow the concept of what's being done. I've now since changed the database role owner back to Carlos so now 'Carlos' owns both the database and all of it's tables. Can we start fresh and assume I just got the request to change the specified database and all of it's tables, sequences, views, & triggers to Lauren?
On 03/01/2012 09:04 AM, Carlos Mennens wrote: > On Thu, Mar 1, 2012 at 11:38 AM, Eric Ndengang > <eric.ndengang_foyet@affinitas.de> wrote: >> Hi >> You can try this command "REASSIGN OWNED BY .... TO ..." like this: >> REASSIGN OWNED BY previous_role TO new_role; >> DROP OWNED previous_role; > > I did as follows: > > iamunix=# \c postgres > SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) > You are now connected to database "postgres" as user "carlos". You are working on the database 'postgres' not 'iamunix'. REASSIGN works on the current database only. So you just REASSIGNED any objects in postgres. > postgres=# REASSIGN OWNED BY carlos TO lauren; > REASSIGN OWNED > postgres=# DROP OWNED BY carlos; > DROP OWNED > > iamunix=# \d > List of relations > Schema | Name | Type | Owner > --------+------------------+----------+-------- > public | dept | table | carlos > public | dept_id_seq | sequence | carlos > public | employees | table | carlos > public | employees_id_seq | sequence | carlos > public | manager_lookup | view | carlos > public | managers | table | carlos > public | managers_id_seq | sequence | carlos Change into iamunix and do the REASSIGN. > > That didn't work for some reason but mostly because I don't follow the > concept of what's being done. I've now since changed the database role > owner back to Carlos so now 'Carlos' owns both the database and all of > it's tables. Can we start fresh and assume I just got the request to > change the specified database and all of it's tables, sequences, > views,& triggers to Lauren? See above. For future reference including the Postgres version would be helpful. This area ownership/grants/etc has undergone a lot of changes over the various versions. > -- Adrian Klaver adrian.klaver@gmail.com
I changed to the suggested database which is owned by 'Carlos' and did as instructed. Everything worked fine. Thank you! On Thu, Mar 1, 2012 at 11:23 AM, Carlos Mennens <carlos.mennens@gmail.com> wrote: > I did do a Google search for "PostgreSQL 9.1 change ownership > recursively" but either couldn't find what I was looking for or > missed it. On Thu, Mar 1, 2012 at 1:36 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > For future reference including the Postgres version would be > helpful. This area ownership/grants/etc has undergone a lot of changes over > the various versions. I specified above I was using 9.1 PostgreSQL.
On 03/01/2012 11:37 AM, Carlos Mennens wrote: > I changed to the suggested database which is owned by 'Carlos' and did > as instructed. Everything worked fine. Thank you! In your previous post my guess is this: iamunix=# \c postgres was really meant to be: iamunix=# \c - postgres The first changes to database postgres as current user, the second changes the user while remaining on the current database. > > On Thu, Mar 1, 2012 at 11:23 AM, Carlos Mennens > <carlos.mennens@gmail.com> wrote: >> I did do a Google search for "PostgreSQL 9.1 change ownership >> recursively" but either couldn't find what I was looking for or >> missed it. > > On Thu, Mar 1, 2012 at 1:36 PM, Adrian Klaver<adrian.klaver@gmail.com> wrote: >> For future reference including the Postgres version would be >> helpful. This area ownership/grants/etc has undergone a lot of changes over >> the various versions. > > I specified above I was using 9.1 PostgreSQL. Oops, my mistake, I never got to the PS:( -- Adrian Klaver adrian.klaver@gmail.com
> iamunix=# \c postgres > > was really meant to be: > > iamunix=# \c - postgres > > The first changes to database postgres as current user, the second > changes the user while remaining on the current database. This is very helpful! psql> \c - username_for_new_connection -- Emi