Re: Permissions - Mailing list pgsql-novice

On 20 Sep 2016, at 22:50, Debra Cerda <Debra.Cerda@bluetreble.com> wrote:

From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Tuesday, September 20, 2016 3:17 PM
Subject: Re: [NOVICE] Permissions
To: Andre Labuschagne <technical@eduadmin.com>
Cc: <pgsql-novice@postgresql.org>

Please don't top-post

David -- Thanks for explaining that to us noobs on the [NOVICE] mailing list. Is this respond format acceptable? I may be missing a setting in Outlook that formats appropriately? 

Andre --

 I was not aware that it was considered inappropriate to top post either. For your reference, I located the mailing list protocol that states "our community generally does not "top post" in response to mailing list threads."  More info at 

https://wiki.postgresql.org/wiki/Mailing_Lists

Re permissions, there were a couple sessions at PostgresOpen last week that addressed permissions, including one specifically on that topic from Nathan Wagner.  

Another useful presentation was "Assume database credentials have leaked: Dynamic Databases" from Sean Chittenden A link to that talk has already been posted at https://wiki.postgresql.org/wiki/Postgres_Open_2016. More talks and videos will be uploaded soon. 

Good luck with your project!

Sincerely,

Debra

On Tue, Sep 20, 2016 at 1:04 PM, Andre Labuschagne <technical@eduadmin.com> wrote:

Would I be correct in saying then that all roles attached to the database at the source PG server will travel to the PG server that is going to mount the copy.  What I mean by copy is to just copy the physical files  I understand the pgDump can dump the database to a sort of archive to a script.  Is that correct?

A full physical copy of the relevant files from a stopped PostgreSQL is basically making an exact backup.  When you start/restore it you get exactly what you backed up.  This generally means "everything" and not just "a database".

 

If the super user has unrestricted access to the database on the destination PG server how would the non existence of roles restrict that access?  This is not what I am understanding.  That is why I asked about revoking the super user at the source PG server.  This is what is confusing me.

You need to describe your setup better.  You either do a complete clone - in which case there is no existing super user and you get all of the original roles - or you import into an existing cluster - in which case you will have an existing super user and need to make sure that you remember to load in all of the original roles.

​

​David J.​