vulnerability/SSL

Hi,
I¡¯m using postgreSQL with SSL these days. The version I¡¯m
using is 8.0.3. I found that it¡¯s impossible to use an
encrypted key file.
When you use a protected server.key file, you will be
prompted to input your passphrase EVERYTIME IT¡¯S USED, not
only when you start the server but also when a client makes a
connection. So you have to leave the key file un-protected. I
think it¡¯s a serious vulnerability since the security relies
on the secrecy of the private key. Without encryption, the
only thing we can use to protect the private key is the
access control mechanism provided by the OS.
Any comments on this issue?

If you don't trust the access control provided by the OS, why are you putting sensitive data on it?
If one can break your access control in the OS they can read all your data anyway - they don't even need to sniff the wire and decrypt it using the key. Or they can just change the passwords of your users and connect - or *change* they key.

If you're still concerned, it should be possible to mount is using a crypto loopback device which wuold require a password to get it in there (note! haven't tried it, but it should work). That would save you against someone rebooting your server on a separate OS and try to read it, but not against someone cracking the system while it's running. But again, your data would be just as vulnerable, so I don't really see the gain.

//Magnus

Greetings!

Does anybody know how well the optimizer works when dealing with inherited
tables? I am currently using 8.0.1.

I have a table called eventlog.record_main, and a number of inherited
tables to partition the data (called
eventlog_partition._<day>__record_main). <luid> is the primary key (all
tables have this indexed via the primary key).

The reason for doing this is that a single table would be way too big
(there are on average 6-7 million rows per table) so that vacuum and
deletes would be inefficient. Inserting has been much more efficient using
this mechanism.

When I try the following query, I get sequential scans:

explain select * from eventlog.record_main order by luid limit 5;

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Limit (cost=160800332.75..160800332.77 rows=5 width=92)
-> Sort (cost=160800332.75..161874465.60 rows=429653138 width=92)
Sort Key: eventlog.record_main.luid
-> Result (cost=0.00..11138614.37 rows=429653138 width=92)
-> Append (cost=0.00..11138614.37 rows=429653138 width=92)
-> Seq Scan on record_main (cost=0.00..0.00 rows=1 width=92)
-> Seq Scan on _20050401__record_main record_main (cost=0.00..223029.64 rows=8620164 width=92)
-> Seq Scan on _20050402__record_main record_main (cost=0.00..201536.46 rows=7789446 width=92)
-> Seq Scan on _20050403__record_main record_main (cost=0.00..211277.34 rows=8165934 width=92)
-> Seq Scan on _20050404__record_main record_main (cost=0.00..219381.70 rows=8479170 width=92)
-> Seq Scan on _20050405__record_main record_main (cost=0.00..226305.94 rows=8746794 width=92)

(and so on)

Yet, when I run the query on a single table, I get index usage:

explain select * from eventlog_partition._20050601__record_main order by luid limit 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.15 rows=5 width=92)
-> Index Scan using _20050601__record_main_pkey on _20050601__record_main (cost=0.00..163375.23 rows=5460021 width=92)
(2 rows)

This means that any query that limits the rows will run extremely
inefficiently. Given a limit of 5, at most only 5 rows need to be
considered in each partition sub-table, so an optimal plan would run
a sub-query in each table limited to 5 rows, and then merge the results.

Any ideas/fixes/patches?

Regards!
Ed

On Wed, 2005-06-08 at 16:08 +0200, Magnus Hagander wrote:

Hi,
I¡¯m using postgreSQL with SSL these days. The version I¡¯m
using is 8.0.3. I found that it¡¯s impossible to use an
encrypted key file.
When you use a protected server.key file, you will be
prompted to input your passphrase EVERYTIME IT¡¯S USED, not
only when you start the server but also when a client makes a
connection. So you have to leave the key file un-protected. I
think it¡¯s a serious vulnerability since the security relies
on the secrecy of the private key. Without encryption, the
only thing we can use to protect the private key is the
access control mechanism provided by the OS.
Any comments on this issue?

If you don't trust the access control provided by the OS, why are you putting sensitive data on it?
If one can break your access control in the OS they can read all your data anyway - they don't even need to sniff the wire and decrypt it using the key. Or they can just change the passwords of your users and connect - or *change* they key.

Yes and no. They can't change the key. It's tied to the certificate,
which is signed. They need to get a signed certificate from a trusted
CA, and put the associated private key on your server after they cracked
it. Which is much like leaving a big banner with "Yes, it was me!"
signed by you on the crime scene. :-)

But overall I agree. If they gained enough privilege to read the key
file, it's possible they're able to access the data as well. They might
be able to patch the server and have the password that protects the key
logged somewhere next time you type it in.

