pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to remove a superuser
7+ messages / 4 participants
[nested] [flat]

* How to remove a superuser
@ 2006-10-10 19:22  Richard Ray <rray@mstc.state.ms.us>
  0 siblings, 2 replies; 7+ messages in thread

From: Richard Ray @ 2006-10-10 19:22 UTC (permalink / raw)
  To: pgsql-admin

I need to drop a superuser account. I get

dba=# drop role bram;
ERROR:  role "bram" cannot be dropped because some objects depend on it
DETAIL:  owner of view teacher
owner of type teacher
owner of view temp1
owner of type temp1
...

What is the preferred method.

Thanks
Richard Ray



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to remove a superuser
@ 2006-10-10 20:39  Tomeh, Husam <htomeh@firstam.com>
  parent: Richard Ray <rray@mstc.state.ms.us>
  1 sibling, 0 replies; 7+ messages in thread

From: Tomeh, Husam @ 2006-10-10 20:39 UTC (permalink / raw)
  To: Richard Ray <rray@mstc.state.ms.us>; pgsql-admin


My understanding is that, in 8.1, you'd need to revoke the privileges
before being able to drop the role/user.

8.2 seems to have two additional nice commands:  "drop owned" command to
remove the objects owned by the user, and "reassign owned" to change the
ownership and give the objects to someone else. 

http://developer.postgresql.org/pgdocs/postgres/sql-drop-owned.html
http://developer.postgresql.org/pgdocs/postgres/sql-reassign-owned.html



--
  Husam 
  http://firstdba.googlepages.com

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Richard Ray
Sent: Tuesday, October 10, 2006 12:23 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] How to remove a superuser

I need to drop a superuser account. I get

dba=# drop role bram;
ERROR:  role "bram" cannot be dropped because some objects depend on it
DETAIL:  owner of view teacher
owner of type teacher
owner of view temp1
owner of type temp1
...

What is the preferred method.

Thanks
Richard Ray

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
**********************************************************************
This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged.  If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited.  If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter.

Thank you.

                                   FADLD Tag
**********************************************************************




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to remove a superuser
@ 2006-10-10 20:41  Jim C. Nasby <jim@nasby.net>
  parent: Richard Ray <rray@mstc.state.ms.us>
  1 sibling, 1 reply; 7+ messages in thread

From: Jim C. Nasby @ 2006-10-10 20:41 UTC (permalink / raw)
  To: Richard Ray <rray@mstc.state.ms.us>; +Cc: pgsql-admin

On Tue, Oct 10, 2006 at 02:22:53PM -0500, Richard Ray wrote:
> I need to drop a superuser account. I get
> 
> dba=# drop role bram;
> ERROR:  role "bram" cannot be dropped because some objects depend on it
> DETAIL:  owner of view teacher
> owner of type teacher
> owner of view temp1
> owner of type temp1
> ...
> 
> What is the preferred method.

You need to reassign ownership of those objects to someone else.
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to remove a superuser
@ 2006-10-10 21:07  Richard Ray <rray@mstc.state.ms.us>
  parent: Jim C. Nasby <jim@nasby.net>
  0 siblings, 2 replies; 7+ messages in thread

From: Richard Ray @ 2006-10-10 21:07 UTC (permalink / raw)
  To: pgsql-admin

On Tue, 10 Oct 2006, Jim C. Nasby wrote:

> On Tue, Oct 10, 2006 at 02:22:53PM -0500, Richard Ray wrote:
>> I need to drop a superuser account. I get
>>
>> dba=# drop role bram;
>> ERROR:  role "bram" cannot be dropped because some objects depend on it
>> DETAIL:  owner of view teacher
>> owner of type teacher
>> owner of view temp1
>> owner of type temp1
>> ...
>>
>> What is the preferred method.
>
> You need to reassign ownership of those objects to someone else.
>

I can reassign the view but not the type.


Welcome to psql 8.1.0, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
        \h for help with SQL commands
        \? for help with psql commands
        \g or terminate with semicolon to execute query
        \q to quit

dba=# alter table teacher owner to bjay;
ALTER TABLE
dba=# alter type teacher owner to bjay;
ERROR:  "teacher" is a table's row type




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to remove a superuser
@ 2006-10-10 21:41  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Richard Ray <rray@mstc.state.ms.us>
  1 sibling, 1 reply; 7+ messages in thread

From: Tom Lane @ 2006-10-10 21:41 UTC (permalink / raw)
  To: Richard Ray <rray@mstc.state.ms.us>; +Cc: pgsql-admin

Richard Ray <rray@mstc.state.ms.us> writes:
> I can reassign the view but not the type.

You don't need to reassign the type's ownership, because it's just part
of the view.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to remove a superuser
@ 2006-10-11 01:15  Jim C. Nasby <jim@nasby.net>
  parent: Richard Ray <rray@mstc.state.ms.us>
  1 sibling, 0 replies; 7+ messages in thread

From: Jim C. Nasby @ 2006-10-11 01:15 UTC (permalink / raw)
  To: Richard Ray <rray@mstc.state.ms.us>; +Cc: pgsql-admin

On Tue, Oct 10, 2006 at 04:07:19PM -0500, Richard Ray wrote:
> On Tue, 10 Oct 2006, Jim C. Nasby wrote:
> 
> >On Tue, Oct 10, 2006 at 02:22:53PM -0500, Richard Ray wrote:
> >>I need to drop a superuser account. I get
> >>
> >>dba=# drop role bram;
> >>ERROR:  role "bram" cannot be dropped because some objects depend on it
> >>DETAIL:  owner of view teacher
> >>owner of type teacher
> >>owner of view temp1
> >>owner of type temp1
> >>...
> >>
> >>What is the preferred method.
> >
> >You need to reassign ownership of those objects to someone else.
> >
> 
> I can reassign the view but not the type.
> 
> 
> Welcome to psql 8.1.0, the PostgreSQL interactive terminal.
> 
> Type:  \copyright for distribution terms
>        \h for help with SQL commands
>        \? for help with psql commands
>        \g or terminate with semicolon to execute query
>        \q to quit
> 
> dba=# alter table teacher owner to bjay;
> ALTER TABLE
> dba=# alter type teacher owner to bjay;
> ERROR:  "teacher" is a table's row type

Yes, unfortunately you'll have to re-create the view as another owner.
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: How to remove a superuser
@ 2006-10-11 13:02  Richard Ray <rray@mstc.state.ms.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 7+ messages in thread

From: Richard Ray @ 2006-10-11 13:02 UTC (permalink / raw)
  To: pgsql-admin

On Tue, 10 Oct 2006, Tom Lane wrote:

> Richard Ray <rray@mstc.state.ms.us> writes:
>> I can reassign the view but not the type.
>
> You don't need to reassign the type's ownership, because it's just part
> of the view.
>

After I reassigned all the views I was allowed to drop the user.

Thanks

> 			regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2006-10-11 13:02 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-10 19:22 How to remove a superuser Richard Ray <rray@mstc.state.ms.us>
2006-10-10 20:39 ` Tomeh, Husam <htomeh@firstam.com>
2006-10-10 20:41 ` Jim C. Nasby <jim@nasby.net>
2006-10-10 21:07   ` Richard Ray <rray@mstc.state.ms.us>
2006-10-10 21:41     ` Tom Lane <tgl@sss.pgh.pa.us>
2006-10-11 13:02       ` Richard Ray <rray@mstc.state.ms.us>
2006-10-11 01:15     ` Jim C. Nasby <jim@nasby.net>

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