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 wrote:
> >
> > Bear Giles 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
> >
>
>
>
