Securing Postgres

L van der Walt wrote:

I would like to secure Postgres completly.

Some issues that I don't know you to fix:
1. User postgres can use psql (...) to do anything.

Prevent anyone from logging in as user postgres.
Remove psql.

2. User root can su to postgres and thus do anything.

That's the root user - it is supposed to be able to do what it likes.

3. Disable all tools like pg_dump

You can delete the executables, but that's not going to stop people
running their own version if they can connect.

How do I secure a database if I don't trust the administrators.
The administrator will not break the db but they may not view
any information in the databse.

If you don't trust the administrators of the machine, there's nothing
you can do if they have physical access to it. They'll always be able to
work around anything you can do.

Can you say more about the situation - it might be someone has been in a
similar situation themselves?
--
Richard Huxton
Archonet Ltd

L van der Walt wrote:

I would like to secure Postgres completly.

Some issues that I don't know you to fix:
1. User postgres can use psql (...) to do anything.
2. User root can su to postgres and thus do anything.
3. Disable all tools like pg_dump

How do I secure a database if I don't trust the administrators.
The administrator will not break the db but they may not view
any information in the databse.

It may be just me and my silly old-fashion attitudes, but I kind of
think that if your sys admin(s) cannot be trusted, you are pretty much
screwed. And your hiring process needs fixing,

But being that as it may, maintaining physical security, i.e., keeping
the host server in a locked room with restricted and recorded access and
that requires at least two persons present so that collusion is required
for tampering, disabling remote root login, granting limited sys admin
privileges with sudo (which records the sudoer activities, for auditing
purposes) might be a way to accomplish what you are looking for.

Don't forget to CC: the list!

L van der Walt wrote:

Example: On a MS Windows Server with MS SQL Server. The administrator
with the administrator username and password can not access the SQL
server data. He also needs the SA username and password for the SQL
server to do so. He can stop and start the server and so on but not
access the data.

He might not be able to directly access the DB, but he can certainly
gain access to the raw data files/backups/passwords and gain access that
way.

How do I secure a system in the same way with Linux and PostgreSQL.

Unix security is a big topic, but basically if someone has root access,
then they can gain access to anything on that machine.

However, you can make it harder by requiring passwords for PG and not
storing them on the machine (other than in their hashed form within the
database). Of course that means you'll need to supply a password for any
automatic tasks (e.g. autovacuum etc) which I don't see as being easy if
you don't store them on the same machine.

But basically, you need to be able to trust the person with the root
login - it is more powerful than a standard MS-Windows administrator
account. Do your administrators need root access?
--
Richard Huxton
Archonet Ltd

Berend Tober wrote:

L van der Walt wrote:

I would like to secure Postgres completly.

Some issues that I don't know you to fix:
1. User postgres can use psql (...) to do anything.
2. User root can su to postgres and thus do anything.
3. Disable all tools like pg_dump

How do I secure a database if I don't trust the administrators.
The administrator will not break the db but they may not view
any information in the databse.

It may be just me and my silly old-fashion attitudes, but I kind of
think that if your sys admin(s) cannot be trusted, you are pretty much
screwed. And your hiring process needs fixing,

But being that as it may, maintaining physical security, i.e., keeping
the host server in a locked room with restricted and recorded access
and that requires at least two persons present so that collusion is
required for tampering, disabling remote root login, granting limited
sys admin privileges with sudo (which records the sudoer activities,
for auditing purposes) might be a way to accomplish what you are
looking for.

Then, I might as well just leave the whole PostgreSQL DB and write my
own mini DB with encrypted XML files. I am sure someone must have an
answer for me.

The big problem is that the administrators works for the client and not
for me. I don't want the client to reverse engineer my database.

There might be other applications on the server so the administrators do
require root access.

About the raw database files, I can use encryption to protect the data.

Richard Huxton wrote:

If you don't trust the administrators you should find someone else to admin
your machine.

Main question: what do you need the administrators to do for you? If you
only need them to do a few things, then it is much easier to limit their
access.

Because, on most popular systems (e.g. C2-level O/S + hardware or lower)
technically competent administrators with _full_ administrative accounts
and/or physical access are able to get full read access to practically any
file. So they will be able to view information in the database.

