Drop user cascade

Started by Alex Ignatovover 9 years ago7 messagesgeneral

Jump to latest

Alex Ignatov

a.ignatov@postgrespro.ru

over 9 years ago

Hello!

Why we can DROP TABLE CASCADE, DROP VIEW CASCADE, DROP SEQUENCE CASCADE but
we can't DROP USER/ROLE CASCADE?

Why do Postgres have no such functionality as DROP USER CASCADE? Is there
any reasons in that absence?

Alex Ignatov
Postgres Professional: <http://www.postgrespro.com>
http://www.postgrespro.com
The Russian Postgres Company

Thomas Kellerer

spam_eater@gmx.net

over 9 years ago

In reply to: Alex Ignatov (#1)

Re: Drop user cascade

Alex Ignatov (postgrespro) schrieb am 19.10.2016 um 12:26:

Hello!

Why we can DROP TABLE CASCADE, DROP VIEW CASCADE, DROP SEQUENCE CASCADE but we can’t DROP USER/ROLE CASCADE?

Why do Postgres have no such functionality as DROP USER CASCADE? Is there any reasons in that absence?

You can do

drop owned by user_name;
drop user user_name;

Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Alex Ignatov

a.ignatov@postgrespro.ru

over 9 years ago

In reply to: Thomas Kellerer (#2)

Re: Drop user cascade

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Thomas Kellerer
Sent: Wednesday, October 19, 2016 1:53 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Drop user cascade

Alex Ignatov (postgrespro) schrieb am 19.10.2016 um 12:26:

Hello!

Why we can DROP TABLE CASCADE, DROP VIEW CASCADE, DROP SEQUENCE CASCADE but we can’t DROP USER/ROLE CASCADE?

Why do Postgres have no such functionality as DROP USER CASCADE? Is there any reasons in that absence?

You can do

drop owned by user_name;
drop user user_name;

Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I know this commands thank yo =).
To use them you need to invoke this command in all database because roles sit atop of PG database or you need to find DBs which has objects that depend on ROLE you want to delete. You can't do anything with objects in DB you are not connected

--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom Lane

tgl@sss.pgh.pa.us

over 9 years ago

In reply to: Alex Ignatov (#1)

Re: Drop user cascade

"Alex Ignatov \(postgrespro\)" <a.ignatov@postgrespro.ru> writes:

Why do Postgres have no such functionality as DROP USER CASCADE? Is there
any reasons in that absence?

The short answer is that DROP USER couldn't reach across databases to get
rid of owned objects in other databases. See

https://www.postgresql.org/docs/9.6/static/role-removal.html

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Alex Ignatov

a.ignatov@postgrespro.ru

over 9 years ago

In reply to: Tom Lane (#4)

Re: Drop user cascade

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Wednesday, October 19, 2016 4:31 PM
To: Alex Ignatov (postgrespro) <a.ignatov@postgrespro.ru>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Drop user cascade

"Alex Ignatov \(postgrespro\)" <a.ignatov@postgrespro.ru> writes:

Why do Postgres have no such functionality as DROP USER CASCADE? Is
there any reasons in that absence?

The short answer is that DROP USER couldn't reach across databases to get
rid of owned objects in other databases. See

https://www.postgresql.org/docs/9.6/static/role-removal.html

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Some security consideration bear in mind that DROP OWNED cant delete own
objects in other DBs? In general what stops us to do inter DBs connection
like MSSQL?

--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com The Russian Postgres
Company

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Melvin Davidson

melvin6925@gmail.com

over 9 years ago

In reply to: Alex Ignatov (#5)

Re: Drop user cascade

On Wed, Oct 19, 2016 at 10:03 AM, Alex Ignatov (postgrespro) <
a.ignatov@postgrespro.ru> wrote:

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Wednesday, October 19, 2016 4:31 PM
To: Alex Ignatov (postgrespro) <a.ignatov@postgrespro.ru>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Drop user cascade

"Alex Ignatov \(postgrespro\)" <a.ignatov@postgrespro.ru> writes:

Why do Postgres have no such functionality as DROP USER CASCADE? Is
there any reasons in that absence?

The short answer is that DROP USER couldn't reach across databases to get
rid of owned objects in other databases. See

https://www.postgresql.org/docs/9.6/static/role-removal.html

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Some security consideration bear in mind that DROP OWNED cant delete own
objects in other DBs? In general what stops us to do inter DBs connection
like MSSQL?

--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com The Russian Postgres
Company

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

In general what stops us to do inter DBs connection like MSSQL?

*It currently is not generic to PostgreSQL, but you can do that with the
dblink extension/functions.*

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Alex Ignatov

a.ignatov@postgrespro.ru

over 9 years ago

In reply to: Melvin Davidson (#6)

Re: Drop user cascade

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Melvin Davidson
Sent: Wednesday, October 19, 2016 5:35 PM
To: Alex Ignatov (postgrespro) <a.ignatov@postgrespro.ru>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Drop user cascade

On Wed, Oct 19, 2016 at 10:03 AM, Alex Ignatov (postgrespro) <a.ignatov@postgrespro.ru <mailto:a.ignatov@postgrespro.ru> > wrote:

-----Original Message-----
From: pgsql-general-owner@postgresql.org <mailto:pgsql-general-owner@postgresql.org>
[mailto:pgsql-general-owner@postgresql.org <mailto:pgsql-general-owner@postgresql.org> ] On Behalf Of Tom Lane
Sent: Wednesday, October 19, 2016 4:31 PM
To: Alex Ignatov (postgrespro) <a.ignatov@postgrespro.ru>
Cc: pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Drop user cascade

"Alex Ignatov \(postgrespro\)" <a.ignatov@postgrespro.ru <mailto:a.ignatov@postgrespro.ru> > writes:

Why do Postgres have no such functionality as DROP USER CASCADE? Is
there any reasons in that absence?

The short answer is that DROP USER couldn't reach across databases to get
rid of owned objects in other databases. See

https://www.postgresql.org/docs/9.6/static/role-removal.html

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org> ) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Some security consideration bear in mind that DROP OWNED cant delete own
objects in other DBs? In general what stops us to do inter DBs connection
like MSSQL?

--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com The Russian Postgres
Company

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org> )
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

In general what stops us to do inter DBs connection like MSSQL?

It currently is not generic to PostgreSQL, but you can do that with the dblink extension/functions.

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you. <http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif>

I know about dblink =)

The question was – is there any caveats to not allow xross db access. Maybe some security considerations prevent to implement it.

We all know that PG = one process rules multiple DBs why not to allow direct access to another DB. We have one transaction counter and so on where is the problems if any?

Alex Ignatov
Postgres Professional: <http://www.postgrespro.com> http://www.postgrespro.com
The Russian Postgres Company