Re: How to revoke privileged from PostgreSQL's superuser

Encrypting data within the database(*) severely limits its usability - you
can't use it in queries, etc. In some cases it's not a problem since you'll
never want to use it in a query anyway, or you can use a proxy(**). But as
a general rule I think if you're encrypting much of your data then a
traditional database isn't the right solution to your problem.

(*) the underlying filesystem and should still be encrypted. The backups
should also be encrypted - a lot of people forget to do that.

(**) for instance you might consider the person's email address to be
sensitive information that should be encrypted but you still want to be
able index the field so you can perform a rapid lookup. In that case you
can add a salted hash of the email and index that. Your app knows how to
perform the same hash so it can quickly find the record but it's totally
opaque to an intruder.

It's important to use a salted hash since an unsalted hash is no longer
secure since a knowledgeable intruder probably already has a list of emails
from other attacks and can easily compute the values to check. At the
minimum a salted hash is something like sha1(email + "my secret") (NOT
sha1("my secret" + email)) but you should really use one of the standard
algorithms to convert a passphrase and salt into an encryption key.
(PBE2K?) For performance reasons you might not want to perform all 1000+
iterations required for an encryption key but it's important to use a
standard algorithm since it's really easy to create hashes that aren't
nearly as strong as you think. E.g., there's a huge difference between
hash(value + salt) and hash(salt + value).

In this case the salt has to systemwide, or at least easily computed given
the email address but not derived from it (e.g., you can use the last few
digits of the hash of the email address as the index into a lookup table
but don't use the hash itself.) In most cases it's best to add a 'salt'
column to the record, perhaps in a shadow table that's not obvious to an
intruder, but you can't do that with anything used in a lookup since you
have no idea what value to use.

On Mon, Aug 6, 2018 at 7:19 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Monday, August 6, 2018, <bejita0409(at)yahoo(dot)co(dot)jp> wrote:
>
>>
>> I have a request for revoking the access to user's data from DBA-user.
>> I think the request is right because users should be the only ones can
>> access their data.
>>
>
> User then needs to encrypt data prior to storing it. Superuser can still
> access the data but would be challenged to make sense of it,
>
> Usually DBAs are tasked with backups which requires read access to all
> relevant data.
>
> David J.
>
>