Re: Permissions

Hi David

I am a complete newbie to PG but have managed to hammer it a bit over the last week or so. The metrics are impressive. It seems to be way faster than MS - as much as five times maybe.

For this project the OS will be Windows servers.

What we are specifically concerned about is the data while in transit. Please forgive me if I am going to sound simple here but I will describe the situation by asking questions.

I have installed PG and was asked for a password. I provided one and then created the database and created a role for it with full permissions. It obviously also has a separate password. Now here are my simple questions:

[1] Can the new role revoke the super user permissions?

[2] If I copy the database and take it to another PG server and mount it does the superuser on that PG server automatically have full rights to this database?

More questions to come but that is where I need to start.

Thanks for chipping in and taking the time to help me with this decision.

Cheers
Andre

> On 20 Sep 2016, at 20:36, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Tue, Sep 20, 2016 at 11:27 AM, Andre Labuschagne <technical(at)eduadmin(dot)com <mailto:technical(at)eduadmin(dot)com>> wrote:
> Hi All
>
> We are looking at using PG for a project. The critical thing with this project is that only the owner of the database created must have access to it. There must be no superuser that can access it. Is this possible with PG?
>
> ​That level of security requires that the O/S and physical access to the listening socket be restricted. Within PostgreSQL itself a cluster superuser (all users are defined at the cluster level, not individual databases) can do anything to any of the databases within the cluster.
>
> In short, what you want might be possible but more detailed guidance would require specifics about the O/S and exactly what level of "superuser" access you are concerned about.
>
> You would most likely need to create a cluster where the only user is the one in question and is made a superuser themselves - they can then choose to create more restricted roles if they desire. Any setup where the database owner is not themselves a superuser is not going to work - since every cluster must have at least one superuser./
>
> David J.