Re: How to revoke privileged from PostgreSQL's superuser

I should clarify something I said above - I know that some data must be
encrypted within the database. My point was that during the design phase
you should take the schema, cross out all columns that will be encrypted,
and ask what's left. If it's nothing but the primary key and things you'll
never sort on (or require uniqueness) then it's reasonable to ask if
storing the information in a relational database is the best approach. An
alternative would be storing each encrypted record as an S3 file or Hadoop
HBase record. In both cases the filename/key would be based on something
like a hashed email address. In the case of S3 it's important to remember
that there is a limit on the number of entries although I don't remember
what it is. Some people have hit it when using it like this.

It's a little more work but it's arguably a lot more secure if you make
full use of AWS and Hadoop security. On the other hand keeping everything
in a single place is a lot more convenient and it only requires one
security audit, not two.

By 'shadow' table I mean that there's no reason why everyone needs access
to everything. A classic example is the user table. Nearly everyone keeps
the password (hopefully hashed!) in the user table. There's no reason to do
this - it's just as easy to keep the passwords in a separate table with the
same primary key. That will only affect the bits of your app that handle
authentication - verifying a password or changing it. If anyone manages to
do a SQL injection attack to list the contents of the user table they'll
learn the user accounts but probably won't get the user's passwords, esp.
if you give that table some innoculous name instead of 'password'.

But since such a limited amount of code that requires access to that table
you can make a design decision that you'll use a separate database user
when authenticating a user. That user will have access to the password
table but the regular user won't have any access to it. If you want to be
really secure you could put the password table into a different schema. The
table is then entirely hidden from someone who has full access to your main
schema. Hence "shadow" table. (Plus the fact that user passwords are kept
in the /etc/shadow file on unix/linux systems.)

This is a pretty powerful technique. E.g., some people add a column that
keeps a salted hash of the data in a row. An intruder won't know to change
the hash value so you can dramatically improve security by checking that
hash value when retrieving a value from the database. If it doesn't match
you throw an alert and refuse to use the value. However this leaves a trace
on the table to the intruder knows that their change will be detected.
That's often a Good Thing - it's a deterrent - but you might prefer to let
intruders make changes so you can determine they're in your system. You
could use a shadow table to hold the hashes and view so that your app
always sees the hash but an intruder doesn't.

You could even go a step further and have a trigger that updates a shadow
table with the 'before' and 'after' values on an insert/update/delete. (Or
pgaudit if you're on postgresql specifically - the general approach works
with any database that supports triggers.) Nobody intruder knows about
this, no application developer knows about this, only the DBA and security
team. That lets you track down every change - what changed, when, who did
it (including IP address if you capture connection info), etc. At most an
intruder might see there's a trigger but if it works via a stored procedure
with the appropriate permissions they won't know what you're doing with it.

The one thing to be careful about is that you can't use a trigger to update
the hash value mentioned above. That would also be fired by an intruder so
it won't tell you anything. It has to be done programmatically, but (at
least with java) it could be implemented by a security team that uses AOP
so the developers know there's a few extra columns but they don't do
anything with them... or if you're paranoid the security team has its own
persistence mechanism so the developers are completely unaware that this is
happening since the hash is written to a different schema & table.

Hmm, I should probably write a blog entry for this...

Bear

On Mon, Aug 6, 2018 at 8:13 AM, Evan Bauer <evanbauer(at)mac(dot)com> wrote:

> Bejita,
>
> I suggest you step back and think about the problem from the point of view
> of the desired security outcome — that of protecting data from improper use
> by administrators. Some of the elements that (to my mind) ought to be part
> of achieving that outcome are:
>
> 1. Determine and document your organizations data access policies. They
> could be very simple, but it is important to document and share them.
> 2. Make use of a privileged access management scheme so that no one has
> unfettered access to superuser (postgres, root, et al) passwords, but has
> to check them out from an audited system for a specific task and time
> period, with appropriate approval processes if needed.
> 3. Use pgaudit to maintain an independent record of all sensitive access.
> The doc is at: https://github.com/pgaudit/pgaudit/blob/master/README.md
> 4. Create a set of administrative roles privileged to only the needs of
> the tasks required. Under normal circumstances, no one should use the
> ‘postgres’ account for production access. This also provides a means of
> enforcing compliance to your policies. Tom Vondra wrote a good
> introduction here: https://blog.2ndquadrant.com/
> auditing-users-and-roles-in-postgresql/
> 5. Setup automated (I tend to use ELK or Splunk) examination of the audit
> logs for violations and anomalies. Human review at regular intervals will
> also make your regulators or security auditors happier (they are never
> really happy.)
> 6. Make use of row-level access control and encryptions as appropriate to
> protect your data. This blog post by Jonathan Katz is a good introduction:
> https://info.crunchydata.com/blog/a-postgresql-row-level-
> security-primer-creating-large-policies
>
> There is a lot of thought and work that goes into executing the steps
> above, but administering systems and databases that handle sensitive data
> is a serious responsibility and requires requirements definition, planning,
> architecture, execution, and then continuous monitoring and improvement.
> As someone new to the DBA role, you should talk to your architecture
> colleagues as you have some good and serious work ahead of you.
>
> Cheers,
>
> - Evan
>
>
>
> > On Aug 6, 2018, at 09:43, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > Bear Giles <bgiles(at)coyotesong(dot)com> writes:
> >> In postgresql the equivalent user is 'postgres'. Nobody should ever be
> >> logged in as that user once you've created the initial user(s). What
> >> postgresql calls a 'superuser' is just a user with a few permissions
> set by
> >> default. It's easy to grant the same privileges to any user, or drop
> them
> >> from someone created as a superuser.
> >
> > Well, more to the point, a superuser is somebody with the rolsuper bit
> > set in their pg_authid entry. You can revoke the bootstrap superuser's
> > superuserness if you have a mind to -- see ALTER USER. However, as
> > everyone has pointed out already, this is a bad idea and you will end
> > up undoing it. (Figuring out how to do that without a reinstall is left
> > as penance for insisting on a bad idea. It is possible, and I think
> > even documented.)
> >
> > However: a whole lot of what the bootstrap superuser can do is inherent
> > in being the owner of all the built-in database objects, and that you
> > cannot get rid of. Objects have to be owned by somebody.
> >
> > regards, tom lane
> >
>
>
>