Re: How to revoke privileged from PostgreSQL's superuser

Bejita,

I suggest you step back and think about the problem from the point of view of the desired security outcome — that of
protectingdata from improper use by administrators.  Some of the elements that (to my mind) ought to be part of
achievingthat outcome are: 

1. Determine and document your organizations data access policies.  They could be very simple, but it is important to
documentand share them. 
2. Make use of a privileged access management scheme so that no one has unfettered access to superuser (postgres, root,
etal) passwords, but has to check them out from an audited system for a specific task and time period, with appropriate
approvalprocesses 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
complianceto 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
reviewat 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
JonathanKatz 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
thathandle 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
architecturecolleagues 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
>