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
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@xxxxxxxxxxx> 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@xxxxxxx 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@xxxxxxxxxxxxx> wrote:
> >
> > Bear Giles <bgiles@xxxxxxxxxxxxxx> 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
> >
>
>
>