Backup operators are often able to get similar access.

You could try to use encryption, but in my opinion that is more for making
_accidental_ viewing of data harder, and to make it easier to prove that
intentional breaches of policy were attempted.

BTW you may wish to put dummy data in your database (fake accounts, etc),
so that if there is a compromise (or an attempt in progress), you might
have a chance of detecting it. Some triggers might help too (don't use them
on _all_ your fake data, just a few might do).

Regards,
Link.

At 03:36 PM 10/5/2005 +0200, L van der Walt wrote:

L van der Walt wrote:

The big problem is that the administrators works for the client and not
for me. I don't want the client to reverse engineer my database.
There might be other applications on the server so the administrators do
require root access.

About the raw database files, I can use encryption to protect the data.

Well, if it's your client's machine, then they any competent
administrator will be able to work around anything you do. They set the
ground-rules you work in - you could be running inside a virtual machine
and never know.

If your database design is so advanced that you can't chance it falling
into the hands of others then you'll need to keep a separate machine and
lock it down yourself.

Are your clients really so dishonest that they'd break into the database
and take the necessary steps to hide their tracks too?

--
Richard Huxton
Archonet Ltd

You could look at what SELinux extensions now available in at least the Red
Hat (and Fedora) distro offer. I have never done anything with SELinux,
and a quick review of the archives indicates it is not a slam dunk to use.
It is designed to create the kind of restrictive environment you describe.

Without the physical security Berend describes, any other security measures
are illusions. All other security measures are easily broken by
alternating boot drives, as an example. I carry a multitool on my
keychain.

Rick

pgsql-general-owner@postgresql.org wrote on 10/05/2005 09:23:58 AM:

L van der Walt wrote:

Then, I might as well just leave the whole PostgreSQL DB and write my
own mini DB with encrypted XML files. I am sure someone must have an
answer for me.

i think the answer is that windows is giving you a false sense of
security.

in an environment where you cannot trust the administrators, the problem
you wish to solve is, um, "difficult". suppose you encrypt the XML files,
where do you put the keys? to be secure, you'd have to prompt the users for
a key or a passphrase, and you wouldn't be able to store the key or
passphrase anywhere in plaintext, but to encrypt securely you'd need another
key or passphrase, and so circularity ensues.

is windows prompting for keys or passphrases in a secure manner? i doubt it.

richard

L van der Walt writes:

The big problem is that the administrators works for the client and not
for me. I don't want the client to reverse engineer my database.

i think you're trying to get native OS security to perform the function
of a well crafted legal document.

richard

Uh. Unless you've done something more than what you say, a windows
administrator can definitely access the data. Maybe most windows
"administrators" don't know how to do it, but it is possible.

I've viewed and changed data on a database on Windows without the database
administrator username and password. I did it to patch a bug in some
software that stored its programs/scripts in a database - we didn't want to
wait for the vendor to fix it. All I needed was a hexeditor and read/write
access to the necessary files - and a bit of luck - fortunately there was
"room" in the code to do the edits. You can't do "inserts/adds" - you can
only overwrite data.

You could make things a bit harder if you have access to a B level trusted
O/S.

But if your "administrators" only need to do a few things you can trust
them with, you might be able to get away with using disk/partition
encryption and sudo or some wrapper scripts for those few things. You then
get the passwords/keys to mount the encrypted partitions/disks remotely via
a secured encrypted channel e.g. ssh or SSL (make sure you check the certs
;) ).

[1]: If you have physical access you can have a boot CD that resets the admin password, but if you don't want to do that, you can always use Knoppix or something with the necessary NTFS support to read and write the data. Encryption helps make such things harder, but at some point the keys to decrypt stuff need to be present so that things can be used - symmetric encryption is typically used for performance reasons.
admin password, but if you don't want to do that, you can always use
Knoppix or something with the necessary NTFS support to read and write the
data. Encryption helps make such things harder, but at some point the keys
to decrypt stuff need to be present so that things can be used - symmetric
encryption is typically used for performance reasons.

Most crypto stuff ends up being some "thingy" that encrypts/decrypts a
small symmetric crypto key, which then in theory can be accessible by a
resourceful person with "root" or "admin".

