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

Very true - when you've been hitting credit card and health care nails you
tend to forget that not every problem requires the same level of hammer!
Ask me what's required for anything in the Hadoop ecosystem. shudder.

However it's also true that there's no such thing as a site or database too
small to attack now. The bad guys have figured out that people are lazy and
getting into a database, any database, can reveal information that can be
used in the next attack. I think worrying about the dba and sysadmin is
misplaced - if you can't trust them you have bigger problems so you need to
vet them carefully and then trust them - but in many places all uniquely
identifiable information (e.g., SSN) or even personally identifiable
information (email address, phone number, street address etc.) have be
encrypted now. Everywhere.

Is it possible that your boss knows about the appropriate requirements in
your industry but doesn't fully understand them or isn't communicating them
well? I could easily see a requirement that the data be encrypted (so
nobody can read it) being confused with a requirement that no user have the
ability have the ability to SELECT from a table. Very different things.

> 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

On Mon, Aug 6, 2018 at 4:46 PM, <bejita0409(at)yahoo(dot)co(dot)jp> wrote:

> 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(at)mac(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > >
> > > Bear Giles <bgiles(at)coyotesong(dot)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
> > >
> >
> >
> >
>
>