[SQL] 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 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 **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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Change Ownership Recursively
On Thu, Mar 1, 2012 at 11:38 AM, Eric Ndengang 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 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 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? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Change Ownership Recursively
On 03/01/2012 09:04 AM, Carlos Mennens wrote: On Thu, Mar 1, 2012 at 11:38 AM, Eric Ndengang 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.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Change Ownership Recursively
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 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 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Change Ownership Recursively
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 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 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.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Change Ownership Recursively
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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
