3

When you install a RDBMS (say, PostgreSQL) and create a database, without taking any additional steps, what can you assume about the security/confidentiality of the data? I mean, is it encrypted by default, so even a person with physical access to the machine (or root password to the OS) would have trouble accessing it without the superuser credentials? Are the credentials (or some easily crackable equivalent) stored somewhere, like in a file?

Sorry if that sounds obvious, but for me it's not. Searching for "database encryption" yields many results about how to encrypt a database, what use cases it's good for, etc, but doesn't answer my basic question. For an ordinary, no special needs scenario (who has the password accesses the data, who hasn't doesn't), do I have to take additional steps for securing my data?

Contrast that question to OS filesystems and Truecrypt: for the former, it's obvious that nothing is confidential at all, you must use full-disk encryption to protect your data; for the latter, it's easy to see the credentials are not stored anywhere, if you don't have the password/keyfile the data in front of you is useless. What assumptions can I make about RDBMS (at least the most common, production-oriented ones)?

mgibsonbr
  • 2,925
  • 2
  • 22
  • 35

4 Answers4

4

The data is not out of the box encrypted. Files are stored within a subdirectory /var/lib/postgresql/. See: http://www.postgresql.org/docs/8.4/static/storage-file-layout.html

While the OS is running its limited to being read by root/postgres user. Remember, if a user has permission to reboot from a live cd or into single user mode or take out the hard drive and there's no full disk encryption, they can read the files. Granted the files aren't easy to navigate through -- they are stored in a binary format, but you can often pick out ASCII strings from the files (and a dedicated attacker could get at the full underlying data).

dr jimbob
  • 39,312
  • 8
  • 94
  • 164
  • 1
    Well, to me that was not obvious, hence the question... I mean, whether in general a RDBMS would rely on the underlying OS for encryption of whether it would do it on its own (out of the box). Now that I know it doesn't do anything out of the box, I'll look at the specific actions I must take. Thanks for the feedback – mgibsonbr Mar 30 '12 at 20:19
  • Sorry for the 'obviously' comment; changed to 'Remember'). I mentioned it thinking most security folks would be aware of needing disk encryption to keep data secret (not linux permissions) if someone has physical access to a drive; but obviously many users aren't (so felt compelled to mention). – dr jimbob Mar 30 '12 at 21:13
3

When you install a RDBMS you shouldn't take as an assumption that data is stored encrypted. In the case of Postgre, this documentation might be of help: http://www.postgresql.org/docs/8.1/static/encryption-options.html

J.D.
  • 298
  • 2
  • 4
  • Thanks, I have seen that document before, my doubt was just how things would be if I did not employ any of those methods. Now I see I must take action, and I'm inclined to use full disk encryption. – mgibsonbr Mar 30 '12 at 20:10
2

Postgres supports encryption but it is not enabled by default.

considering you have a default setup:

To get to the data you would need access to the localhost. So trying to access the database from an external source is not possible. So you would need to get to the root account of the server it's running on or an account on the server that can behave like localhost.

The root password is stored...in the database. However with postgres you can always get in once you are superuser. MySQL for instance requires you to start the server in a special mode. How they store your encryption keys/passwords depends on the product, you should look at every single one's manual.

Lucas Kauffman
  • 54,437
  • 17
  • 116
  • 196
  • Sorry, I didn't understand the last part. When you say "the password is stored in the database", that means if I manually enable encryption of the database, then the password will be safe, even if the filesystem itself is not encrypted? (using a Truecrypt analogy, having a copy of the password/keyfile inside the volume doesn't help anyone mount it in case it's not mounted yet) – mgibsonbr Mar 30 '12 at 20:06
  • Oh no, the default root password to log in as the DB's administrative user is stored in the database. It is hashed by default. Postgres doesn't have a default administrative user if I recall correctly. MySQL does however. – Lucas Kauffman Mar 30 '12 at 20:11
  • Ok, thanks for the clarification. Since it's stored, then it's useless against someone with physical access (in case of the theft of the machine, for instance), so full disk encryption is really the way to go. – mgibsonbr Mar 30 '12 at 20:32
  • Yes indeed, that's the only way to be sure. – Lucas Kauffman Mar 30 '12 at 20:47
1

This depends on your database and how you install it. For example, for PostgreSQL, your assumptions are different if you install from source or you install from most repositories.

The source installation gives you the following assumptions:

  • no database encryption (db encryption is hard btw)
  • No access from other machines
  • unlimited access from the local machine regardless of password.

Most OS packages give you the following assumptions:

  • no db encryption
  • No access from other machines
  • access from local machine only via the "postgres" system user account (effectively this means sudoers and root can access the data from the local machine).

The basic thing though is you need to be intimately familiar with the security of your rdbms and familiar with the default configuration provided by your method of installation.

Chris Travers
  • 429
  • 4
  • 5