Database level encryption

Database level encryption

am 03.04.2010 15:35:14 von Timothy Madden

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 ?

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database level encryption

am 03.04.2010 19:03:16 von Joe Conway

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig26797C36A99E800069A43797
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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


--------------enig26797C36A99E800069A43797
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iQIcBAEBCAAGBQJLt3TZAAoJEDfy90M199hlD8YP+gIG7i2yBhBFrcr35L4T eekj
5xTbjrWAbUrkdDcyDqldNYR6T8X2bWf7HgUib0si2NkHlkoxA7tA4vaZguf5 nB6F
Caa1ZCSQvt0cmxAh92DG1xBOPtoiqW4AzcCuH25guXCYAQzHmariOcuCeYXX cbB7
kyxFkjMsnf/iFm+UigcyZTt9TYlCUfQGRW7RKzeOiDREkm1kc6t/MxHu5qlW ysGb
TKFoEh/tY8fE4SjtM4aAbP+2gkqUuF3hCdvMfVq+fsw/Y3Gp7powfwS6C/+2 M7ZY
A8ppqxRqvvKqBRK0X8scBwzfnzTl6+46wGTUp5CVXufxRgmZC4yF8+YxEp9w 8XX+
M43VvrzBL4Tt9zVrrKl6pVez0yezoikdsLu77WW94kLWHroggW7Lpg46b9rQ JDZc
X5rfCjyucptGdRszPnB4qbs+n58mrn6cW0o4cSy6iOcGlcGGqaNEzyQeA1b1 R9zj
R3yEsTGAhUr0CL0Big6Q8qRyHtvzkJlcc01Agy76qMmUxTJt+/z5Nzpxns7Y Lsoa
uLx0aFPWekGfT9Z87lt8i1nmRTRrYIzF9MsX6KQ29sRTwEI7R/lCZVFPAQ+U 5/vu
XBwIMZzcuVX2jUjqxutF/gBgqihmU3+SZ5TYzjhtpEps0iluhTsjQ6K5gGoN V8hZ
geVi1fS1sa7LkblxVg4A
=o5w2
-----END PGP SIGNATURE-----

--------------enig26797C36A99E800069A43797--

Re: Database level encryption

am 05.04.2010 22:30:10 von Timothy Madden

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 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?
> =A0 a) The physical machine is stolen
> =A0 b) A database dump is stolen
> =A0 c) Someone roots your system
> =A0 d) Someone compromises your application, via SQL injection, etc
>
> 2. What data needs to be encrypted?
> =A0 a) All columns of all tables
> =A0 b) Selected columns of selected tables
>
> 3. Do you need to be able to search or sort on any of the encrypted
> =A0 columns?
>
> 4. Is your password stored somewhere on the hardware, or is it entered
> =A0 by a human every time the application starts?
>
> 5. Do you want a single password for all data access, or is the
> =A0 encryption by user or some other segmentation?
>
> 6. Is brute-force cracking of the password a concern? Will your
> =A0 application shut down repeated failed attempts?
>
> There is no magic bullet. This requires careful thought, analysis, and
> trade-offs.
>
> Joe
>
>

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database level encryption

am 05.04.2010 22:34:53 von Scott Marlowe

On Mon, Apr 5, 2010 at 2:30 PM, Timothy Madden wro=
te:
> 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 =A0see 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.)

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database level encryption

am 05.04.2010 22:46:48 von Kevin Grittner

Scott Marlowe wrote:
> Timothy Madden 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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database level encryption

am 05.04.2010 22:55:23 von Anibal David Acosta

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
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 =A0see 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.)

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database level encryption

am 06.04.2010 00:50:12 von Joe Conway

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigBF2C67937C1E42F7C5D33689
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On 04/05/2010 01:46 PM, Kevin Grittner wrote:
> Scott Marlowe wrote:
>> Timothy Madden wrote:
> =20
>>> 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.
> =20
>> Everything you've said so far points to using a mounted encrypted
>> drive to store the db.
> =20
> 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


