Re: How to revoke privileged from PostgreSQL's superuser

[Rui DeSousa <rui@xxxxxxxxxxxxx>]

On Aug 10, 2018, at 7:21 PM, Benedict Holland <benedict.m.holland@xxxxxxxxx> wrote:

Oh this is an easy one. There are simpler solutions for PITR. Also, a PITR is a very specific case of a database use, if it even uses one. Generally speaking, you would not want to keep encrypted data within a database. There simply isn't a need for it. Just use a file or a folder. You can't do anything that you would normally do with a database if you can't read or access any of your objects. It would just be a table of binary objects without names, possibly access or creation dates depending on the level of paranoia. Literally, you would have an int column and a binary object column. What can you honestly do with that? You can't even link it to other objects. It has no relational structure, hense the question. If there isn't a relationship to anything then a relational database wouldn't really help anything. 

Also, I would probably keep the encryption key within the database anyway. Otherwise, your objects could get permanently lost making the whole thing moot in the first place. 

Look, you either trust your DBAs or you don't. If you don't trust them, why are they your DBA? This is like writing unit tests for unit tests or having even higher levels of privilege than a superuser. It's turtles all the way down.

~Ben

On Fri, Aug 10, 2018 at 4:12 PM, Rui DeSousa <rui@xxxxxxxxxxxxx> wrote:

On Aug 6, 2018, at 10:45 AM, Bear Giles <bgiles@xxxxxxxxxxxxxx> wrote:

then it's reasonable to ask if storing the information in a relational database is the best approach.

Why? Just because its encrypted doesn’t mean it shouldn’t be stored in the database. What about PITR, how would that be handled?  You basically would have to reimplement things the RDBMS system gives you for free by storing it outside the database.  Don’t forget it's called a management system for a reason.

With that logic then you should use flat files for encrypted data and unencrypted data.  It’s what was done many moons ago; and its unstructured haphazard approach gave rise to RDBMS systems.

You cannot say that encrypted data does not belong in a RDBMS system… that is just false.  Hell, I’ve stored blobs in a RDMBS system which could have easily been stored in a different system if need be.  It’s a design choice and what fits the application and budget needs.

Encrypting sensitive information and storing in the database is a valid use case.  It may be only a few columns that are encrypted or a complete document (blob); there is no need to increase complexity just to move those columns out of the database.