Thread: Database level encryption
Hello Please how could I encrypt my database so data can be retrieved only with my password, when my client application starts, even if the database is captured ? I would like the encryption to be transparent at the application level, which needs only provide a password and then all the database can be accessed, and I would like to store my database on any filesystem on my client's computers. so I do not want filesystem ecryption either. My client has a proximity advertising application, running with a local PostgreSQL database, that they install on many computers from several client companies, to cover many spots on a given geographic area. The application always starts with my password, and my client will be the only one that can start or stop it, and I would like the application data in the database to be protected by that same password. I can only see how PostgreSQL encrypts the password or the connection in the documentation, and for the database I can see application-level encryption with pgcrypto (and filesystem level encryption), How could I get database level encryption in PostgreSQL ?
On 04/03/2010 06:35 AM, Timothy Madden wrote: > I can only see how PostgreSQL encrypts the password or the connection > in the documentation, and for the database I can see application-level > encryption with pgcrypto (and filesystem level encryption), How could > I get database level encryption in PostgreSQL ? This is an extremely broad question, and you have barely begun to provide enough information to answer it. For starters: 1. What is your threat scenario? a) The physical machine is stolen b) A database dump is stolen c) Someone roots your system d) Someone compromises your application, via SQL injection, etc 2. What data needs to be encrypted? a) All columns of all tables b) Selected columns of selected tables 3. Do you need to be able to search or sort on any of the encrypted columns? 4. Is your password stored somewhere on the hardware, or is it entered by a human every time the application starts? 5. Do you want a single password for all data access, or is the encryption by user or some other segmentation? 6. Is brute-force cracking of the password a concern? Will your application shut down repeated failed attempts? There is no magic bullet. This requires careful thought, analysis, and trade-offs. Joe
Attachment
My scenario is how to protect the database if the machine is stolen (it is a mini-laptop), and I would like to encrypt the entire database, that is all columns of all tables, and if possible everything else found in the database. I would like all searching and sorting functions, just like with a normal database (that is, transparent encryption for the application level). The password will be entered by a human in order to start the application. The application exits after three unsuccessful attempts, but nothing prevents the user to start the application again; the number of failures is not counted. However if the database could count that I would not mind. I want a single password for data access to the entire database, there is only one database user involved anyway. I do not see the careful analysis required that you write about, I would say I am asking for SGBD support for database-level encryption. Thank you, Timothy Madden On Sat, Apr 3, 2010 at 8:03 PM, Joe Conway <mail@joeconway.com> wrote: > On 04/03/2010 06:35 AM, Timothy Madden wrote: >> I can only see how PostgreSQL encrypts the password or the connection >> in the documentation, and for the database I can see application-level >> encryption with pgcrypto (and filesystem level encryption), How could >> I get database level encryption in PostgreSQL ? > > This is an extremely broad question, and you have barely begun to > provide enough information to answer it. For starters: > > 1. What is your threat scenario? > a) The physical machine is stolen > b) A database dump is stolen > c) Someone roots your system > d) Someone compromises your application, via SQL injection, etc > > 2. What data needs to be encrypted? > a) All columns of all tables > b) Selected columns of selected tables > > 3. Do you need to be able to search or sort on any of the encrypted > columns? > > 4. Is your password stored somewhere on the hardware, or is it entered > by a human every time the application starts? > > 5. Do you want a single password for all data access, or is the > encryption by user or some other segmentation? > > 6. Is brute-force cracking of the password a concern? Will your > application shut down repeated failed attempts? > > There is no magic bullet. This requires careful thought, analysis, and > trade-offs. > > Joe > >
On Mon, Apr 5, 2010 at 2:30 PM, Timothy Madden <terminatorul@gmail.com> wrote: > My scenario is how to protect the database if the machine is stolen > (it is a mini-laptop), and > I would like to encrypt the entire database, that is all columns of > all tables, and if possible > everything else found in the database. > > I would like all searching and sorting functions, just like with a > normal database (that is, > transparent encryption for the application level). The password will > be entered by a human in > order to start the application. The application exits after three > unsuccessful attempts, but > nothing prevents the user to start the application again; the number > of failures is not counted. > However if the database could count that I would not mind. I want a > single password for > data access to the entire database, there is only one database user > involved anyway. > > I do not see the careful analysis required that you write about, I > would say I am asking for > SGBD support for database-level encryption. Everything you've said so far points to using a mounted encrypted drive to store the db. Windows and Linux both support this, and I'm sure Mac does too. The fact that a different tool is needed for each OS might be an issue. It's dirt simple to setup an encrypted drive on linux where the user types in the passphrase on each boot. Some laptops have wonky behaviour bringing up drives on USB at bootup tho (I'm looking at a Dell that's sitting in the cube next to me that has issues a BIOS update can't fix.)
Scott Marlowe <scott.marlowe@gmail.com> wrote: > Timothy Madden <terminatorul@gmail.com> wrote: >> My scenario is how to protect the database if the machine is >> stolen (it is a mini-laptop), and I would like to encrypt the >> entire database, that is all columns of all tables, and if >> possible everything else found in the database. >> >> I would like all searching and sorting functions, just like with >> a normal database (that is, transparent encryption for the >> application level). The password will be entered by a human in >> order to start the application. > Everything you've said so far points to using a mounted encrypted > drive to store the db. Agreed. I know you explicitly said you didn't want to use that in your original post, but you didn't say why. I don't think you're going to convince anyone here to put effort into something you can configure to "just work" with so little trouble on existing systems, without a really good argument. -Kevin
On 04/05/2010 01:46 PM, Kevin Grittner wrote: > Scott Marlowe <scott.marlowe@gmail.com> wrote: >> Timothy Madden <terminatorul@gmail.com> wrote: > >>> My scenario is how to protect the database if the machine is >>> stolen (it is a mini-laptop), and I would like to encrypt the >>> entire database, that is all columns of all tables, and if >>> possible everything else found in the database. >>> >>> I would like all searching and sorting functions, just like with >>> a normal database (that is, transparent encryption for the >>> application level). The password will be entered by a human in >>> order to start the application. > >> Everything you've said so far points to using a mounted encrypted >> drive to store the db. > > Agreed. I know you explicitly said you didn't want to use that in > your original post, but you didn't say why. I don't think you're > going to convince anyone here to put effort into something you can > configure to "just work" with so little trouble on existing systems, > without a really good argument. Agreed here also. I don't see any reason for Postgres to provide this sort of functionality when it can be done at the OS level. There is going to be a significant performance hit -- that is why I would suggest careful analysis and selective encryption instead. But if that isn't important, an encrypted drive is probably the only option. Joe
Attachment
The machine is a mini-laptop running almost all day time (actually there are many of them) and if the machine is captured it is likely to be captured while running. With an encrypted file system if the machine is already booted you already have access to the file system and can simply copy it and even place back the machine without anyone notice anything. With an encrypted database, you need the password anytime you connect, even if another application already has an open connection. On Tue, Apr 6, 2010 at 1:50 AM, Joe Conway <mail@joeconway.com> wrote: > On 04/05/2010 01:46 PM, Kevin Grittner wrote: >> Scott Marlowe <scott.marlowe@gmail.com> wrote: >>> Timothy Madden <terminatorul@gmail.com> wrote: >> >>>> My scenario is how to protect the database if the machine is >>>> stolen (it is a mini-laptop), and I would like to encrypt the >>>> entire database, that is all columns of all tables, and if >>>> possible everything else found in the database. >>>> >>>> I would like all searching and sorting functions, just like with >>>> a normal database (that is, transparent encryption for the >>>> application level). The password will be entered by a human in >>>> order to start the application. >> >>> Everything you've said so far points to using a mounted encrypted >>> drive to store the db. >> >> Agreed. I know you explicitly said you didn't want to use that in >> your original post, but you didn't say why. I don't think you're >> going to convince anyone here to put effort into something you can >> configure to "just work" with so little trouble on existing systems, >> without a really good argument. > > Agreed here also. I don't see any reason for Postgres to provide this > sort of functionality when it can be done at the OS level. There is > going to be a significant performance hit -- that is why I would suggest > careful analysis and selective encryption instead. But if that isn't > important, an encrypted drive is probably the only option. > > Joe > >
On Tue, 6 Apr 2010 12:45:52 +0300 Timothy Madden wrote:
> The machine is a mini-laptop running almost all day time (actually
> there are many of them) and if the machine is captured it is likely to
> be captured while running. With an encrypted file system if the
> machine is already booted you already have access to the file system
> and can simply copy it and even place back the machine without anyone
> notice anything.
If someone captures the machine the bad guy can install a network
sniffer and steal the database passwords upon connect.
> With an encrypted database, you need the password anytime you connect,
> even if another application already has an open connection.
See above, this doesn't help.
If someone get's root access to your machine, nothing (no filesystem
and no database encryption) is goint to help you here.
Bye
--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project
Timothy Madden <terminatorul@gmail.com> wrote: > With an encrypted database, you need the password anytime you > connect, even if another application already has an open > connection. How is the database server supposed to start up and become ready to accept connections without reading the database? Also, as previously mentioned, if a bad guy gets hold of the machine while running, what prevents them from installing a daemon to record and transmit keystrokes after they copy the encrypted data? Perhaps an encrypted drive for the database data combined with an aggressive lockup policy for an idle machine would work? -Kevin
The machine does not have internet and it will not be trivial for the bad guy to install anything there. And my idea is exactly that the database is inaccessible, even if the server starts. Unless the password is provided upon connecting or selecting the database. You can think of it as if all columns in all tables are ecrypted (although I would like even the number and names of tables and schema-objects to be encrypted). On Tue, Apr 6, 2010 at 5:36 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Timothy Madden <terminatorul@gmail.com> wrote: > >> With an encrypted database, you need the password anytime you >> connect, even if another application already has an open >> connection. > > How is the database server supposed to start up and become ready to > accept connections without reading the database? > > Also, as previously mentioned, if a bad guy gets hold of the machine > while running, what prevents them from installing a daemon to record > and transmit keystrokes after they copy the encrypted data? > > Perhaps an encrypted drive for the database data combined with an > aggressive lockup policy for an idle machine would work? > > -Kevin >
On Tue, 6 Apr 2010 12:45:52 +0300 Timothy Madden wrote:
> The machine is a mini-laptop running almost all day time (actually
> there are many of them) and if the machine is captured it is likely to
> be captured while running. With an encrypted file system if the
> machine is already booted you already have access to the file system
> and can simply copy it and even place back the machine without anyone
> notice anything.
If someone grabbing one and running is the main issue, in addition to encrypting the file system, you could switch out any semi-decent batteries in the laptops to old ones that don't hold a charge. At that point, they'd have the same requirement as desktops- they'd have to be plugged in to stay on.
Melissa Peterson
R&D Engineer
melissa@gigacrete.com
GigaCrete, Inc.
6775 Speedway Boulevard, Suite M-104
Las Vegas, NV 89115
Phone 702-643-6363
Fax 702-543-7010
www.gigacrete.com
B U I L D S T R O N G. B U I L D F O R W A R D.
This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is strictly prohibited. If you have received this communication in error, please notify us by reply email and immediately and permanently delete this message and any attachments.
TrueCrypt is a free open source application, can encrypt any drive or create a virtual encrypted drive. -----Mensaje original----- De: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] En nombre de Scott Marlowe Enviado el: lunes, 05 de abril de 2010 05:35 p.m. Para: Timothy Madden CC: Joe Conway; pgsql-admin@postgresql.org Asunto: Re: [ADMIN] Database level encryption On Mon, Apr 5, 2010 at 2:30 PM, Timothy Madden <terminatorul@gmail.com> wrote: > My scenario is how to protect the database if the machine is stolen > (it is a mini-laptop), and > I would like to encrypt the entire database, that is all columns of > all tables, and if possible > everything else found in the database. > > I would like all searching and sorting functions, just like with a > normal database (that is, > transparent encryption for the application level). The password will > be entered by a human in > order to start the application. The application exits after three > unsuccessful attempts, but > nothing prevents the user to start the application again; the number > of failures is not counted. > However if the database could count that I would not mind. I want a > single password for > data access to the entire database, there is only one database user > involved anyway. > > I do not see the careful analysis required that you write about, I > would say I am asking for > SGBD support for database-level encryption. Everything you've said so far points to using a mounted encrypted drive to store the db. Windows and Linux both support this, and I'm sure Mac does too. The fact that a different tool is needed for each OS might be an issue. It's dirt simple to setup an encrypted drive on linux where the user types in the passphrase on each boot. Some laptops have wonky behaviour bringing up drives on USB at bootup tho (I'm looking at a Dell that's sitting in the cube next to me that has issues a BIOS update can't fix.) -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Timothy Madden <terminatorul@gmail.com> wrote: > The machine does not have internet and it will not be trivial for the > bad guy to install anything there. > [...] Then why bother encrypting the database if $BADGUY cannot access it without your installed application anyhow? Tim
Timothy Madden <terminatorul@gmail.com> wrote: > The machine does not have internet It would be very unusual for a machine never to be connected to a network which has Internet access, at least for periodic OS updates or to get new versions of the database or software. But OK, if you say they are never, ever connected to networks with Internet connections, I guess the bad guy would need to access the machine *twice* to get the password off of it and compromise the data. You still haven't suggested any reason that this would be more secure than an encrypted mount-point combined with aggressive idle-time lockup, though. > it will not be trivial for the bad guy to install anything there. Well, if you set up security properly, it wouldn't be trivial for a bad guy to copy the database off the machine under the pending login, if they got hold of it while it was running, unless someone left it running under the root login. Personally, I wouldn't give the password for that login to anyone who was going to be carrying the laptop into the field. > And my idea is exactly that the database is inaccessible, even if > the server starts. But the server needs to read certain data from the database directory in order to start. In particular, WAL files need to be read to get a clean start, and those can contain any data from the database table. Any or all tables may need to be accessed to get the database to a consistent point on startup. Plus there are all the system catalogs, including the ones needed to authenticate users. -Kevin
Andreas 'ads' Scherbaum <adsmail@wars-nicht.de> wrote: > If someone captures the machine the bad guy can install a network > sniffer and steal the database passwords upon connect. I think protecting against a keylogger is a different issue than database encryption. Is this why database encryption is "not needed" for PostgreSQL, as people here say ? >> With an encrypted database, you need the password anytime you connect, >> even if another application already has an open connection. > > See above, this doesn't help. > > If someone get's root access to your machine, nothing (no filesystem > and no database encryption) is goint to help you here. I would have to disagree with you here. The whole point of encryption is that you need the key in order to get your data back. Timothy Madden
On Wed, Apr 7, 2010 at 1:07 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Timothy Madden <terminatorul@gmail.com> wrote: > [...] > But the server needs to read certain data from the database > directory in order to start. In particular, WAL files need to be > read to get a clean start, and those can contain any data from the > database table. Any or all tables may need to be accessed to get > the database to a consistent point on startup. Plus there are all > the system catalogs, including the ones needed to authenticate > users. OK let's put the key logger issue aside from database encryption. I am willing to accept that the server may need to read the list of tables/schema-objects in the database, and some leftover data, in order to start, as long as the leftover data is immediately discarded upon start-up, and as long as it is likely that this data is not a large fraction of the data found in the database. It would still be nice if this check or clean-up could be delayed until such time some user really selects the database for use, and provides a password. I would expect the database (or catalog, I guess, is it ?) to be visible, but any attempt to connect without the password would fail as if user has no rights on the database or the password is wrong. What I perceive as a problem here might be that an encrypted database would automatically need the privileges to be set up so that only the owner can read or connect to it. That is its privileges would have to indicate that even the postgres user can not read it. Except maybe for the names of tables and schema objects, if the server insists that it needs those for a clean start up, and so those shall remain clear text. User authentication should be unrelated to encrypting the database owned by that user. You can think of it as if only the owner can ever connect to such a database, and his/her password is the encryption key, or as if any user that wishes to connect should provide the encryption key first, and then the user name and password. Thank you, Timothy Madden
Timothy Madden <terminatorul@gmail.com> wrote: > [...] > User authentication should be unrelated to encrypting the database > owned by that user. You can think of it as if only the owner can ever > connect to such a database, and his/her password is the encryption > key, or as if any user that wishes to connect should provide the > encryption key first, and then the user name and password. As you seem not to need any of PostgreSQL's features, why not use SQLite? It seems perfect for your application as far as you stated it. Tim
Timothy, I've worked with SQL Anywhere which does have database encryption. There are pluses to having a encrypted db, but it did slow down the processing. They also had the ability to encrypt stored procedures and triggers. That didn't' seem to really slow down the system. That being said, the encryption will keep the normal user out of the system, but those aren't the people you need to worry about. The people you need to worry about are the real hackers and they will be able to get around this type of encryption. I'd like to see something to protect stored procedures and triggers but overall I agree that a encrypted drive is probably the best thing and require ssl connections. Best Regards Michael Gould "Timothy Madden" <terminatorul@gmail.com> wrote: > Andreas 'ads' Scherbaum <adsmail@wars-nicht.de> wrote: > >> If someone captures the machine the bad guy can install a network >> sniffer and steal the database passwords upon connect. > > I think protecting against a keylogger is a different issue than > database encryption. Is this why database encryption is "not needed" > for PostgreSQL, as people here say ? > > >>> With an encrypted database, you need the password anytime you connect, >>> even if another application already has an open connection. >> >> See above, this doesn't help. >> >> If someone get's root access to your machine, nothing (no filesystem >> and no database encryption) is goint to help you here. > > > I would have to disagree with you here. The whole point of encryption > is that you need the key in order to get your data back. > > > Timothy Madden > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >
Ok people thank you for your answers. Timothy Madden On Wed, Apr 7, 2010 at 3:25 PM, Michael Gould <mgould@intermodalsoftwaresolutions.net> wrote: > Timothy, > > I've worked with SQL Anywhere which does have database encryption. There > are pluses to having a encrypted db, but it did slow down the processing. > They also had the ability to encrypt stored procedures and triggers. That > didn't' seem to really slow down the system. > > That being said, the encryption will keep the normal user out of the system, > but those aren't the people you need to worry about. The people you need to > worry about are the real hackers and they will be able to get around this > type of encryption. I'd like to see something to protect stored procedures > and triggers but overall I agree that a encrypted drive is probably the best > thing and require ssl connections. > > Best Regards > > Michael Gould > > > > "Timothy Madden" <terminatorul@gmail.com> wrote: >> Andreas 'ads' Scherbaum <adsmail@wars-nicht.de> wrote: >> >>> If someone captures the machine the bad guy can install a network >>> sniffer and steal the database passwords upon connect. >> >> I think protecting against a keylogger is a different issue than >> database encryption. Is this why database encryption is "not needed" >> for PostgreSQL, as people here say ? >> >> >>>> With an encrypted database, you need the password anytime you connect, >>>> even if another application already has an open connection. >>> >>> See above, this doesn't help. >>> >>> If someone get's root access to your machine, nothing (no filesystem >>> and no database encryption) is goint to help you here. >> >> >> I would have to disagree with you here. The whole point of encryption >> is that you need the key in order to get your data back. >> >> >> Timothy Madden >> >> -- >> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-admin >> > > >
terminatorul@gmail.com (Timothy Madden) writes: > Andreas 'ads' Scherbaum <adsmail@wars-nicht.de> wrote: > >> If someone captures the machine the bad guy can install a network >> sniffer and steal the database passwords upon connect. > > I think protecting against a keylogger is a different issue than > database encryption. Is this why database encryption is "not needed" > for PostgreSQL, as people here say ? No, the nuance is a bit different. It's not that "database encryption is not needed" - it's rather that "database encryption doesn't usefully protect against a terribly interesting set of attacks." When we think through the scenarios, while encrypting the whole database might seemingly protect against *some* attacks, that's not enough of the story: - There are various classes of attacks that it doesn't help one bit with. - In order to have the database accessible to the postmaster process, there needs to be a copy of the decryption key on that machine, and it is surprisingly difficult to protect that key from someone who has physical access to the machine. This has the result that people are inclined to suggest that encrypting the whole database mayn't actually be a terribly useful technique in practice. -- Know how to blow any problem up into insolubility. Know how to use the phrase "The new ~A system" to insult its argument, e.g., "I guess this destructuring LET thing is fixed in the new Lisp system", or better yet, PROLOG. -- from the Symbolics Guidelines for Sending Mail
On Tue, Apr 6, 2010 at 3:45 AM, Timothy Madden <terminatorul@gmail.com> wrote: > The machine is a mini-laptop running almost all day time (actually > there are many of them) and if the machine is captured it is likely to > be captured while running. Wait, stop right there. So, you're a thief and you just made off with an HP mini with an encrypted file system. You have a login prompt. What do you do to get into it and then get to the encrypted hard drive? Seriously, what is your attack. Why does this machine even have regular login enabled? It would be easy enough to program it to unmount the encrypted drive after 3 failed login attempts. I think you hand-waved this one into being. It's not easy to get into a laptop that's locked / not logged in without rebooting it. And rebooting it unmounts your secure file system. You have a key with passphrase stored on a USB key that's used to start the dbs, then locked physically away from the laptops. If there's some objection to file system encryption I haven't thought of here lemme know.
Hi Timothy,
On Wed, 7 Apr 2010 13:45:11 +0300 Timothy Madden wrote:
> On Wed, Apr 7, 2010 at 1:07 AM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
> > Timothy Madden <terminatorul@gmail.com> wrote:
> >
> [...]
> > But the server needs to read certain data from the database
> > directory in order to start. In particular, WAL files need to be
> > read to get a clean start, and those can contain any data from the
> > database table. Any or all tables may need to be accessed to get
> > the database to a consistent point on startup. Plus there are all
> > the system catalogs, including the ones needed to authenticate
> > users.
>
> OK let's put the key logger issue aside from database encryption.
No, because that's one of the main problems.
If someone already goot root access on this laptop, he can snuff
keystrokes or the network traffic and capture all kind of passwords
(and other interesting information).
Basically your database, running on an unprivileged account, is only as
secure as the root account.
> I am willing to accept that the server may need to read the list of
> tables/schema-objects in the database, and some leftover data, in
> order to start, as long as the leftover data is immediately discarded
> upon start-up, and as long as it is likely that this data is not a
> large fraction of the data found in the database. It would still be
> nice if this check or clean-up could be delayed until such time some
> user really selects the database for use, and provides a password.
There's more:
- Vacuum reads whole memory pages, so any kind of encryption can only be
on row level.
- Analyze stores the most common values per column, so it must be able
to scan the columns without the password. Else the planer won't have
reasonable good data. In addition: the statistics data is stored in
system tables, so your password must apply here too.
Bye
--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project