At 04:31 PM 10/5/2005 +0200, L van der Walt wrote:

On Wed, Oct 05, 2005 at 04:37:38PM +0200, L van der Walt wrote:

Then, I might as well just leave the whole PostgreSQL DB and write my
own mini DB with encrypted XML files. I am sure someone must have an
answer for me.

I think you are missing the point. Root is all powerful, end of story.
They could copy the datafiles to another machine and read them there.
If you can access the Postgres user you can copy the files also. Root
can open the debugger on the PostgreSQL backend and read data that way.

Root can use the debugger to bypass access protection in PostgreSQL,
and the backend would have no way to knowing it even happened. Root can
sniff the packets going over the network. Root can read the password
and ident files. Root can access kernel memory and adjust his own
permissions. Root can create a jail and make processes think they're
on the same machine when they're actual somewhere else.

In your example, they could simply extract your encyption key from the
memory of your program and decrypt the database files themselves. There
is no defense.

Root *is* god. If you don't trust the users, don't give them root. Why
should you? Perhaps you need explain what you want more carefully.
Maybe SELinux has a start for you, but you should probably just not let
people have root, seriously.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

L van der Walt <mailing@lani.co.za> writes:

The big problem is that the administrators works for the client and not
for me. I don't want the client to reverse engineer my database.
There might be other applications on the server so the administrators do
require root access.
About the raw database files, I can use encryption to protect the data.

If you think encryption will protect you against someone with root
privileges, you're sadly mistaken. They could, for example, attach
to your process with a debugger and examine the decrypted data appearing
within its memory space. Or just capture the decryption key that way,
and then study the files at their leisure.

All the same points hold for SQL Server of course --- the fact that you
weren't aware of these risks doesn't mean they don't exist.

regards, tom lane

Richard Huxton wrote:

L van der Walt wrote:

The big problem is that the administrators works for the client and
not for me. I don't want the client to reverse engineer my database.
There might be other applications on the server so the administrators
do require root access.

About the raw database files, I can use encryption to protect the data.

Well, if it's your client's machine, then they any competent
administrator will be able to work around anything you do. They set
the ground-rules you work in - you could be running inside a virtual
machine and never know.

If your database design is so advanced that you can't chance it
falling into the hands of others then you'll need to keep a separate
machine and lock it down yourself.

Are your clients really so dishonest that they'd break into the
database and take the necessary steps to hide their tracks too?

--
Richard Huxton
Archonet Ltd

No I can not trust the clients administrators.

I have played now with MySQL and with MySQL you can change the password
for root in MySQL (same as postgres in PostgreSQL). If you use the
command line tools like dump you require the password. Just because
your root doesn't mean your root in MySQL

Can one separate the user postgres in PostgreSQL from the user postgres
in Linux(The OS)?

Also sprach L van der Walt (mailing@lani.co.za)

The big problem is that the administrators works for the client and not
for me. I don't want the client to reverse engineer my database.

[...]

About the raw database files, I can use encryption to protect the data.

How shall the DBMS acces the data files? It will need the key, be it a
simple password or a pub key for asymmetric encryption. So you have to
store the key somewhere on the machine where an administrator can
access it. You could store the key on another machine, but it has to
be transmitted to the server, so anyone with physical (or at least
root access) can sniff it.

However, there is *no* way to protect a computer program from being
reverse engineered. If you want to run it, it has to be read and
executed by the server, so it can also be analyzed by an attacker.

That's actually the same problem Digital Restriction Management
systems have, the simply *do* *not* *work*.

--
PGP FPR: CF74 D5F2 4871 3E5C FFFE 0130 11F4 C41E B3FB AE33
--
https://www.ccc.de/ - Europe`s largest hacker group, founded in 1981.

http://mdc3.cybernotic.org/ - Chaostreff Magdeburg

At 04:48 PM 10/5/2005 +0200, L van der Walt wrote:

The big problem is that the administrators works for the client and not
for me. I don't want the client to reverse engineer my database.
There might be other applications on the server so the administrators do
require root access.

If it's so important to you, put it on a secured separate physical server,
use encryption, and you admin it.

If you do it right, you should be able to make the client pay for the
separate server ;).

