pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedChange Ownership Recursively
6+ messages / 3 participants
[nested] [flat]
* Change Ownership Recursively
@ 2012-03-01 16:23 Carlos Mennens <carlos.mennens@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Carlos Mennens @ 2012-03-01 16:23 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Change Ownership Recursively
@ 2012-03-01 17:04 Carlos Mennens <carlos.mennens@gmail.com>
parent: Carlos Mennens <carlos.mennens@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Carlos Mennens @ 2012-03-01 17:04 UTC (permalink / raw)
To: pgsql-sql
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 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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Change Ownership Recursively
@ 2012-03-01 18:36 Adrian Klaver <adrian.klaver@gmail.com>
parent: Carlos Mennens <carlos.mennens@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Adrian Klaver @ 2012-03-01 18:36 UTC (permalink / raw)
To: Carlos Mennens <carlos.mennens@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Change Ownership Recursively
@ 2012-03-01 19:37 Carlos Mennens <carlos.mennens@gmail.com>
parent: Adrian Klaver <adrian.klaver@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Carlos Mennens @ 2012-03-01 19:37 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Change Ownership Recursively
@ 2012-03-01 19:44 Adrian Klaver <adrian.klaver@gmail.com>
parent: Carlos Mennens <carlos.mennens@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Adrian Klaver @ 2012-03-01 19:44 UTC (permalink / raw)
To: Carlos Mennens <carlos.mennens@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Change Ownership Recursively
@ 2012-03-02 16:56 Emi Lu <emilu@encs.concordia.ca>
parent: Adrian Klaver <adrian.klaver@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Emi Lu @ 2012-03-02 16:56 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@gmail.com>; +Cc: Carlos Mennens <carlos.mennens@gmail.com>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-03-02 16:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-01 16:23 Change Ownership Recursively Carlos Mennens <carlos.mennens@gmail.com>
2012-03-01 17:04 ` Carlos Mennens <carlos.mennens@gmail.com>
2012-03-01 18:36 ` Adrian Klaver <adrian.klaver@gmail.com>
2012-03-01 19:37 ` Carlos Mennens <carlos.mennens@gmail.com>
2012-03-01 19:44 ` Adrian Klaver <adrian.klaver@gmail.com>
2012-03-02 16:56 ` Emi Lu <emilu@encs.concordia.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox