Обсуждение: pg_hba.conf alternative
Hello, Is there not some other alternative to pg_hba.conf? I have the problem where the system administrators at our company obviously have access to the whole filesystem, and our database records needs to be hidden even from them. With pg_hba.conf that is not possible, as they just change all the conf lines to "trust" auth and viola they have access to the database without passwords. Is there a more secure alternative to this? The perfect scenario being to deny everyone include "root" access to a database without a password. regards, Quintin Beukes
Hi, On Wed, 2006-02-08 at 14:34 +0200, Q Beukes wrote: > I have the problem where the system administrators at our company > obviously have access to the whole filesystem, and our database records > needs to be hidden even from them. As they have access to whole filesystem, they can access anything, even if you enable password auth (they'd switch to trust auth and reload postmaster). They can also copy the data dir to another server and search the You should either trust your sysadms, or work with people who you trust. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
I think this was discussed many times on this list, and the main conclusion was: if you don't trust your DB machine's admin, any security measure against him will be only illusory. The sysadmin can in any case access the data, you can just make this harder, you can't prevent that. So you better get admins who you trust... On Wed, 2006-02-08 at 13:34, Q Beukes wrote: > Hello, > > Is there not some other alternative to pg_hba.conf? > > I have the problem where the system administrators at our company > obviously have access to the whole filesystem, and our database records > needs to be hidden even from them. > > With pg_hba.conf that is not possible, as they just change all the conf > lines to "trust" auth and viola they have access to the database without > passwords. > > Is there a more secure alternative to this? The perfect scenario being > to deny everyone include "root" access to a database without a password. > > regards, > Quintin Beukes > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
On Wed, Feb 08, 2006 at 02:34:29PM +0200, Q Beukes wrote: > Is there not some other alternative to pg_hba.conf? > > I have the problem where the system administrators at our company > obviously have access to the whole filesystem, and our database records > needs to be hidden even from them. > > With pg_hba.conf that is not possible, as they just change all the conf > lines to "trust" auth and viola they have access to the database without > passwords. Or they just copy the whole database to another machine and access it that way. Or copy your backups. Or hack the application accessing the data (the application has the password in it, right?). If can stop them doing those things you can stop them altering pg_hba.conf too so your problem is solved. > Is there a more secure alternative to this? The perfect scenario being > to deny everyone include "root" access to a database without a password. Well, you could change the source to remove struct auth, but then they'd just compile their own version and overwrite the system one. Yes, we're looking for alternatives for pg_hba.conf, but what you want is to dam a river with sheets of paper. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
> Hello, > > Is there not some other alternative to pg_hba.conf? > > I have the problem where the system administrators at our company > obviously have access to the whole filesystem, and our database records > needs to be hidden even from them. If they have full access, then they have FULL access. > > With pg_hba.conf that is not possible, as they just change all the conf > lines to "trust" auth and viola they have access to the database without > passwords. You are looking for a security that can not exit in your scenario. > > Is there a more secure alternative to this? The perfect scenario being > to deny everyone include "root" access to a database without a password. > They only way to secure data is to remove all access to it. If you don't trust your admins, then you have the wrong admins.
Q Beukes wrote: >Hello, > >Is there not some other alternative to pg_hba.conf? > >I have the problem where the system administrators at our company >obviously have access to the whole filesystem, and our database records >needs to be hidden even from them. > >With pg_hba.conf that is not possible, as they just change all the conf >lines to "trust" auth and viola they have access to the database without >passwords. > >Is there a more secure alternative to this? The perfect scenario being >to deny everyone include "root" access to a database without a password. > > > > This is an illusion, as plenty of security experts will tell you. Password auth is a losing game for high security in the first place. So this comment shows that you haven't thought this out properly. If you want the data hidden from system administrators, you need to have the client encrypt it before storing it. Of course, that will have massive implications for your application. There are no simple solutions. See here for why: http://www.acm.org/classics/sep95/ cheers andrew
> > > Q Beukes wrote: > >>Hello, >> >>Is there not some other alternative to pg_hba.conf? >> >>I have the problem where the system administrators at our company >>obviously have access to the whole filesystem, and our database records >>needs to be hidden even from them. >> >>With pg_hba.conf that is not possible, as they just change all the conf >>lines to "trust" auth and viola they have access to the database without >>passwords. >> >>Is there a more secure alternative to this? The perfect scenario being >>to deny everyone include "root" access to a database without a password. >> >> >> >> > > This is an illusion, as plenty of security experts will tell you. > Password auth is a losing game for high security in the first place. So > this comment shows that you haven't thought this out properly. > > If you want the data hidden from system administrators, you need to have > the client encrypt it before storing it. Of course, that will have > massive implications for your application. And even then, your admins will probably have access to the application source and, if they want, can get data. The unpopular reality is that if you must keep something secret, you can't give access to it to anyone who is not trusted to keep the secret. The best bet is to have one system that has the "secret" data, managed by those who are trusted. It means that the "trusted" people are on the hook for backups and preventive maintenence, but secrets aren't free.
Well, I am not looking for 100% security. I know that full access if full access, and that even if you were to encrypt the system through Postgre the determined person WILL always be able to get it out if they have system level access. All I wanted to do was to prevent the basic SQL/Linux literate user from accessing the databases. At the moment it is very easy for them to access the data. I trust that they wont go as far as overwriting the system with custom compiled version, or copying the data and so forth. It just that we would feel much better if we knew the data wasn't as open as it is now, with a simple pg restart it is all open? Can this only be done by maybe modifying the source to make pg_hba fields statically compiled into the executable? Martijn van Oosterhout wrote: >On Wed, Feb 08, 2006 at 02:34:29PM +0200, Q Beukes wrote: > > >>Is there not some other alternative to pg_hba.conf? >> >>I have the problem where the system administrators at our company >>obviously have access to the whole filesystem, and our database records >>needs to be hidden even from them. >> >>With pg_hba.conf that is not possible, as they just change all the conf >>lines to "trust" auth and viola they have access to the database without >>passwords. >> >> > >Or they just copy the whole database to another machine and access it >that way. Or copy your backups. Or hack the application accessing the >data (the application has the password in it, right?). > >If can stop them doing those things you can stop them altering >pg_hba.conf too so your problem is solved. > > > >>Is there a more secure alternative to this? The perfect scenario being >>to deny everyone include "root" access to a database without a password. >> >> > >Well, you could change the source to remove struct auth, but then they'd >just compile their own version and overwrite the system one. > >Yes, we're looking for alternatives for pg_hba.conf, but what you want >is to dam a river with sheets of paper. > >Have a nice day, > >
Q Beukes wrote: >Well, > >I am not looking for 100% security. I know that full access if full access, >and that even if you were to encrypt the system through Postgre the >determined >person WILL always be able to get it out if they have system level access. > >All I wanted to do was to prevent the basic SQL/Linux literate user from >accessing >the databases. At the moment it is very easy for them to access the data. > mechanism is there for a reason: >I trust that they wont go as far as overwriting the system with custom >compiled >version, or copying the data and so forth. It just that we would feel >much better >if we knew the data wasn't as open as it is now, with a simple pg >restart it is all >open? > >Can this only be done by maybe modifying the source to make pg_hba >fields statically >compiled into the executable? > > Of course it would be possible to hardcode the values - it's a SMOC. But nobody round here is likely to do the work reuired, since nobody believes it's worth doing, I believe. This mechanism you object to is there for a reason: if you lock yourself out of the database you can recover from the error. The solution you are proposing is therefore a huge footgun. And your user with basic linux/sql knowledge would still be able to see data fly by, for example, logging statements, or watching network traffic. How hard is it to run ethereal, after all, or tail a log file? There is even a module for ethereal that understands the postgres wire protocol. You aren't asking for security - you are asking for the illusion of security, which many would argue is worse than no security at all. cheers andrew
Q Beukes schrieb: > Well, > > I am not looking for 100% security. I know that full access if full access, > and that even if you were to encrypt the system through Postgre the > determined > person WILL always be able to get it out if they have system level access. > > All I wanted to do was to prevent the basic SQL/Linux literate user from > accessing > the databases. At the moment it is very easy for them to access the data. > > I trust that they wont go as far as overwriting the system with custom > compiled > version, or copying the data and so forth. It just that we would feel > much better > if we knew the data wasn't as open as it is now, with a simple pg > restart it is all > open? > > Can this only be done by maybe modifying the source to make pg_hba > fields statically > compiled into the executable? > Instead, you might want to read about SELinux. You can protect files even to root (unless they reboot ;) but really you should have only trusted people have admin accounts. How comes you have somebody untrusted as admin? Regards Tino
To give it to you straight... its just to ease the minds of management. Someone pointed out to them how easy it really is to access the data, and this kind of started to make them feel uncomfortable. They know the admins are very computer literate and that any protection can be broken by them. But it's just like information locked inside a cabinet, it can be accessed by breaking in right? But employees wont do it, because it's just not ethical to break into your employers private stash. But if it was lying on a paper on a desk somewhere, even the most honest employee might peek onto it for interest sake. And this type of information can stir quite a bit, trust me. That is all I was wondering about, if there was a way to just lock it inside a cabinet with a tiny bit more security. After that you can always take measures to make sure they aren't installing malicious software, or taking information home. You can install software like Tripwire to make sure the binaries are kept fine, remove gcc and so forth. Tino Wildenhain wrote: > Q Beukes schrieb: > >> Well, >> >> I am not looking for 100% security. I know that full access if full >> access, >> and that even if you were to encrypt the system through Postgre the >> determined >> person WILL always be able to get it out if they have system level >> access. >> >> All I wanted to do was to prevent the basic SQL/Linux literate user from >> accessing >> the databases. At the moment it is very easy for them to access the >> data. >> >> I trust that they wont go as far as overwriting the system with custom >> compiled >> version, or copying the data and so forth. It just that we would feel >> much better >> if we knew the data wasn't as open as it is now, with a simple pg >> restart it is all >> open? >> >> Can this only be done by maybe modifying the source to make pg_hba >> fields statically >> compiled into the executable? >> > Instead, you might want to read about SELinux. > You can protect files even to root (unless they > reboot ;) but really you should have only trusted > people have admin accounts. How comes you have > somebody untrusted as admin? > > Regards > Tino >
> If you want the data hidden from system administrators, you need to have
> the client encrypt it before storing it. Of course, that will have
> massive implications for your application.
Have you considered storing your data on an encrypted filesystem? I have no
idea what kind of performance hit you would suffer, but you wouldn't have to
change your application at all that way. Perhaps a private mount so that
only the postgresql process tree could see the decrypted bits?
-- Korry
korry wrote: >>If you want the data hidden from system administrators, you need to have >>the client encrypt it before storing it. Of course, that will have >>massive implications for your application. >> >> > >Have you considered storing your data on an encrypted filesystem? I have no >idea what kind of performance hit you would suffer, but you wouldn't have to >change your application at all that way. Perhaps a private mount so that >only the postgresql process tree could see the decrypted bits? > > > Since what he is worried about is the ability of admins to get at the data by connecting to the postgres server (after changing pg_hba.conf), this will not make the slightest difference - the data would be decrypted before it ever got to the intruder. For encryption to be effective against some perceived threat, the data has to be encrypted before it gets anywhere the spy can see it. There really are no magic solutions. Unfortunately, there is not a similar shortage of snake oil. cheers andrew
> Since what he is worried about is the ability of admins to get at the
> data by connecting to the postgres server (after changing pg_hba.conf),
> this will not make the slightest difference - the data would be
> decrypted before it ever got to the intruder.
I was suggesting that pg_hba.conf could be stored in the same encrypting
filesystem.
-- Korry
korry wrote: >>Since what he is worried about is the ability of admins to get at the >>data by connecting to the postgres server (after changing pg_hba.conf), >>this will not make the slightest difference - the data would be >>decrypted before it ever got to the intruder. >> >> > >I was suggesting that pg_hba.conf could be stored in the same encrypting >filesystem. > > > > Then how can it be changed? What if you need to allow access from, say, another user or another network? Oh, the admins have to change it ... In the end you have to trust your admins or fire them and hire some you do trust. cheers andrew
> >I was suggesting that pg_hba.conf could be stored in the same encrypting
> >filesystem.
>
> Then how can it be changed? What if you need to allow access from, say,
> another user or another network? Oh, the admins have to change it ...
Not all admins are equal... the admin that takes care of the database would
obviously have the decrypt password for the encrypting filesystem. That
admin (but not other admins) can change the pg_hba.conf file.
-- Korry
korry wrote: >>>I was suggesting that pg_hba.conf could be stored in the same encrypting >>>filesystem. >>> >>> >>Then how can it be changed? What if you need to allow access from, say, >>another user or another network? Oh, the admins have to change it ... >> >> > >Not all admins are equal... the admin that takes care of the database would >obviously have the decrypt password for the encrypting filesystem. That >admin (but not other admins) can change the pg_hba.conf file. > > > > Why would you not simply set this up on a seperate machine to which only the trusted admins had access? Most data centers I am familiar with use single purpose machines anyway. If someone is trusted as root on your box they can screw you no matter what you do. Pretending otherwise is just folly. cheers andrew
> Why would you not simply set this up on a seperate machine to which only
> the trusted admins had access? Most data centers I am familiar with use
> single purpose machines anyway. If someone is trusted as root on your
> box they can screw you no matter what you do. Pretending otherwise is
> just folly.
Agreed - that would be a much better (easier and more secure) solution where
practical.
-- Korry
I did consider that, but the software we use (which again uses postgresql) atm only supports local connection to the database. I am the database admin, the other admins just manage stuff like user accounts, checking logs, etc... Unfortunately there is no other way to set it up, and like I mentioned government security is not required. I did however statically code the pg_hba.conf file into pg binaries. The only way I found to access the db now would be to replace the binary and possibly sniffing traffic. But we're not worried about that. They not really criminally minded people. thx for everyones help anyway ;> korry wrote: >>Why would you not simply set this up on a seperate machine to which only >>the trusted admins had access? Most data centers I am familiar with use >>single purpose machines anyway. If someone is trusted as root on your >>box they can screw you no matter what you do. Pretending otherwise is >>just folly. >> >> > >Agreed - that would be a much better (easier and more secure) solution where >practical. > > -- Korry > >---------------------------(end of broadcast)--------------------------- >TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > >
But why do they need access to the files in the file system? Why not put them on the local box but don't give them permissions to edit the pg_hba file? They should still be able to connect. On Feb 9, 2006, at 5:56 PM, Q Beukes wrote: > I did consider that, but the software we use (which again uses > postgresql) > atm only supports local connection to the database. > > I am the database admin, the other admins just manage stuff like user > accounts, > checking logs, etc... > > Unfortunately there is no other way to set it up, and like I mentioned > government security is not required. > > I did however statically code the pg_hba.conf file into pg binaries. > > The only way I found to access the db now would be to replace the > binary > and > possibly sniffing traffic. But we're not worried about that. They > not really > criminally minded people. > > thx for everyones help anyway ;> > > > korry wrote: > >>> Why would you not simply set this up on a seperate machine to >>> which only >>> the trusted admins had access? Most data centers I am familiar >>> with use >>> single purpose machines anyway. If someone is trusted as root on >>> your >>> box they can screw you no matter what you do. Pretending >>> otherwise is >>> just folly. >>> >>> >> >> Agreed - that would be a much better (easier and more secure) >> solution where >> practical. >> >> -- Korry >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 3: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faq >> >> >> > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
how? is there some kernel patch to completely to enable you to deny access to root? Tino Wildenhain pointed out SELinux has a feature like that. Rick Gigger wrote: > But why do they need access to the files in the file system? Why not > put them on the local box but don't give them permissions to edit the > pg_hba file? They should still be able to connect. > > On Feb 9, 2006, at 5:56 PM, Q Beukes wrote: > >> I did consider that, but the software we use (which again uses >> postgresql) >> atm only supports local connection to the database. >> >> I am the database admin, the other admins just manage stuff like user >> accounts, >> checking logs, etc... >> >> Unfortunately there is no other way to set it up, and like I mentioned >> government security is not required. >> >> I did however statically code the pg_hba.conf file into pg binaries. >> >> The only way I found to access the db now would be to replace the >> binary >> and >> possibly sniffing traffic. But we're not worried about that. They >> not really >> criminally minded people. >> >> thx for everyones help anyway ;> >> >> >> korry wrote: >> >>>> Why would you not simply set this up on a seperate machine to >>>> which only >>>> the trusted admins had access? Most data centers I am familiar >>>> with use >>>> single purpose machines anyway. If someone is trusted as root on your >>>> box they can screw you no matter what you do. Pretending otherwise is >>>> just folly. >>>> >>>> >>> >>> Agreed - that would be a much better (easier and more secure) >>> solution where >>> practical. >>> >>> -- Korry >>> >>> ---------------------------(end of >>> broadcast)--------------------------- >>> TIP 3: Have you checked our extensive FAQ? >>> >>> http://www.postgresql.org/docs/faq >>> >>> >>> >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 4: Have you searched our list archives? >> >> http://archives.postgresql.org >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
Q Beukes schrieb: > how? is there some kernel patch to completely to enable you to deny > access to root? > Tino Wildenhain pointed out SELinux has a feature like that. I still dont get your problem (apart from that you can always google for SELinux) Why arent the other "admins" not trustworthy? And why do you have many of them? If they only check logs and create users, why do they have to be admins? They could use carefully configured sudo as well to fullfill their tasks w/o full access to the system. I'd say, grep your problem at the root (literally spoken) Regards Tino
>> how? is there some kernel patch to completely to enable you to deny >> access to root? >> Tino Wildenhain pointed out SELinux has a feature like that. > > I still dont get your problem (apart from that you can always > google for SELinux) > > Why arent the other "admins" not trustworthy? And why do you > have many of them? If they only check logs and create users, > why do they have to be admins? They could use carefully > configured sudo as well to fullfill their tasks w/o full > access to the system. > > I'd say, grep your problem at the root (literally spoken) Yes. Exactly. I guess I misunderstood the situation. Admin is vague word. It could mean db admins, it could mean a system administrator for that computer etc. I apologize if that was specified earlier in the discussion. I just assumed that if you didn't want them to be able to edit the conf file that they wouldn't have root because well... that just seems obvious. I realize though that you don't need real security but rather a small barrier to give the management the warm fuzzies. I'm sure that you have your reasons but if you could make them non- root users and give them privileges to do what they need to do with sudo or something but not give them perms on the hba file then that would seem to be a better solution all around than compiling your own custom postgres. Just a suggestion.