Virtual machines aren't an option, performance on x86 platforms isn't so
good for now AND most VM products often have a snapshot/suspend feature,
which might be useful for looking in the "RAM" for keys and stuff...

About the raw database files, I can use encryption to protect the data.

I'm sure at some point you'd need to decrypt the data, so be careful how
you do things at that point. How are you going to do it?

Don't forget, if you secure things so much, it gets hard for the client too.

The client may also ask: how can they trust you?

I wouldn't recommend anybody to have any of their critical/important data
in an encrypted database which they have no access to without a 3rd party's
help.

Regards,
Link.

L van der Walt wrote:

Richard Huxton wrote:

L van der Walt wrote:

The big problem is that the administrators works for the client and
not for me. I don't want the client to reverse engineer my database.
There might be other applications on the server so the administrators
do require root access.

Well, if it's your client's machine, then they any competent
administrator will be able to work around anything you do. They set
the ground-rules you work in - you could be running inside a virtual
machine and never know.

Are your clients really so dishonest that they'd break into the
database and take the necessary steps to hide their tracks too?

No I can not trust the clients administrators.

Then you really need to have your own machine.

I have played now with MySQL and with MySQL you can change the password
for root in MySQL (same as postgres in PostgreSQL). If you use the
command line tools like dump you require the password. Just because
your root doesn't mean your root in MySQL

Oh, you can stop playing. But you won't stop a determined administrator
for more than about 5 minutes with just a password.

Can one separate the user postgres in PostgreSQL from the user postgres
in Linux(The OS)?

Naturally - just set your pg_hba.conf to use passwords rather than
ident. See the manuals for details.

--
Richard Huxton
Archonet Ltd

On Wed, 2005-10-05 at 09:37, L van der Walt wrote:

Berend Tober wrote:

L van der Walt wrote:

I would like to secure Postgres completly.

Some issues that I don't know you to fix:
1. User postgres can use psql (...) to do anything.
2. User root can su to postgres and thus do anything.
3. Disable all tools like pg_dump

How do I secure a database if I don't trust the administrators.
The administrator will not break the db but they may not view
any information in the databse.

It may be just me and my silly old-fashion attitudes, but I kind of
think that if your sys admin(s) cannot be trusted, you are pretty much
screwed. And your hiring process needs fixing,

But being that as it may, maintaining physical security, i.e., keeping
the host server in a locked room with restricted and recorded access
and that requires at least two persons present so that collusion is
required for tampering, disabling remote root login, granting limited
sys admin privileges with sudo (which records the sudoer activities,
for auditing purposes) might be a way to accomplish what you are
looking for.

Then, I might as well just leave the whole PostgreSQL DB and write my
own mini DB with encrypted XML files. I am sure someone must have an
answer for me.

And it still won't be secure, because whoever has administrator / root
access can copy all the files off, including the encryption keys, and
then get access that way.

This is an interesting thread, but ultimately it's a discussion about
angels dancing on the head of a pin. If someone can log in as root,
they can get access to the data.

If you break the encryption out so that the data is encrypted before it
gets to the database, then that data is secure from the administrator of
the db machine, but now vulnerable to the administrator of the middle
tier machine doing the encryption.

Trying to implement security procedures to reduce root's access are
ultimately futile, no matter how much code you throw at the problem.

Richard_D_Levine@raytheon.com wrote:

You could look at what SELinux extensions now available in at least the Red
Hat (and Fedora) distro offer. I have never done anything with SELinux,
and a quick review of the archives indicates it is not a slam dunk to use.
It is designed to create the kind of restrictive environment you describe.

i'm not sure it's the answer. SELinux is focused on suppressing privilege
escallation problems. root is still root, it has to be. you can constrain
root, but in order to be able to administer the system, root still needs to
be able to modify security policy, otherwise it'd be trivially easy for
a less-than-skilled sysadmin to render his machines unmanageable. even
skilled sysadmins from time to time commit the good old fashioned oops,
after all.

the general problem of an environment where you do not choose to trust
your sysadmins is a very hard one. i've spent some time thinking about
how to handle it, and there are no easy solutions. building a secure,
reliable audit trail system struck me as the way to go, but you literally
need to get the audit logs off site into another facility with
completely independent administration.

richard