OTOH, I see no advantage in reading the key at connection time instead
of startup time (like every other daemon does). Encrypted key has an
interesting significance with backups. Someone may be able to steal one
backup of yours. They'll get old data (maybe you don't care much about
that), _and_ the key. You don't want them to be able to sign stuff or
impersonate your servers with it.

.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it

Hi,
A possible countermeasure on Windows platform,
inspired by Magnus.Thanks ;)
First we remove the passphrase from the key file,
making it plain.
Windows provides a feature "encrypted file system",
provide transparent encryption/decryption. We can log
on using the account we run Postgres with and encrypt
the plaintext key file. Then we logon using another
non-amin account, and start postgres using "runas"
service. Therefore the file is encrypted, only the
Postgres acount and the recovery agent(built-in
administrator by default) can read/modify it. The file
will remain encrypted when restored from backup.
I've tested it on my computer and it works.

cheers,
Changyu

--- dong changyu <dcy1_1999@yahoo.com> wrote:

Hi,
I��m using postgreSQL with SSL these days. The
version
I��m using is 8.0.3. I found that it��s impossible
to
use an encrypted key file.
When you use a protected server.key file, you will
be
prompted to input your passphrase EVERYTIME IT��S
USED, not only when you start the server but also
when
a client makes a connection. So you have to leave
the
key file un-protected. I think it��s a serious
vulnerability since the security relies on the
secrecy
of the private key. Without encryption, the only
thing
we can use to protect the private key is the access
control mechanism provided by the OS.
Any comments on this issue?

cheers,
Changyu

__________________________________
Discover Yahoo!
Have fun online with music videos, cool games, IM
and more. Check it out!
http://discover.yahoo.com/online.html

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

__________________________________
Discover Yahoo!
Find restaurants, movies, travel and more fun for the weekend. Check it out!
http://discover.yahoo.com/weekend.html

On Wed, 2005-06-08 at 10:00 -0700, dong changyu wrote:

Hi,
A possible countermeasure on Windows platform,
inspired by Magnus.Thanks ;)
First we remove the passphrase from the key file,
making it plain.
Windows provides a feature "encrypted file system",
provide transparent encryption/decryption. We can log
on using the account we run Postgres with and encrypt
the plaintext key file. Then we logon using another
non-amin account, and start postgres using "runas"
service. Therefore the file is encrypted, only the
Postgres acount and the recovery agent(built-in
administrator by default) can read/modify it. The file
will remain encrypted when restored from backup.
I've tested it on my computer and it works.

cheers,
Changyu

You mean that every process that runs as "postgres" has the ability to
read the file _without typing any password_? Or when you start
PostgreSQL it prompts for one? Can "administrator" read it _without
knowing password_?

I may be missing something, but what's the difference with a file like
this:

-r-------- 1 postgres postgres 50 Jan 15 21:15 akey

in any Unix system? Only "postgres" and "root" can read it.

How about backups? Does the backup process (I assume it runs as
administrator) store the key in cleartext?

.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it

Hi Marco,
The problem I described in the first mail is that
because of some unknown reasons, if you save the
server.key file with a passphrase, you will be
prompted to enter the passphrase every time you start
the server AND a client make a connection, which
actually forbids us to use a passphrase to protect the
key file, therefore the key file have to be saved in
plaintext without encryption.
EFS is a feature provided by Windows which will
encrypt any selected file using a symmetric algorithm,
the symmetric key will encrypted by the user��s public
key and the recovery agent��s public key and the
encrypted key will be saved within the file header.
Thus only the user and recovery agent can decrypt it.
And for another user, he cannot even open it (but can
delete it). So we can ensure no one can read and
modify it. Decryption is transparent to users and
applications. The operation system will do it
automatically if it can find appropriate private key.
The difference between this and -r-------- 1 postgres
postgres 50 Jan 15 21:15
is that the file is encrypted using EFS, while the
latter is remain plaintext.
When you backup the file, it remains encrypted. If you
restore the file to a file system which doesn��t
support EFS (non-NTFS), it will corrupt, else it will
remain encrypted.

cheers,
Changyu

--- Marco Colombo <pgsql@esiway.net> wrote:

You mean that every process that runs as "postgres"
has the ability to
read the file _without typing any password_? Or when
you start
PostgreSQL it prompts for one? Can "administrator"
read it _without
knowing password_?

I may be missing something, but what's the
difference with a file like
this:

-r-------- 1 postgres postgres 50 Jan 15 21:15
akey

in any Unix system? Only "postgres" and "root" can
read it.

How about backups? Does the backup process (I assume
it runs as
administrator) store the key in cleartext?

.TM.
--
____/ ____/ /
/ / / Marco
Colombo
___/ ___ / / Technical
Manager
/ / / ESI s.r.l.
_____/ _____/ _/
Colombo@ESI.it

On Wed, 2005-06-08 at 10:00 -0700, dong changyu
wrote:

