Обсуждение: 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
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 containinformation that is legally privileged. If you are not the addressee, or the person responsible for delivering itto the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictlyprohibited. If you have received this message by mistake, please immediately notify us by replying to the messageand delete the original message immediately thereafter. Thank you. FADLD Tag **********************************************************************
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)
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
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
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)
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 >