Re: How to revoke privileged from PostgreSQL's superuser

bejita0409@yahoo.co.jp writes:

> Hello,
>
> I am a newbie DBA.
>
> 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.
> But DBA-user also need full access to the other data? It means that DBA-user also needs to be a superuser.
>
> So I conclude the request that how to revoke privileged from superuser in postgres.
>
> As my knowledge, the superuser in PostgreSQL bypasses all the permission check.
> So that, there is no way to do it in PostgreSQL, is that right?
>
> Is there some DBAs are faced with this before?
>

There are certainly DBAs who have had very similar requests. Often, they
are generated by non-technical people who don't really understand how
the technology works and have concerns over who has access to the data
(a common one is people who are concerned about who has access to their
email - we had a similar challenge from our Chief legal officer who was
paranoid sys admins were reading his highly sensitive email, this is
despite the fact 1 week previously, I was on a flight sitting in the
seat behind him while he read his email on his iPad, which I could (but
didn't) easily read over his shoulder!).

The key to handling this sort of request is to dig deeper to understand
what the real risk is that they want addressed and work out how you can
do that within the constraints of the technology and what makes sense
within your context. I'm sure someone will respond to this thread with
all sorts of highly restrictive and powerful controls that will restrict
access to the data, but if they are not appropriate for your business
context, will likely cripple the very process you are trying to
protect. All controls/restrictions cause some level of inconvenience -
the challenge is in getting the balance right so that the identified
risk is mitigated with the least level of inconvenience to normal
business operations.

The reality is that at various times, humans will need the ability to
access the data in ways which will limit, if not completely prevent,
your ability to restrict access. This is particularly relevant for
system and database administrators. It is pretty much 100% impossible to
guarantee that a sys admin or DBA cannot access data. However, what you
can do is approach the problem slightly differently and look at ways to
make this access harder and more importantly, make sure that all access
is logged appropriately and can be audited, ensuring the
logging/auditing system is also protected from deletion or modification.

Other posts in the thread include some good pointers on what you can do
to help with this. The principals are pretty straight forward. Possibly
the most important thing to do is ensure there is no 'anonymous' access
e.g. you cannot login to the database as 'postgres' or some other
generic account which multiple people have access to. Instead, ensure
that everyone with any level of administrator privilege has to login
using an account which is specific to them and not shared. The second
thing to do is ensure the logging level is appropriate and that all
logging is also stored/recorded on a system which the administrator does
not have access to and ensure the level of privileges every
individual has is at the minimum they require to get the job done. It is
also important that logs and audit trails are regularly reviewed to
ensure nobody is abusing the system and all controls are still
appropriate (things change, new systems come on line, old ones a
retired, business processes change etc).

If necessary, consider controls which restrict access to accounts with
extended privileges to certain hosts e.g. DBA 'Phil' can only log into
the database from server xxx.xxx.xxx.xxx and he can only log into that
server between 9am and 5pm Mon - Fri etc. Maybe he has to use a hardware
token etc. 

In most cases, provided you can give strong guarantee that unauthorised
data access can be identified, you will satisfy the security
requirements and this is often far more feasible than outright blocking
of access. 

Finally, it is also important that all staff are aware of the
organisations policies, procedures and controls regarding data
access. They need to know what is expected of them and what is
unacceptable.  


--
Tim Cross