Hi,
A possible countermeasure on Windows platform,
inspired by Magnus.Thanks ;)
First we remove the passphrase from the key file,
making it plain.
Windows provides a feature "encrypted file

system",

provide transparent encryption/decryption. We can

log

on using the account we run Postgres with and

encrypt

the plaintext key file. Then we logon using

another

non-amin account, and start postgres using "runas"
service. Therefore the file is encrypted, only the
Postgres acount and the recovery agent(built-in
administrator by default) can read/modify it. The

file

will remain encrypted when restored from backup.
I've tested it on my computer and it works.

cheers,
Changyu

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Thu, 2005-06-09 at 02:59 -0700, Changyu Dong wrote:

Hi Marco,
The problem I described in the first mail is that
because of some unknown reasons, if you save the
server.key file with a passphrase, you will be
prompted to enter the passphrase every time you start
the server AND a client make a connection, which
actually forbids us to use a passphrase to protect the
key file, therefore the key file have to be saved in
plaintext without encryption.
EFS is a feature provided by Windows which will
encrypt any selected file using a symmetric algorithm,
the symmetric key will encrypted by the user��s public
key and the recovery agent��s public key and the
encrypted key will be saved within the file header.

As long as the 'postgres' user has access to it w/o typing any password,
that's only a detail. Unless someone physically steals your disk, the
fact it's stored encrypted is irrelevant. The only thing that matters is
who can access it, and how.

Thus only the user and recovery agent can decrypt it.
And for another user, he cannot even open it (but can
delete it). So we can ensure no one can read and
modify it.

That's how the permission bits work in Unix. No need to encrypt the
file, we know permission bits actually work as expected under Unix. In
this case encryption adds no extra level of security on a running
system.

Decryption is transparent to users and
applications. The operation system will do it
automatically if it can find appropriate private key.
The difference between this and -r-------- 1 postgres
postgres 50 Jan 15 21:15
is that the file is encrypted using EFS, while the
latter is remain plaintext.

I fail to see the difference. On Windows, the 'postgres' user can read
it without password. 'Administrator' has access to it, too.

On Unix, with 400 permissions, the 'postgres' user can read it without
password. 'root' has access to it, too.

When you backup the file, it remains encrypted.

Then the backup is useless. If the secret key of the user 'postgres' is
lost (and it can be, since it is stored elsewhere, I think buried
somewhere where 'Administrator' can find it, maybe in user profile),
you'll never recover then content of the file.

If you
restore the file to a file system which doesn��t
support EFS (non-NTFS), it will corrupt, else it will
remain encrypted.

Now THAT puzzles me a lot. I can imagine it be restored in plain. I can
imagine it be restored encrypted. I have no way to justify the file
contents being lost only because of restoring it on FAT.

Anyway, that's not the point here.

The point is: on Windows, if someone breaks in your 'postgres' account,
he can read the key. If someone breaks in your 'administrator' account,
he can read the key. But other users cannot read it.

This level of protection is exactly the same provided by the 400
permissions above under Unix. If someone breaks in the 'postgres'
account, he can read the key. If someone breaks in the 'root' account,
he can read the key. But other users cannot read it.

I fail to see any difference in the above scenarios.

Encrypting the key in the .pem file (as supported by openssl) is
completely different! No one, ever, can access it w/o knowing the
password. That's why it takes the operator to type the password in.
Also backups are safe. And just as useful as the file itself, they can
be restored everywhere. If someone forgets the password, the contents
are lost, but that's true for the file itself. The backup is just what
you expect to be, a copy. You restore it, and get a _working_ copy for
the file, on every filesystem. The .pem key can be sent by email even,
as is (since it's base64 encoded).

The daemon should ask for the password only once, we agree on that.

Storing the key encrypted (in the openssl sense) doesn't help much
against root, if he's able to trick the operator into typing the
password again. If you're able to avoid it, that is you're in a highly
secure environment with operators trained _not_ to type the password in
"just to have the server restarted", .pem encryption adds a lot to your
security.

The EFS encryption as you described it adds nothing but a false sense of
security (and the ability to use some more buzzwords). The level of
protection is just the same of a Unix file with the right permissions.
The key point here is that both the 'postgres' user and 'administrator'
have _transparent_ access to the file contents. No password required.

.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it

The EFS encryption as you described it adds nothing but a
false sense of security (and the ability to use some more
buzzwords). The level of protection is just the same of a
Unix file with the right permissions.
The key point here is that both the 'postgres' user and
'administrator'
have _transparent_ access to the file contents. No password required.

While most of what you wrote is definitly correct, you missed a few
things about EFS.

1) Administrator does not necessarily have *transparent* access. It's
only the users access that is transparent.

2) It is quite possible to remove the administrator recovery key. This
can be used to protect *against* administrators reading the file. You do
*not* need to have *any* recovery key.