--------------enigBF2C67937C1E42F7C5D33689
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iQIcBAEBCAAGBQJLumkkAAoJEDfy90M199hlU7UQAJReurAoNKVVHYEUqx0V 3NGR
lk2XUplNS/MtPKG8WuYJSwNAbAtjr0M1LJ5Q8PfJUf8iM0T9B2lNrOPrLfq2 PJAu
wtXiohriGrkdRvAkfiEqiUmIfFLTWXuT33mtjCQwYLWJZrxV+fe/UEIdCBjc mFhc
QzZsvGFwUsESVOLYmZ0SxGeJgfV6y8QVX9Siuv+U6qP2OyfSFDxVJIM/jqvH x7NN
Q/vluvYYQwIJnB+KOzOKh3jCCTtoj5BNi7BAmk25VpfYjgy9uToQqDEUNvAv MM29
3Bjwkc563B0E4GBYvO66iTvEtamajcIfvR922bKb4hZwVMp8D3bQkxRFGxLG kgso
esJZwd7Ygsw0TykcCJFgJQP9lqO6yNTf2PTpjT4e13bglw6CD3zzbsV/EwUM L2Qq
T9wVb61s51JulZ8JwhCl/JqHVD2YVI5sdw3Er11YPNxH+JktG6F279hzs2Yq i4HU
WotDGIAIzsSDQaKiFFVp98S+BJ9X7nI8KxwoEKrEQiJ9M12GT0/kgjed+5lk kLTO
e609HUe2hI5ItTj/rIwNIItaqnMTs74i+o7UtSmJblFib4MK+FZ0CEJUNqu4 PXMC
HGDoHGx0CTKEdDix4jLoE1s+ndpqMQWeWozd2Tx1P2Or5RqQ/TxDH1CYBH4R xJZk
IvRWSHWSlnvHx0NVGiI5
=s3Us
-----END PGP SIGNATURE-----

--------------enigBF2C67937C1E42F7C5D33689--

Re: Database level encryption

am 06.04.2010 11:45:52 von Timothy Madden

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 wrote:
> On 04/05/2010 01:46 PM, Kevin Grittner wrote:
>> Scott Marlowe wrote:
>>> Timothy Madden 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. =A0I know you explicitly said you didn't want to use that in
>> your original post, but you didn't say why. =A0I 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
>
>

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database level encryption

am 06.04.2010 14:59:38 von adsmail

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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database level encryption

am 06.04.2010 16:36:17 von Kevin Grittner

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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database level encryption

am 06.04.2010 16:59:59 von Timothy Madden

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
wrote:
> Timothy Madden 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
>

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database level encryption

am 06.04.2010 17:47:52 von Melissa Peterson

This is a multi-part message in MIME format.

------_=_NextPart_001_01CAD5A0.8483399C
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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.

------_=_NextPart_001_01CAD5A0.8483399C
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable




charset=3Diso-8859-1">
6.5.7226.0">
RE: [ADMIN] Database level encryption




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.






------_=_NextPart_001_01CAD5A0.8483399C--

Re: Database level encryption

am 06.04.2010 19:36:54 von Tim Landscheidt

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


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database level encryption

am 07.04.2010 00:07:21 von Kevin Grittner

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


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database level encryption

am 07.04.2010 12:24:19 von Timothy Madden

Andreas 'ads' Scherbaum 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

Re: Database level encryption

am 07.04.2010 12:45:11 von Timothy Madden

On Wed, Apr 7, 2010 at 1:07 AM, Kevin Grittner
wrote:
> Timothy Madden wrote:
>
[...]
> But the server needs to read certain data from the database
> directory in order to start. =A0In particular, WAL files need to be
> read to get a clean start, and those can contain any data from the
> database table. =A0Any or all tables may need to be accessed to get
> the database to a consistent point on startup. =A0Plus 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

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database level encryption

am 07.04.2010 14:05:13 von Tim Landscheidt

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


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database level encryption

am 07.04.2010 14:25:52 von Michael Gould

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.=
=20
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" wrote:
> Andreas 'ads' Scherbaum wrote:
>=20
>> If someone captures the machine the bad guy can install a network
>> sniffer and steal the database passwords upon connect.
>=20
> 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 ?
>=20
>=20
>>> 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.
>=20
>=20
> 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.
>=20
>=20
> Timothy Madden
>=20
> --=20
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>=20



--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database level encryption

am 07.04.2010 18:52:14 von Timothy Madden

Ok people thank you for your answers.

Timothy Madden



On Wed, Apr 7, 2010 at 3:25 PM, Michael Gould
wrote:
> Timothy,
>
> I've worked with SQL Anywhere which does have database encryption. =A0The=
re
> 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. =A0T=
hat
> didn't' seem to really slow down the system.
>
> That being said, the encryption will keep the normal user out of the syst=
em,
> 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. =A0I'd like to see something to protect stored proced=
ures
> and triggers but overall I agree that a encrypted drive is probably the b=
est
> thing and require ssl connections.
>
> Best Regards
>
> Michael Gould
>
>
>
> "Timothy Madden" wrote:
>> Andreas 'ads' Scherbaum 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
>>
>
>
>

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database level encryption

am 07.04.2010 20:58:35 von Christopher Browne

terminatorul@gmail.com (Timothy Madden) writes:
> Andreas 'ads' Scherbaum 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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database level encryption

am 07.04.2010 21:21:30 von Scott Marlowe

On Tue, Apr 6, 2010 at 3:45 AM, 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.

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.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database level encryption

am 08.04.2010 10:52:49 von adsmail

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
> wrote:
> > Timothy Madden 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.
>=20
> 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

--=20
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin