Thread: Securing Postgres
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. How do I secure a system in the same way with Linux and PostgreSQL. Richard Huxton 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. > > > > 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 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
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
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. 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: >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. > >How do I secure a system in the same way with Linux and PostgreSQL. > >Richard Huxton 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. >> >> >> >>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 >> > > > > >---------------------------(end of broadcast)--------------------------- >TIP 5: don't forget to increase your free space map settings >
You do know that MySQL lets you reset the root password if you forget it, don't you? See: http://dev.mysql.com/doc/mysql/en/resetting-permissions.html Not terribly secure, after all. Susan L van der Walt <mailing@lani.co.za> To: Richard Huxton <dev@archonet.com>, pgsql-general@postgresql.org Sent by: cc: Subject: Re: [GENERAL] Securing Postgres pgsql-general-owner@pos |-------------------| tgresql.org | [ ] Expand Groups | |-------------------| 10/05/2005 08:27 AM 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)? ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings ---------------------------------------------------------------------------------------------- See our award-winning line of tape and disk-based backup & recovery solutions at http://www.overlandstorage.com ----------------------------------------------------------------------------------------------
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
>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 be aware that for a sufficiently clever administrator, there is no meaningful defense in windows, unix, or linux. the client programs you have written must have access; there must be access to the system catalog. unless you are using a properly set up TCP over SSL connection to get to the database, your clients are vulnerable to a man-in-the-middle attack. it takes a program that sits passively in the connection collecting interesting data until it gets what it needs. unencrypted data residing in RAM or in swap space is at risk at all times. the sole purposes of the windows security "features" you are depending on are to comfort PHBs and discomfit the inept. they don't really work. richard