2b) It's even so that in Windows XP (and I think 2003), if it is *not* a
member of a domain, there *is* no default recovery key. In a domain,
it's the domain admins key, or whatever is configured in your domain
policy. In 2000, it's the local admin that first logs on to the box.

3) The recommended practice is to have the recovery key only available
off-line, locked into a separate building with half an army defending
it. Or something like that. At least put it in a smartcard that nobody
can access without going through lots and lots of safe checks on who
they are.

So it does offer a bit of extra security. "Just" to protect the key used
to set up the SSL sessions, I'm not sure it's worth it. Because again,
if they hack your admin account, they can get to your files *without*
going thruogh getting into the SSL stream.

//Magnus

--- Marco Colombo <pgsql@esiway.net> wrote:

As long as the 'postgres' user has access to it w/o
typing any password,
that's only a detail. Unless someone physically
steals your disk, the
fact it's stored encrypted is irrelevant. The only
thing that matters is
who can access it, and how.

That's how the permission bits work in Unix. No need
to encrypt the
file, we know permission bits actually work as
expected under Unix. In
this case encryption adds no extra level of security
on a running
system.

I fail to see the difference. On Windows, the
'postgres' user can read
it without password. 'Administrator' has access to
it, too.

On Unix, with 400 permissions, the 'postgres' user
can read it without
password. 'root' has access to it, too.

Then how about resore it from a backup to another
system? In this way the permission is bypassed but EFS
still works.

Then the backup is useless. If the secret key of the
user 'postgres' is
lost (and it can be, since it is stored elsewhere, I
think buried
somewhere where 'Administrator' can find it, maybe
in user profile),
you'll never recover then content of the file.

Right, but the user's private key can be exported into
a password protected pem file.

Now THAT puzzles me a lot. I can imagine it be
restored in plain. I can
imagine it be restored encrypted. I have no way to
justify the file
contents being lost only because of restoring it on
FAT.

If the encrypted file can be restored in plain to FAT,
it's useless. Anyone can backup and then resore it to
decrypt the file. And the file is not lost, you can
still restore it from the backup. It's not so hard to
find an NTFS partition, Postgres requires NTFS to run.

Anyway, that's not the point here.

The point is: on Windows, if someone breaks in your
'postgres' account,
he can read the key. If someone breaks in your
'administrator' account,
he can read the key. But other users cannot read it.

This level of protection is exactly the same
provided by the 400
permissions above under Unix. If someone breaks in
the 'postgres'
account, he can read the key. If someone breaks in
the 'root' account,
he can read the key. But other users cannot read it.

I fail to see any difference in the above scenarios.

If an intruder can break the postgres or root account,
he can read everything, as have been discussed, not
only the key but also the data file. So in this
situation, it's useless to protect the key only.

Encrypting the key in the .pem file (as supported by
openssl) is
completely different! No one, ever, can access it
w/o knowing the
password. That's why it takes the operator to type
the password in.
Also backups are safe. And just as useful as the
file itself, they can
be restored everywhere. If someone forgets the
password, the contents
are lost, but that's true for the file itself. The
backup is just what
you expect to be, a copy. You restore it, and get a
_working_ copy for
the file, on every filesystem. The .pem key can be
sent by email even,
as is (since it's base64 encoded).

Yes, the .pem file can be kept for distribution and
backup, but the working copy has to be plain.

The daemon should ask for the password only once, we
agree on that.

Yes, that's the ultimate solution. So we can use
encrypted key without any outside mechanism.

Storing the key encrypted (in the openssl sense)
doesn't help much
against root, if he's able to trick the operator
into typing the
password again. If you're able to avoid it, that is
you're in a highly
secure environment with operators trained _not_ to
type the password in
"just to have the server restarted", .pem encryption
adds a lot to your
security.

I'm not sure, but windows begins to support smart card
logon, therefore no password will be need and stored.

The EFS encryption as you described it adds nothing
but a false sense of
security (and the ability to use some more
buzzwords). The level of
protection is just the same of a Unix file with the
right permissions.
The key point here is that both the 'postgres' user
and 'administrator'
have _transparent_ access to the file contents. No
password required.

At least it make it impossible to restore the plain
key from backup.

.TM.
--
____/ ____/ /
/ / / Marco
Colombo
___/ ___ / / Technical
Manager
/ / / ESI s.r.l.
_____/ _____/ _/
Colombo@ESI.it

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

cheers,
Changyu

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Hi Magnus,
You are right. My description is based on windows 2000
which is the weakest one.
Have the recovery key only available off-line is a
good practice. And if you don't want recovery agent,
backup the user's private key is also appropriate. It
can be done without effort. You don't need an army or
something like that :)

cheers,
Changyu

--- Magnus Hagander <mha@sollentuna.net> wrote:

The EFS encryption as you described it adds

