bejita0409@xxxxxxxxxxx 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
