Thread: security question
How good is postgresql security? For example, If I have data that I do not anyone to see, including the programmer/dba, is it enough to change the password to the only user? If they have access to the raw files is there a way for them to somehow see the data? can they copy the files to another postgresql instance where they have rights and view the data? Basically, we have a requirement to put sensitive personnel information into the database, including salary etc. and we don't want any employees, including the dba to have a possibility of accessing it. Thanks Sim
am Mon, dem 22.01.2007, um 16:10:15 +0200 mailte Sim Zacks folgendes: > How good is postgresql security? > For example, If I have data that I do not anyone to see, including the > programmer/dba, is it enough to change the password to the only user? > If they have access to the raw files is there a way for them to somehow see > the data? > can they copy the files to another postgresql instance where they have > rights and view the data? I think, anyone with read access to the database files can read the information stored in this files. This isn't a postgresql-problem, this is a general problem. > > Basically, we have a requirement to put sensitive personnel information > into the database, including salary etc. and we don't want any employees, > including the dba to have a possibility of accessing it. Store the sensitive data encrypted, and use SSL or other encrypted communication between server and client. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Mon, Jan 22, 2007 at 04:10:15PM +0200, Sim Zacks wrote: > How good is postgresql security? Good, within limits. > For example, If I have data that I do not anyone to see, including the > programmer/dba, is it enough to change the password to the only user? > If they have access to the raw files is there a way for them to somehow see > the data? > can they copy the files to another postgresql instance where they have > rights and view the data? The answer depends heavily on what the "programmer/dba" can do. Any superuser of the DB can see any data Any user that can access the raw files can see any data Any user that can poke into memory can see any data Any user that can access the backups can see any data there So in theory, if you restrict the programmer appropriately you could do it, but you have to check they can still do their job. > Basically, we have a requirement to put sensitive personnel information > into the database, including salary etc. and we don't want any employees, > including the dba to have a possibility of accessing it. Very tricky. Look around to see what other people have done. This question has come up before. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 01/22/07 08:22, Martijn van Oosterhout wrote: > On Mon, Jan 22, 2007 at 04:10:15PM +0200, Sim Zacks wrote: >> How good is postgresql security? > > Good, within limits. > >> For example, If I have data that I do not anyone to see, including the >> programmer/dba, is it enough to change the password to the only user? >> If they have access to the raw files is there a way for them to somehow see >> the data? >> can they copy the files to another postgresql instance where they have >> rights and view the data? > > The answer depends heavily on what the "programmer/dba" can do. > > Any superuser of the DB can see any data > Any user that can access the raw files can see any data > Any user that can poke into memory can see any data > Any user that can access the backups can see any data there > > So in theory, if you restrict the programmer appropriately you could do > it, but you have to check they can still do their job. Anyone tried running PG with restrictive SELinux policies? >> Basically, we have a requirement to put sensitive personnel information >> into the database, including salary etc. and we don't want any employees, >> including the dba to have a possibility of accessing it. > > Very tricky. Look around to see what other people have done. This > question has come up before. > > Have a nice day, -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFtMqdS9HxQb37XmcRAko0AKC5PGCCRbgAEWE0I2+on5qkiGPgkACgxEcB JQcUuFK60xtLb0bkECciByY= =jAMn -----END PGP SIGNATURE-----
On Mon, Jan 22, 2007 at 08:30:53AM -0600, Ron Johnson wrote: > > The answer depends heavily on what the "programmer/dba" can do. > > > > Any superuser of the DB can see any data > > Any user that can access the raw files can see any data > > Any user that can poke into memory can see any data > > Any user that can access the backups can see any data there > > > > So in theory, if you restrict the programmer appropriately you could do > > it, but you have to check they can still do their job. > > Anyone tried running PG with restrictive SELinux policies? I beleive redhat does this by default, if you have SELinux enabled. Suitably restricted, it should mean the dba/programmer won't be able to get at the data except via the database. I don't know of anyone that's actually done this. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
On 22 Jan 2007 at 16:10, Sim Zacks wrote: > How good is postgresql security? > For example, If I have data that I do not anyone to see, including the programmer/dba, is it enough > to change the password to the only user? > If they have access to the raw files is there a way for them to somehow see the data? > can they copy the files to another postgresql instance where they have rights and view the data? > > Basically, we have a requirement to put sensitive personnel information into the database, including > salary etc. and we don't want any employees, including the dba to have a possibility of accessing it. You'll have to store the data encrypted. If you want to be ultrasecure you should encrypt\decrypt on the client side. http://www.postgresql.org/docs/8.2/interactive/encryption-options.html You can encrypt/decrypt server side using fynctions from the contrib pgrypto module, but if you choose to do it that way then the data is being transmitted in the clear between the client and the server (unless you're using SSL). Even if using SSL the data would be present on the server in unencrypted form both before it gets stored, and after it gets decrypted and is being sent back to the client. Any DBA etc would be able to intercept that data. Not only that but the DBA would be able to intercept the key being used to encrypt/decrypt the data (and thus be able to decrypt the contents of the entire DB). The only way to absolutely prevent this from happening is to encrypt/decrypt locally on the client side. This is not a PostgreSQL limitation, it would be true of any DB out there. -jan
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 01/22/07 09:55, Jan Muszynski wrote: > On 22 Jan 2007 at 16:10, Sim Zacks wrote: > >> How good is postgresql security? For example, If I have data >> that I do not anyone to see, including the programmer/dba, is >> it enough to change the password to the only user? If they have >> access to the raw files is there a way for them to somehow see >> the data? can they copy the files to another postgresql >> instance where they have rights and view the data? >> >> Basically, we have a requirement to put sensitive personnel >> information into the database, including salary etc. and we >> don't want any employees, including the dba to have a >> possibility of accessing it. > > You'll have to store the data encrypted. If you want to be > ultrasecure you should encrypt\decrypt on the client side. > > http://www.postgresql.org/docs/8.2/interactive/encryption-options.html > > > You can encrypt/decrypt server side using fynctions from the > contrib pgrypto module, but if you choose to do it that way then > the data is being transmitted in the clear between the client and > the server (unless you're using SSL). Even if using SSL the data > would be present on the server in unencrypted form both before it > gets stored, and after it gets decrypted and is being sent back > to the client. Any DBA etc would be able to intercept that data. > Not only that but the DBA would be able to intercept the key > being used to encrypt/decrypt the data (and thus be able to > decrypt the contents of the entire DB). Root, I can understand, but why would the DBA be able to intercept the key? > The only way to absolutely prevent this from happening is to > encrypt/decrypt locally on the client side. Unless you are also running DB apps on the host. > This is not a PostgreSQL limitation, it would be true of any DB > out there Running under the standard Unix "root can do anything" security model. Systems with (properly configured) highly-granular security models would not let that happen. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFtOMjS9HxQb37XmcRAk65AJ4kq/gZwtm30ADG1SYNpN4ZPwQSJACcDmKg 3frSz8GJC/FJaTAdDlIiACA= =6f2P -----END PGP SIGNATURE-----
On 22 Jan 2007 at 10:15, Ron Johnson wrote: > On 01/22/07 09:55, Jan Muszynski wrote: > > On 22 Jan 2007 at 16:10, Sim Zacks wrote: > > > >> How good is postgresql security? For example, If I have data > >> that I do not anyone to see, including the programmer/dba, is > >> it enough to change the password to the only user? If they have > >> access to the raw files is there a way for them to somehow see > >> the data? can they copy the files to another postgresql > >> instance where they have rights and view the data? > >> > >> Basically, we have a requirement to put sensitive personnel > >> information into the database, including salary etc. and we > >> don't want any employees, including the dba to have a > >> possibility of accessing it. > > > > You'll have to store the data encrypted. If you want to be > > ultrasecure you should encrypt\decrypt on the client side. > > > > http://www.postgresql.org/docs/8.2/interactive/encryption-options.html > > > > > > You can encrypt/decrypt server side using fynctions from the > > contrib pgrypto module, but if you choose to do it that way then > > the data is being transmitted in the clear between the client and > > the server (unless you're using SSL). Even if using SSL the data > > would be present on the server in unencrypted form both before it > > gets stored, and after it gets decrypted and is being sent back > > to the client. Any DBA etc would be able to intercept that data. > > Not only that but the DBA would be able to intercept the key > > being used to encrypt/decrypt the data (and thus be able to > > decrypt the contents of the entire DB). > > Root, I can understand, but why would the DBA be able to intercept > the key? All he'd have to do is turn on logging for all SQL statements. In the log files he'd find the key as it was transmitted from the client (necessary if the client is entering the key and crypt functions are taking place server side). I am assuming that DBA = database superuser in this instance, and probably should have used the term superuser instead. <snip>