Обсуждение: Permissions
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? Cheers Andre
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.
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.g.johnston@gmail.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.
Its customary to bottom (or inline) post of these mailing lists.
Hi DavidI 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.
MS...SQL Server?
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?
The "bootstrap" role (postgres) isn't special - just default. By "full permissions" do you mean "another superuser"? One superuser can indeed revoke or even drop another. All of them are equals in their ability to do anything to the cluster's databases and global objects.
[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?
You probably need to describe what you mean by "copy" here. If you were to dump a database and restore it into a different cluster any superusers defined in that new cluster would have unrestricted access to the newly restored database. At the same time, without special steps being taken there is no guarantee that such a single-database transfer would be usable by anyone defined on the new cluster since the roles from the old cluster may not exist on the new one.
David J.
Hi David
Once again - thanks a ton for taking the time to help me.
Yes MSSQL - we have done some benchmark tests with that as well. Not nice. Just our experience here. Others may differ.
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?
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.
Thanks for your patience.
Cheers
Andre
On 20 Sep 2016, at 21:52, David G. Johnston <david.g.johnston@gmail.com> wrote:Its customary to bottom (or inline) post of these mailing lists.Hi DavidI 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.MS...SQL Server?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?The "bootstrap" role (postgres) isn't special - just default. By "full permissions" do you mean "another superuser"? One superuser can indeed revoke or even drop another. All of them are equals in their ability to do anything to the cluster's databases and global objects.[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?You probably need to describe what you mean by "copy" here. If you were to dump a database and restore it into a different cluster any superusers defined in that new cluster would have unrestricted access to the newly restored database. At the same time, without special steps being taken there is no guarantee that such a single-database transfer would be usable by anyone defined on the new cluster since the roles from the old cluster may not exist on the new one.David J.
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.
On 20 Sep 2016, at 22:17, David G. Johnston <david.g.johnston@gmail.com> wrote:Please don't top-postOn 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
Sorry about the top posting. Still learning here.Hi DavidI 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?
No. Not knowing the name of the only super user might be inconvenient but its only security by obscurity. If they have unencrypted physical data files
they have the potential see all of the data contained therein. They don't even need passwords since they can just setup pg_hba.conf for trust access.
David J.
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>
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
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.
On 20 Sep 2016, at 22:48, David G. Johnston <david.g.johnston@gmail.com> wrote:Sorry about the top posting. Still learning here.Hi DavidI 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?No. Not knowing the name of the only super user might be inconvenient but its only security by obscurity. If they have unencrypted physical data filesthey have the potential see all of the data contained therein. They don't even need passwords since they can just setup pg_hba.conf for trust access.David J.
Hi David
Thanks for that. So PG de facto has absolutely no security while in transit then. That is what we are trying to establish.
Sybase supports this sort of security while in transit as does Mimer. There may be others.
Thanks for answering my questions.
Cheers
Andre
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-postDavid -- 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 atRe 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,DebraOn 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.
Hi Debra
Thanks for that intervention. If there is indeed a way of securing a backed up or stolen database while in transit our interest remains. Else we may need to look elsewhere. We cannot risk the data being stolen or hijacked on account of easy access. Will look at the links you posted. Thanks a ton.
Cheers
Andre
Thanks for that. So PG de facto has absolutely no security while in transit then. That is what we are trying to establish.
Your definition of "in transit" is unusual...someone obtaining a copy of a backup (or any data files) is generally considered "data at rest". Data in transit is stuff flowing on the wires when you, e.g., connect psql to the database and makes queries. The server is capable of leveraging SSL to setup secure tunnels for data in transit. The server does not itself encrypt data at rest whether it is the data files, WAL, or in-memory data buffers. Supplemental options in this area are present but I am unfamiliar with them.
David J.
On 20 Sep 2016, at 23:03, David G. Johnston <david.g.johnston@gmail.com> wrote:Thanks for that. So PG de facto has absolutely no security while in transit then. That is what we are trying to establish.Your definition of "in transit" is unusual...someone obtaining a copy of a backup (or any data files) is generally considered "data at rest". Data in transit is stuff flowing on the wires when you, e.g., connect psql to the database and makes queries. The server is capable of leveraging SSL to setup secure tunnels for data in transit. The server does not itself encrypt data at rest whether it is the data files, WAL, or in-memory data buffers. Supplemental options in this area are present but I am unfamiliar with them.David J.
Hi David
Our usage of the terms is the exact opposite.
I am simply referring to the database being taken else mounted and accused. We can refer to that as at rest. If we restrict access when it has “left” the initial PG server and mounted onto another PG server then we have a solution. But your reference to the little tool that enables trust seems to blow all security out of the water. It is troublesome.
Cheers
Andre
On Tue, Sep 20, 2016 at 11:17:47PM +0200, Andre Labuschagne wrote: > > > On 20 Sep 2016, at 23:03, David G. Johnston <david.g.johnston@gmail.com> wrote: > > > > On Tue, Sep 20, 2016 at 1:53 PM, Andre Labuschagne <technical@eduadmin.com <mailto:technical@eduadmin.com>> wrote: > > Thanks for that. So PG de facto has absolutely no security while in transit then. That is what we are trying to establish. > > > > ???Your definition of "in transit" is unusual...someone obtaining a copy of a backup (or any data files) is generallyconsidered "data at rest".??? Data in transit is stuff flowing on the wires when you, e.g., connect psql to thedatabase and makes queries. The server is capable of leveraging SSL to setup secure tunnels for data in transit. Theserver does not itself encrypt data at rest whether it is the data files, WAL, or in-memory data buffers. Supplementaloptions in this area are present but I am unfamiliar with them. > > > > David J. > > > > Hi David > > Our usage of the terms is the exact opposite. > > I am simply referring to the database being taken else mounted and accused. We can refer to that as at rest. If we restrictaccess when it has ???left??? the initial PG server and mounted onto another PG server then we have a solution. But your reference to the little tool that enables trust seems to blow all security out of the water. It is troublesome. Andre, There are plenty of non-Postgres solutions to the problem, such as hardware or volume encryption of the storage devices. If you're worried about security of your backups, then it's the job of your backup solution to encrypt the backups, not Postgres. Why should Postgres make a specific implementation of something that is generally available? -- -- Skylar Thompson (skylar2@u.washington.edu) -- Genome Sciences Department, System Administrator -- Foege Building S046, (206)-685-7354 -- University of Washington School of Medicine
Hi DavidOur usage of the terms is the exact opposite.I am simply referring to the database being taken else mounted and accused. We can refer to that as at rest. If we restrict access when it has “left” the initial PG server and mounted onto another PG server then we have a solution. But your reference to the little tool that enables trust seems to blow all security out of the water. It is troublesome.
There are many external tools that will encrypt files. You can also setup a filesystem that has encryption features. You don't necessarily need the full cooperation of PostgreSQL to make things meet your definition/trade-off of secure.
IMHO, The "little tool that enables trust" really isn't a problem by itself (and its not really a tool...) but rather has a slight impact of the potential risk surface and learning curve. It probably shouldn't be used in production but can come in handy in other setups. You've already lost once some gets a hold of unencrytped data files - a problem that can be readily solved outside of PostgreSQL - that its a bit easier to spin up the database and access the database is just opening the barn door a bit further.
There are many others on these lists, and in the community, more knowledgeable in security than I. It can be made considerably more secure than it comes "out of the box".
David J.
> On 20 Sep 2016, at 23:23, Skylar Thompson <skylar2@u.washington.edu> wrote: > > On Tue, Sep 20, 2016 at 11:17:47PM +0200, Andre Labuschagne wrote: >> >>> On 20 Sep 2016, at 23:03, David G. Johnston <david.g.johnston@gmail.com> wrote: >>> >>> On Tue, Sep 20, 2016 at 1:53 PM, Andre Labuschagne <technical@eduadmin.com <mailto:technical@eduadmin.com>> wrote: >>> Thanks for that. So PG de facto has absolutely no security while in transit then. That is what we are trying to establish. >>> >>> ???Your definition of "in transit" is unusual...someone obtaining a copy of a backup (or any data files) is generallyconsidered "data at rest".??? Data in transit is stuff flowing on the wires when you, e.g., connect psql to thedatabase and makes queries. The server is capable of leveraging SSL to setup secure tunnels for data in transit. Theserver does not itself encrypt data at rest whether it is the data files, WAL, or in-memory data buffers. Supplementaloptions in this area are present but I am unfamiliar with them. >>> >>> David J. >>> >> >> Hi David >> >> Our usage of the terms is the exact opposite. >> >> I am simply referring to the database being taken else mounted and accused. We can refer to that as at rest. If we restrictaccess when it has ???left??? the initial PG server and mounted onto another PG server then we have a solution. But your reference to the little tool that enables trust seems to blow all security out of the water. It is troublesome. > > Andre, > > There are plenty of non-Postgres solutions to the problem, such as hardware > or volume encryption of the storage devices. If you're worried about > security of your backups, then it's the job of your backup solution to > encrypt the backups, not Postgres. > > Why should Postgres make a specific implementation of something that is > generally available? > > -- > -- Skylar Thompson (skylar2@u.washington.edu) > -- Genome Sciences Department, System Administrator > -- Foege Building S046, (206)-685-7354 > -- University of Washington School of Medicine > > Hi Skylar We are talking about thousands of installations within the organisation. Ideally we need to allow the users at the installationsto be able to create their own databases and some of them we supply from head office. The ones we supply applicationswill be using. When the on site administrators use something like pgAdmin they must not be able to tamper withthe databases that we have supplied - no backing up or accessing and so on. Both Sybase and Mimer allow this as explicitlogin and password is required to each database, even if you are a super user. It is not just about backing up -it is the entire gambit. I was just trying to redirect the discussion when I referred to backing up as that is the obviousway for a rogue employee to make a copy of that data or stop the database and copy it manually. Hope that makes sense. Cheers Andre
On 20 Sep 2016, at 23:30, David G. Johnston <david.g.johnston@gmail.com> wrote:Hi DavidOur usage of the terms is the exact opposite.I am simply referring to the database being taken else mounted and accused. We can refer to that as at rest. If we restrict access when it has “left” the initial PG server and mounted onto another PG server then we have a solution. But your reference to the little tool that enables trust seems to blow all security out of the water. It is troublesome.There are many external tools that will encrypt files. You can also setup a filesystem that has encryption features. You don't necessarily need the full cooperation of PostgreSQL to make things meet your definition/trade-off of secure.IMHO, The "little tool that enables trust" really isn't a problem by itself (and its not really a tool...) but rather has a slight impact of the potential risk surface and learning curve. It probably shouldn't be used in production but can come in handy in other setups. You've already lost once some gets a hold of unencrytped data files - a problem that can be readily solved outside of PostgreSQL - that its a bit easier to spin up the database and access the database is just opening the barn door a bit further.There are many others on these lists, and in the community, more knowledgeable in security than I. It can be made considerably more secure than it comes "out of the box".David J.
Hi David
In the end to make this project practical we basically need to be able to create databases at HQ [and apps to go with them of course] and get the sites to mount the databases agains a PG server but not be able to tamper with the database in any form whatsoever - backing up [we will do that programmatically with pgDump for example] or accessing or whatever. That is what we are wanting to do. If this can be done it will of course cover theft as well as the same behaviour will apply to any other server.
The trust tool that you mentioned - is this universally available to any hacker or theft of the database? So a rogue employee can get their hands not? No special skill required?
Cheers
Andre
On Tuesday 20 September 2016 23:47:53 Andre Labuschagne wrote: > Hi Skylar > > We are talking about thousands of installations within the organisation. > Ideally we need to allow the users at the installations to be able to > create their own databases and some of them we supply from head office. > The ones we supply applications will be using. When the on site > administrators use something like pgAdmin they must not be able to tamper > with the databases that we have supplied - no backing up or accessing and > so on. Both Sybase and Mimer allow this as explicit login and password is > required to each database, even if you are a super user. PostgreSQL will definitely not help you with that. I find it hard to believe any database will allow you to ship a copy to a local admin but will have it somehow not be readable by them, but good luck with the commercial products if they claim to be able to.
> On 20 Sep 2016, at 23:58, Alan Hodgson <ahodgson@lists.simkin.ca> wrote: > > On Tuesday 20 September 2016 23:47:53 Andre Labuschagne wrote: >> Hi Skylar >> >> We are talking about thousands of installations within the organisation. >> Ideally we need to allow the users at the installations to be able to >> create their own databases and some of them we supply from head office. >> The ones we supply applications will be using. When the on site >> administrators use something like pgAdmin they must not be able to tamper >> with the databases that we have supplied - no backing up or accessing and >> so on. Both Sybase and Mimer allow this as explicit login and password is >> required to each database, even if you are a super user. > > PostgreSQL will definitely not help you with that. I find it hard to believe > any database will allow you to ship a copy to a local admin but will have it > somehow not be readable by them, but good luck with the commercial products if > they claim to be able to. > > > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice > > Hi Alan We have experience with both products I mentioned and have had experience with them for the last twenty years or so [beenin this game for 33 years now - from DOS1.0 and Unix and Zenix to Linux etc]. They do not claim and no luck is required. You better believe it. All permissions ship with the database. It works quite brilliantly as the only accessto the databases shipped is through the apps that ship and no tool will allow tampering with the objects that havebeen explicitly granted to specific users by the owners of the objects. It works very well. We even provide live backupapps that they can run - an app does that as well. They can copy the backed up files but cannot access them with anytool without being asked for a login and password. We were hoping we could achieve a similar thing with PG for a new project. We may have to stick with one of the other two. Cheers Andre
All permissions ship with the database. It works quite brilliantly as the only access to the databases shipped is through the apps that ship and no tool will allow tampering with the objects that have been explicitly granted to specific users by the owners of the objects. It works very well.
PostgreSQL has an entirely different architecture that is not particularly amenable to what you describe.
If the remote site admins have admin/root access on the machine hosting the PostgreSQL database you cannot prevent them from doing anything they want to the database. You might be able to compile a pre-shared key into the application and encrypt all data at rest. That is what these other tools would have to be doing otherwise your perception of encryption is broken since the admin could simply read the bytes off the disk and decompress/decode them into text (presuming said algorithm is discover-able).
As for the "trust tool"...just read the docs on pb_hba.conf
David J.
Hi Alan You better believe it. The best databases on the planet do allow that - Mimer, Sybase and now I am finding Interbase as well. These vendors saythe feature I request is security 101. There are no doubt others that I have not seen nor tested yet. But not allowingthat feature means there is de facto zero security. As it stands now PG has zero security. Cheers Andre > On 20 Sep 2016, at 23:58, Alan Hodgson <ahodgson@lists.simkin.ca> wrote: > > On Tuesday 20 September 2016 23:47:53 Andre Labuschagne wrote: >> Hi Skylar >> >> We are talking about thousands of installations within the organisation. >> Ideally we need to allow the users at the installations to be able to >> create their own databases and some of them we supply from head office. >> The ones we supply applications will be using. When the on site >> administrators use something like pgAdmin they must not be able to tamper >> with the databases that we have supplied - no backing up or accessing and >> so on. Both Sybase and Mimer allow this as explicit login and password is >> required to each database, even if you are a super user. > > PostgreSQL will definitely not help you with that. I find it hard to believe > any database will allow you to ship a copy to a local admin but will have it > somehow not be readable by them, but good luck with the commercial products if > they claim to be able to. > > > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice > >
Andre Labuschagne wrote: > You better believe it. > > The best databases on the planet do allow that - Mimer, Sybase and now I am finding Interbase as well. > These vendors say the feature I request is security 101. There are no doubt others that I have not > seen nor tested yet. But not allowing that feature means there is de facto zero security. As it > stands now PG has zero security. Harsh words. Since there was little context in the e-mail, I read the thread, and from what I read I understood that what you want is to backup a database at site A, ship it to site B and restore it there while making sure that nobody can tamper with the backup or steal it while it is being shipped. Is that correct? If yes, why not encrypt the backup before shipment? Any encryption tool like GnuPG (https://gnupg.org/) should be up to the task. It may well be that many databases have features that allow you to encrypt a backup with on-board tools, but PostgreSQL doesn't have the manpower to write and maintain paraphernalia like that with with the core distribution. Yours, Laurenz Albe
Hi Albe Nothing to do with encryption. This is what the databases mentioned allow you to do. Set a user name and password thatonly the owner of the database knows about. No super user can gain access to the database no matter where or how itis shipped. It is that simple - the user name and password must reside with the database and only that users name andpassword can access that database no matter where or how it is shipped. Mounting it onto another PG server must not makeit accessible to the super user on that server. That is what I am talking about. The three databases I mentioned workexactly like that - I am sure there are others that do so as well. It is security 101. If you can do that with PG I am all ears. I am really wanting to use PG but this single apparent deficiency is stoppingme in my tracks. Cheers Andre > On 5 Oct 2016, at 10:04, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > > Andre Labuschagne wrote: >> You better believe it. >> >> The best databases on the planet do allow that - Mimer, Sybase and now I am finding Interbase as well. >> These vendors say the feature I request is security 101. There are no doubt others that I have not >> seen nor tested yet. But not allowing that feature means there is de facto zero security. As it >> stands now PG has zero security. > > Harsh words. > > Since there was little context in the e-mail, I read the thread, and from what I read > I understood that what you want is to backup a database at site A, ship it to site B > and restore it there while making sure that nobody can tamper with the backup or steal it > while it is being shipped. > > Is that correct? > > If yes, why not encrypt the backup before shipment? > Any encryption tool like GnuPG (https://gnupg.org/) should be up to the task. > > It may well be that many databases have features that allow you to encrypt a backup > with on-board tools, but PostgreSQL doesn't have the manpower to write and maintain paraphernalia > like that with with the core distribution. > > Yours, > Laurenz Albe
Please don't top-post on PostgreSQL mailing lists. Andre Labuschagne wrote: >>> The best databases on the planet do allow that - Mimer, Sybase and now I am finding Interbase as well. > Nothing to do with encryption. This is what the databases mentioned allow you to do. Set a user name > and password that only the owner of the database knows about. No super user can gain access to the > database no matter where or how it is shipped. It is that simple - the user name and password must > reside with the database and only that users name and password can access that database no matter > where or how it is shipped. Mounting it onto another PG server must not make it accessible to the > super user on that server. That is what I am talking about. The three databases I mentioned work > exactly like that - I am sure there are others that do so as well. It is security 101. Well, if somebody who has access to the files cannot get the contents, that is by definition encryption, right? If the data files are not encrypted, anybody who can read them can get at the data. > If you can do that with PG I am all ears. I am really wanting to use PG but this single apparent > deficiency is stopping me in my tracks. Are you trying to find a working solution or just trying to tell us that PostgreSQL stinks because it lacks your favourite feature? For a seasoned old hand like you it should be a trifle to encrypt a backup with GnuPG and send it like that. That way nobody but the intended recipient can get at the data. Yours, Laurenz Albe
> On 5 Oct 2016, at 13:09, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > > Please don't top-post on PostgreSQL mailing lists. > > Andre Labuschagne wrote: >>>> The best databases on the planet do allow that - Mimer, Sybase and now I am finding Interbase as well. > >> Nothing to do with encryption. This is what the databases mentioned allow you to do. Set a user name >> and password that only the owner of the database knows about. No super user can gain access to the >> database no matter where or how it is shipped. It is that simple - the user name and password must >> reside with the database and only that users name and password can access that database no matter >> where or how it is shipped. Mounting it onto another PG server must not make it accessible to the >> super user on that server. That is what I am talking about. The three databases I mentioned work >> exactly like that - I am sure there are others that do so as well. It is security 101. > > Well, if somebody who has access to the files cannot get the contents, > that is by definition encryption, right? If the data files are not encrypted, > anybody who can read them can get at the data. > >> If you can do that with PG I am all ears. I am really wanting to use PG but this single apparent >> deficiency is stopping me in my tracks. > > Are you trying to find a working solution or just trying to tell us that > PostgreSQL stinks because it lacks your favourite feature? > > For a seasoned old hand like you it should be a trifle to encrypt a backup > with GnuPG and send it like that. That way nobody but the intended recipient > can get at the data. > > Yours, > Laurenz Albe Hi Albe Apologies for top-posting. I keep forgetting with these mailing lists. I am seriously looking for a working solution with PG. This is not just my favourite feature. It is the only thing thatactually provides any form of security. Anything else leaves a gaping hole at the weakest point - the so-called superuser. Just because PG does not have decent security does not mean it stinks for all situations. There are some where securityis not required at all. For this project we are investigating serious security such as I have described is not negotiable. Encryption is meaningless if the super user can control the encrypting. What is required is the following: the super usergrants a user the rights to create a database and all objects within the database. The super user simply grants theuser that right. The super user has zero access to what that user creates unless that user explicitly grants the superuser those rights. That is called security. That is what I am trying to achieve with PG. I was hoping that it ispossible to do such a thing. That is what Mimer, Sybase and Interbase [and perhaps others I am yet to encounter] do asa matter of course. It as necessary for the security of a database as wheels are to a car. Cheers Andre
> On 5 Oct 2016, at 14:06, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > > Andre Labuschagne wrote: >> Encryption is meaningless if the super user can control the encrypting. What is required is the >> following: the super user grants a user the rights to create a database and all objects within the >> database. The super user simply grants the user that right. The super user has zero access to what >> that user creates unless that user explicitly grants the super user those rights. That is called >> security. That is what I am trying to achieve with PG. I was hoping that it is possible to do such a >> thing. That is what Mimer, Sybase and Interbase [and perhaps others I am yet to encounter] do as a >> matter of course. It as necessary for the security of a database as wheels are to a car. > > If you need exactly that feature, you are probably happier with a different database > system, because PostgreSQL doesn't have it and probably never will. > > Most people would argue that this is no hard security, it only makes the attack > more complicated. As a database superuser I can access files on the file system > in any database I ever heard of, thus I can read the files containing the tables, > thus I can figure out what is in them. > > Yours, > Laurenz Albe Hi With this project we are looking at you would be the potential problem. You would fit it perfectly. And you would be theguy we could not have anywhere near the data. I am obviously not referring to you personally but the access you wouldhave. I understand from this year’s PG conference that this very topic was discussed and the need was acknowledged so it may yetbe supported in a future version of PG. For many projects it is a very serious weakness in the engine and why PG couldnever be considered for them. A great pity when it comes to the project we are looking at as in other respects it reallyis a fine database with everything else stacking up excellently. Cheers Andre
Andre Labuschagne wrote: > Encryption is meaningless if the super user can control the encrypting. What is required is the > following: the super user grants a user the rights to create a database and all objects within the > database. The super user simply grants the user that right. The super user has zero access to what > that user creates unless that user explicitly grants the super user those rights. That is called > security. That is what I am trying to achieve with PG. I was hoping that it is possible to do such a > thing. That is what Mimer, Sybase and Interbase [and perhaps others I am yet to encounter] do as a > matter of course. It as necessary for the security of a database as wheels are to a car. If you need exactly that feature, you are probably happier with a different database system, because PostgreSQL doesn't have it and probably never will. Most people would argue that this is no hard security, it only makes the attack more complicated. As a database superuser I can access files on the file system in any database I ever heard of, thus I can read the files containing the tables, thus I can figure out what is in them. Yours, Laurenz Albe