nothing but a

false sense of security (and the ability to use

some more

buzzwords). The level of protection is just the

same of a

Unix file with the right permissions.
The key point here is that both the 'postgres'

user and

'administrator'
have _transparent_ access to the file contents. No

password required.

While most of what you wrote is definitly correct,
you missed a few
things about EFS.

1) Administrator does not necessarily have
*transparent* access. It's
only the users access that is transparent.

2) It is quite possible to remove the administrator
recovery key. This
can be used to protect *against* administrators
reading the file. You do
*not* need to have *any* recovery key.

2b) It's even so that in Windows XP (and I think
2003), if it is *not* a
member of a domain, there *is* no default recovery
key. In a domain,
it's the domain admins key, or whatever is
configured in your domain
policy. In 2000, it's the local admin that first
logs on to the box.

3) The recommended practice is to have the recovery
key only available
off-line, locked into a separate building with half
an army defending
it. Or something like that. At least put it in a
smartcard that nobody
can access without going through lots and lots of
safe checks on who
they are.

So it does offer a bit of extra security. "Just" to
protect the key used
to set up the SSL sessions, I'm not sure it's worth
it. Because again,
if they hack your admin account, they can get to
your files *without*
going thruogh getting into the SSL stream.

//Magnus

---------------------------(end of
broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Thu, 2005-06-09 at 13:54 +0200, Magnus Hagander wrote:

The EFS encryption as you described it adds nothing but a
false sense of security (and the ability to use some more
buzzwords). The level of protection is just the same of a
Unix file with the right permissions.
The key point here is that both the 'postgres' user and
'administrator'
have _transparent_ access to the file contents. No password required.

While most of what you wrote is definitly correct, you missed a few
things about EFS.

[...stuff on EFS deleted...]

I agree on that. I'm not saying EFS is a bad idea. I'm only considering
the server key case. Anyway protecting against a malicious superuser is
the subject of a chapter on its own. There are many ways for a superuser
to hijack an user account. I'm not a Windows guy, but with a similar
setup under Unix, I'd do:

su - user -c "cp encrypted_file /tmp/unencrypted"

or anything equivalent. That is, at C level, open(2) the file, read(2)
it (the system gives me plaintext contents) write(2) it to another file.
The OP said he starts PostgreSQL with 'runas' which I get being the
Windows relative to 'su' (or 'cron'?). If Administrator can run programs
as the user, he can read the key file. That's the big difference.

So it does offer a bit of extra security. "Just" to protect the key used
to set up the SSL sessions, I'm not sure it's worth it. Because again,
if they hack your admin account, they can get to your files *without*
going thruogh getting into the SSL stream.

I think you're missing two points:

1) the purpose of getting the key is _not_ to be able to decrypt the SSL
stream (even if that's a nice consequence). The worse you can do is
_sign_ things with the key, that is, impersonate the server, mount man
in the middle attacks, and so on. Without anyone notice it for a while.
The whole point of encrypting the key is that a compromised key is worse
than a compromised system (and way more likely to go unnoticed).

2) there's not need for the intruder to scale to administrator powers.
If they break the 'postgres' account, they read the key. Even if they
break the server at SQL level, i.e. they gain superuser for the
database, they may be able to read it with a COPY SQL command, since
it's likely the key is accessible to the server (I'm not claiming the
latter is feasible - just in theory that's all they need).

The problem reported by the OP is a real one, since it prevents the
usage of an encrypted key. Not a big one, but still one. The "solution"
the OP posted later is not solving the problem at all.

BTW, even with the key is encrypted (the .pem way), the cleartext copy
must stay in the server memory space (possibly in a locked area). It may
be available to root ("debugging" the server), the user or the process
itself of course, if "they" manage to execute arbitrary code. There's
not way to make it 100% safe.

.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it

The EFS encryption as you described it adds nothing but a false
sense of security (and the ability to use some more

buzzwords). The

level of protection is just the same of a Unix file with

the right

permissions.
The key point here is that both the 'postgres' user and
'administrator'
have _transparent_ access to the file contents. No

password required.

While most of what you wrote is definitly correct, you missed a few
things about EFS.

[...stuff on EFS deleted...]

I agree on that. I'm not saying EFS is a bad idea. I'm only
considering the server key case. Anyway protecting against a
malicious superuser is the subject of a chapter on its own.
There are many ways for a superuser to hijack an user
account. I'm not a Windows guy, but with a similar setup
under Unix, I'd do:

su - user -c "cp encrypted_file /tmp/unencrypted"

or anything equivalent. That is, at C level, open(2) the
file, read(2) it (the system gives me plaintext contents)
write(2) it to another file.
The OP said he starts PostgreSQL with 'runas' which I get
being the Windows relative to 'su' (or 'cron'?). If
Administrator can run programs as the user, he can read the
key file. That's the big difference.

