Hi all, Thanks for giving a lot of points of view. I know superuser can not be revoked apart of privileges, because it does not like nosuperusers who's privileges can be made from GRANT statement.
As you all mentioned, I will re-check more about our system designation. I am inclined to encrypt sensitive data or using some monitoring softs like pgaudit to monitor accesses. Thanks, -- bejita --- evanba...@mac.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 <t...@sss.pgh.pa.us> wrote: > > > > Bear Giles <bgi...@coyotesong.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 > > > > >
