Postgres Pro
Downloads
Home   >   mailing lists

Re: Permissions - Mailing list pgsql-novice

From Andre Labuschagne
Subject Re: Permissions
Date
Msg-id 80F65ECE-2933-447C-B124-C3B11A16EB06@eduadmin.com
Whole thread Raw
In response to Re: Permissions  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: Permissions
List pgsql-novice
Tree view

On 20 Sep 2016, at 22:17, David G. Johnston <david.g.johnston@gmail.com> wrote:

Please don't top-post

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.​


Sorry about the top posting.  Still learning here.

Hi David

I am not making myself clear.  Let us try another angle.  We are concerned about security breaches and database theft within and outside the organisation.  Assuming a rogue employee gets their hands on a full backup of one of the databases and they did not have the details of the only role that is listed as having privileges would this employee be able to download PG set it up on his or computer, provide a superuser password and then have full access to the database?  Is there a way to prevent this access? 

Cheers
Andre

pgsql-novice by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: Permissions
Next
From: "David G. Johnston"
Date:
Subject: Re: Permissions