Yes, that is correct - runas is similar to su. But in order to do
"runas", you need the service accounts password. Once you are "root" on
a unix system, you can do "su - user" *without* the password. That's a
big difference.
(You can also use the postgres accounts smartcard, if you are using
smartcard logins, but the deal is that you need *something* that is
normally private to the account - even if you are an administrator)

So it does offer a bit of extra security. "Just" to protect the key
used to set up the SSL sessions, I'm not sure it's worth

it. Because

again, if they hack your admin account, they can get to your files
*without* going thruogh getting into the SSL stream.

I think you're missing two points:

1) the purpose of getting the key is _not_ to be able to
decrypt the SSL stream (even if that's a nice consequence).
The worse you can do is _sign_ things with the key, that is,
impersonate the server, mount man in the middle attacks, and
so on. Without anyone notice it for a while.
The whole point of encrypting the key is that a compromised
key is worse than a compromised system (and way more likely
to go unnoticed).

Correct, I was looking only at the issue of decrypting the stream.

Well, if you have admin on the server, you can replace the whole
postmaster binary. Which means game over no matter what.

2) there's not need for the intruder to scale to administrator powers.
If they break the 'postgres' account, they read the key.

That is the difference, though. Replacing the binary would require admin
privs, not just postgres privs. (Depending on the installation, of
course, but atl east the default install on win32 will explicitlyi deny
the service acconut permissions to change it's own binaries)

The problem reported by the OP is a real one, since it
prevents the usage of an encrypted key. Not a big one, but
still one. The "solution"
the OP posted later is not solving the problem at all.

Agreed.

BTW, even with the key is encrypted (the .pem way), the
cleartext copy must stay in the server memory space (possibly
in a locked area). It may be available to root ("debugging"
the server), the user or the process itself of course, if
"they" manage to execute arbitrary code. There's not way to
make it 100% safe.

If they ahve the postgres superuser, and can somehow send/receive files
(COPY for example), they can load a user defined C function, and just
read the server key from there. Sure, it's slightly more complicated
than reading the file, but not really all that much.

I guess we could read in the password ourselves and drop it in our
shared memory segment to pass to subprocesses - though that means they
can get to the password easier as well. Assuming OpenSSL has the APIs
for that, I haven't checked that. I'm unconvinced it makes enough of a
difference to be worthwhile, though.

(BTW, am I correct in reading this as a problem that only appears on
win32, because of the exec nature of the backend, right? Or does it show
up on Unix as well?)

//Magnus

I don't know, I just tested it on win32.

Changyu

--- Magnus Hagander <mha@sollentuna.net> wrote:

(BTW, am I correct in reading this as a problem that
only appears on
win32, because of the exec nature of the backend,
right? Or does it show
up on Unix as well?)

//Magnus

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Thu, 2005-06-09 at 05:21 -0700, Changyu Dong wrote:

--- Marco Colombo <pgsql@esiway.net> wrote:

As long as the 'postgres' user has access to it w/o
typing any password,
that's only a detail. Unless someone physically
steals your disk, the
fact it's stored encrypted is irrelevant. The only
thing that matters is
who can access it, and how.

That's how the permission bits work in Unix. No need
to encrypt the
file, we know permission bits actually work as
expected under Unix. In
this case encryption adds no extra level of security
on a running
system.

I fail to see the difference. On Windows, the
'postgres' user can read
it without password. 'Administrator' has access to
it, too.

On Unix, with 400 permissions, the 'postgres' user
can read it without
password. 'root' has access to it, too.

Then how about resore it from a backup to another
system? In this way the permission is bypassed but EFS
still works.

Either the Windows backup contains the private key of the user or not.

If not, the backup is incomplete and useless (to get the file contents).
You may get other files from it, but that's not the point. You may just
not include the key file in _that_ backup.

If you have two backups, one "normal" and another "safe", just put the
keyfile on the safe one, along with the other private keys. You can do
the same under Unix of course.

If your single backup contains the user private key, EFS is bypassed as
well.

This is going offtopic. The EFS approach is no different from any
encrypted filesystem, nothing new under the sun. It shares the weakness
of any system that lets you access the data at runtime w/o password.

Then the backup is useless. If the secret key of the
user 'postgres' is
lost (and it can be, since it is stored elsewhere, I
think buried
somewhere where 'Administrator' can find it, maybe
in user profile),
you'll never recover then content of the file.

Right, but the user's private key can be exported into
a password protected pem file.

Save the server key in the same way then. Put the server key and the
user key together.

[...]

If an intruder can break the postgres or root account,
he can read everything, as have been discussed, not
only the key but also the data file. So in this
situation, it's useless to protect the key only.

Yes, it has been discussed: the purpose of the key is not protecting the
data, but protecting your identity. If the key is compromised, they can
impersonate you. Generally, this is much bigger a damage. They can
create fake data, _signed by you_.

[...]

Yes, the .pem file can be kept for distribution and
backup, but the working copy has to be plain.

The daemon should ask for the password only once, we
agree on that.

Yes, that's the ultimate solution. So we can use
encrypted key without any outside mechanism.

We agree on that. That's the _only_ solution if you want that kind of
security.

Storing the key encrypted (in the openssl sense)
doesn't help much
against root, if he's able to trick the operator
into typing the
password again. If you're able to avoid it, that is
you're in a highly
secure environment with operators trained _not_ to
type the password in
"just to have the server restarted", .pem encryption
adds a lot to your
security.

I'm not sure, but windows begins to support smart card
logon, therefore no password will be need and stored.

That changes nothing. Somehow the key as to be given, unencrypted, to
the server. Be it an operator typing a password, or inserting a
smartcard, a patched server can store the key in cleartext anywhere.
You have to teach your operators to think twice before performing
anything that lets the server access the key. With your solution, you're
letting the server access the key automatically.

The EFS encryption as you described it adds nothing
but a false sense of
security (and the ability to use some more
buzzwords). The level of
protection is just the same of a Unix file with the
right permissions.
The key point here is that both the 'postgres' user
and 'administrator'
have _transparent_ access to the file contents. No
password required.

At least it make it impossible to restore the plain
key from backup.

The safety of that backup lies only on its incompleteness.

If you include the user key in the same backup, there's no security.
If you don't include the user key (and thus create an incomplete
backup), it's easier not to include the server key either, and put it in
the same place you put the user key. They are both "private keys".

