pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow 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>
2006-10-10 20:39 ` Re: How to remove a superuser Tomeh, Husam <htomeh@firstam.com>
2006-10-10 20:41 ` Re: How to remove a superuser Jim C. Nasby <jim@nasby.net>
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 19:22 How to remove a superuser Richard Ray <rray@mstc.state.ms.us>
@ 2006-10-10 20:39 ` Tomeh, Husam <htomeh@firstam.com>
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 19:22 How to remove a superuser Richard Ray <rray@mstc.state.ms.us>
@ 2006-10-10 20:41 ` Jim C. Nasby <jim@nasby.net>
2006-10-10 21:07 ` Re: How to remove a superuser 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 19:22 How to remove a superuser Richard Ray <rray@mstc.state.ms.us>
2006-10-10 20:41 ` Re: How to remove a superuser Jim C. Nasby <jim@nasby.net>
@ 2006-10-10 21:07 ` Richard Ray <rray@mstc.state.ms.us>
2006-10-10 21:41 ` Re: How to remove a superuser Tom Lane <tgl@sss.pgh.pa.us>
2006-10-11 01:15 ` Re: How to remove a superuser 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 19:22 How to remove a superuser Richard Ray <rray@mstc.state.ms.us>
2006-10-10 20:41 ` Re: How to remove a superuser Jim C. Nasby <jim@nasby.net>
2006-10-10 21:07 ` Re: How to remove a superuser Richard Ray <rray@mstc.state.ms.us>
@ 2006-10-10 21:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-10-11 13:02 ` Re: How to remove a superuser 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-10 19:22 How to remove a superuser Richard Ray <rray@mstc.state.ms.us>
2006-10-10 20:41 ` Re: How to remove a superuser Jim C. Nasby <jim@nasby.net>
2006-10-10 21:07 ` Re: How to remove a superuser Richard Ray <rray@mstc.state.ms.us>
2006-10-10 21:41 ` Re: How to remove a superuser Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-10-11 13:02 ` Richard Ray <rray@mstc.state.ms.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
* Re: How to remove a superuser
2006-10-10 19:22 How to remove a superuser Richard Ray <rray@mstc.state.ms.us>
2006-10-10 20:41 ` Re: How to remove a superuser Jim C. Nasby <jim@nasby.net>
2006-10-10 21:07 ` Re: How to remove a superuser Richard Ray <rray@mstc.state.ms.us>
@ 2006-10-11 01:15 ` Jim C. Nasby <jim@nasby.net>
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
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