[GENERAL] Drop user cascade

2016-10-19 Thread Alex Ignatov (postgrespro) 
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 
The Russian Postgres Company

Re: [GENERAL] Drop user cascade

2016-10-19 Thread Alex Ignatov (postgrespro) 


-Original Message-
From: [email protected] 
[mailto:[email protected]] On Behalf Of Thomas Kellerer
Sent: Wednesday, October 19, 2016 1:53 PM
To: [email protected]
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 ([email protected]) 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] journaled FS and and WAL

2016-10-19 Thread Alex Ignatov (postgrespro) 

-Original Message-
From: [email protected] 
[mailto:[email protected]] On Behalf Of [email protected]
Sent: Wednesday, October 19, 2016 11:01 AM
To: Michael Paquier 
Cc: Albe Laurenz ; [email protected]
Subject: Re: [GENERAL] journaled FS and and WAL

So, as for the data content of the WAL file, I see that no more page will be 
allocated. I wonder if during a crash, strange things can still happen at disk 
level however, in particular in SSD devices; on these things we have no 
control, and perhaps journaling helps?
As for the metadata, if during a crash it's flushed (with fdatasync, only when 
the FS decides to do that), can anything bad happen without journaling?

Third, let's suppose that the WAL can't get corrupted. When the system flushes 
data pages to the disk according to the WAL content, if there is a crash, am I 
sure that tables files old pages and /or their metadata, inode can't get 
corrupted?
If that, there is no possibility to reconstruct the things, even through the 
WAL. Even in this case, perhaps journaling helps.

I don't mind about performance but I absolutely mind about reliability, so I 
was thinking about the safest setting of linux FS and postgresql I can use.
Thanks!
Pupillo






Il 15/10/2016 07:52, Michael Paquier ha scritto:
> On Fri, Oct 14, 2016 at 11:27 PM, Albe Laurenz  
> wrote:
>> After a successful commit, the WAL file and its metadata are on disk.
>> Moreover, the file metadata won't change (except for the write and 
>> access
>> timestamps) because WAL files are created with their full size and 
>> never extended, so no WAL file should ever get "lost" because of 
>> partial metadata writes.
> This behavior depends as well on the value of wal_sync_method. For 
> example with fdatasync the metadata is not flushed. It does not matter 
> any for for WAL segments as Albe has already mentioned, but the choice 
> here impacts performance.



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

Hi!
PG can lost its segments from data file and nobody knows it.   For PG  - no 
file = no data and no need to recover after crash, there is no infos about what 
data files belongs to PG.
After this don’t bother about WAL and anything else =)
Just use FS with journal, check sums you DB with initdb -k, fsync=on , do 
regular backups and check it thoroughly with restore. Also don’t forget to 
praise the gods that so far PG clogs file is not corrupted while being not 
protected by any checksums in minds. Youl never know that PG clog is corrupted 
until "doomsday"

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



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

Re: [GENERAL] Drop user cascade

2016-10-19 Thread Alex Ignatov (postgrespro) 

-Original Message-
From: [email protected]
[mailto:[email protected]] On Behalf Of Tom Lane
Sent: Wednesday, October 19, 2016 4:31 PM
To: Alex Ignatov (postgrespro) 
Cc: [email protected]
Subject: Re: [GENERAL] Drop user cascade

"Alex Ignatov \(postgrespro\)"  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 ([email protected]) 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Drop user cascade

2016-10-19 Thread Alex Ignatov (postgrespro) 
 

From: [email protected] 
[mailto:[email protected]] On Behalf Of Melvin Davidson
Sent: Wednesday, October 19, 2016 5:35 PM
To: Alex Ignatov (postgrespro) 
Cc: Tom Lane ; [email protected]
Subject: Re: [GENERAL] Drop user cascade

 

 

 

On Wed, Oct 19, 2016 at 10:03 AM, Alex Ignatov (postgrespro) 
mailto:[email protected]> > wrote:


-Original Message-
From: [email protected] 
<mailto:[email protected]> 
[mailto:[email protected] 
<mailto:[email protected]> ] On Behalf Of Tom Lane
Sent: Wednesday, October 19, 2016 4:31 PM
To: Alex Ignatov (postgrespro) 
Cc: [email protected] <mailto:[email protected]> 
Subject: Re: [GENERAL] Drop user cascade

"Alex Ignatov \(postgrespro\)" mailto:[email protected]> > 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 ([email protected] 
<mailto:[email protected]> ) 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 ([email protected] 
<mailto:[email protected]> )
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