Including a useless copy of the server key encrypted with the user key
(stored elsewhere) is just a perverse way to gain nothing.
But I agree that sometimes perverse systems make perverse things look
natural. :-)

.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it

On Thu, 2005-06-09 at 15:04 +0200, Magnus Hagander wrote:
[...]

Yes, that is correct - runas is similar to su. But in order to do
"runas", you need the service accounts password. Once you are "root" on
a unix system, you can do "su - user" *without* the password. That's a
big difference.
(You can also use the postgres accounts smartcard, if you are using
smartcard logins, but the deal is that you need *something* that is
normally private to the account - even if you are an administrator)

Is that at application level or system level? You know I can install a
patched su that asks root for passwords as well, but the problem is with
the seteuid() system call, not su. You can (with SELinux) limit root
powers a lot, but that's not the point.

[...]

I guess we could read in the password ourselves and drop it in our
shared memory segment to pass to subprocesses - though that means they
can get to the password easier as well. Assuming OpenSSL has the APIs
for that, I haven't checked that. I'm unconvinced it makes enough of a
difference to be worthwhile, though.
(BTW, am I correct in reading this as a problem that only appears on
win32, because of the exec nature of the backend, right? Or does it show
up on Unix as well?)

Is the Unix version much different? I think the postmaster just forks
and execs the backends. But, aren't connections handled by the
postmaster? All the SSL thing should happen before the fork I think. Is
the Windows model different? Do backends handle SSL negotiation?

.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it

Yes, that is correct - runas is similar to su. But in order to do
"runas", you need the service accounts password. Once you

are "root"

on a unix system, you can do "su - user" *without* the password.
That's a big difference.
(You can also use the postgres accounts smartcard, if you are using
smartcard logins, but the deal is that you need *something* that is
normally private to the account - even if you are an administrator)

Is that at application level or system level? You know I can
install a patched su that asks root for passwords as well,
but the problem is with the seteuid() system call, not su.
You can (with SELinux) limit root powers a lot, but that's
not the point.

I think it's at the system level. At least there is no API to do it.

I guess we could read in the password ourselves and drop it in our
shared memory segment to pass to subprocesses - though that

means they

can get to the password easier as well. Assuming OpenSSL

has the APIs

for that, I haven't checked that. I'm unconvinced it makes

enough of a

difference to be worthwhile, though.
(BTW, am I correct in reading this as a problem that only

appears on

win32, because of the exec nature of the backend, right? Or does it
show up on Unix as well?)

Is the Unix version much different? I think the postmaster
just forks and execs the backends.

It forks. It doesn't exec. As such, it inherits all the memory from the
postmaster.

But, aren't connections
handled by the postmaster? All the SSL thing should happen
before the fork I think. Is the Windows model different? Do
backends handle SSL negotiation?

On win32 we do fork() + exec(), and the OpenSSL library requires a new
initialization, including reading the key. I should say I'm speaking
from memory of stuff I looked at a longt ime ago, though - it could
probably do with someone looking throuhg exactly how it is now ;-)

IIRC, the problem is that the openssl structs contain function pointers,
which are not guaranteed to point to the same thing in the child.

//Magnus

--- Marco Colombo <pgsql@esiway.net> wrote:

