Re: Re: How to revoke privileged from PostgreSQL's superuser

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

--- evanbauer@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 <tgl@sss.pgh.pa.us> wrote:
> >
> > Bear Giles <bgiles@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
> >
>
>
>