Re: 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(at)svana(dot)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.

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

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

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

> 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

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
>

> 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

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

> 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

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

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

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

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.