Either the Windows backup contains the private key
of the user or not.

If not, the backup is incomplete and useless (to get
the file contents).
You may get other files from it, but that's not the
point. You may just
not include the key file in _that_ backup.

If you have two backups, one "normal" and another
"safe", just put the
keyfile on the safe one, along with the other
private keys. You can do
the same under Unix of course.

If your single backup contains the user private key,
EFS is bypassed as well.

I don't think we should include everything in ONE
backup. Incremental and differential backup are often
used, but they need several backup to resore the
system. And as I have said, the user's private key can
be exported in a standard pem file, encrypted by a
password only known by himself(not use EFS, just the
same as any encrypted pem file), if you don't know the
password, how could you bypassed EFS?

This is going offtopic. The EFS approach is no
different from any
encrypted filesystem, nothing new under the sun. It
shares the weakness
of any system that lets you access the data at
runtime w/o password.

Save the server key in the same way then. Put the
server key and the
user key together.

That is not a good idea, you have to encrypt the
server's key and delete the plain key before you
backup.

[...]

If an intruder can break the postgres or root

account,

he can read everything, as have been discussed,

not

only the key but also the data file. So in this
situation, it's useless to protect the key only.

Yes, it has been discussed: the purpose of the key
is not protecting the
data, but protecting your identity. If the key is
compromised, they can
impersonate you. Generally, this is much bigger a
damage. They can
create fake data, _signed by you_.

But this key is only used for SSL of my postgres, so
even it is copromised, the only way the intruder to
use it is to decrypt and forge data between client and
postgres. If he can access the data directly, why not
he do so?

[...]

Yes, the .pem file can be kept for distribution

and

backup, but the working copy has to be plain.

The daemon should ask for the password only

once, we

agree on that.

Yes, that's the ultimate solution. So we can use
encrypted key without any outside mechanism.

We agree on that. That's the _only_ solution if you
want that kind of
security.

That changes nothing. Somehow the key as to be
given, unencrypted, to
the server. Be it an operator typing a password, or
inserting a
smartcard, a patched server can store the key in
cleartext anywhere.
You have to teach your operators to think twice
before performing
anything that lets the server access the key. With
your solution, you're
letting the server access the key automatically.

It's also a problem with encrypted pem.

The EFS encryption as you described it adds

nothing

but a false sense of
security (and the ability to use some more
buzzwords). The level of
protection is just the same of a Unix file with

the

right permissions.
The key point here is that both the 'postgres'

user

and 'administrator'
have _transparent_ access to the file contents.

No

password required.

At least it make it impossible to restore the

plain

key from backup.

The safety of that backup lies only on its
incompleteness.

If you include the user key in the same backup,
there's no security.
If you don't include the user key (and thus create
an incomplete
backup), it's easier not to include the server key
either, and put it in
the same place you put the user key. They are both
"private keys".

Including a useless copy of the server key encrypted
with the user key
(stored elsewhere) is just a perverse way to gain
nothing.
But I agree that sometimes perverse systems make
perverse things look
natural. :-)

In your logic, then all the encryption algorithms are
"perverse" because they rely on incompleteness, you
can never include the key itself in the encrypted
data, you always need to keep something secret.

cheers,
Changyu

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Thu, 2005-06-09 at 15:53 +0200, Magnus Hagander wrote:
[...]

I guess we could read in the password ourselves and drop it in our
shared memory segment to pass to subprocesses - though that

means they

can get to the password easier as well. Assuming OpenSSL

has the APIs

for that, I haven't checked that. I'm unconvinced it makes

enough of a

difference to be worthwhile, though.
(BTW, am I correct in reading this as a problem that only

appears on

win32, because of the exec nature of the backend, right? Or does it
show up on Unix as well?)

Is the Unix version much different? I think the postmaster
just forks and execs the backends.

It forks. It doesn't exec. As such, it inherits all the memory from the
postmaster.

Oh, I need to check it out then. I thought the 'postmaster' executable
forks and execs 'postgres', and then they share mem via shmat(2). But
now I see they are the same executable - weird how I've never noticed
that before.

But, aren't connections
handled by the postmaster? All the SSL thing should happen
before the fork I think. Is the Windows model different? Do
backends handle SSL negotiation?

On win32 we do fork() + exec(), and the OpenSSL library requires a new
initialization, including reading the key. I should say I'm speaking
from memory of stuff I looked at a longt ime ago, though - it could
probably do with someone looking throuhg exactly how it is now ;-)

IIRC, the problem is that the openssl structs contain function pointers,
which are not guaranteed to point to the same thing in the child.

Ouch, I see the problem. You do need to pass the unencrypted key around
then, assuming openssl supports such a thing. Now I also see it's
useless to setup the openssl context only to destroy it with exec. So it
has to be the child to handle it.

.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it