pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to revoke privileged from PostgreSQL's superuser
40+ messages / 15 participants
[nested] [flat]
* How to revoke privileged from PostgreSQL's superuser
@ 2018-08-06 09:48 bejita0409@yahoo.co.jp
0 siblings, 5 replies; 40+ messages in thread
From: bejita0409@yahoo.co.jp @ 2018-08-06 09:48 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Hello,
I am a newbie DBA.
I have a request for revoking the access to user's data from DBA-user.
I think the request is right because users should be the only ones can access their data.
But DBA-user also need full access to the other data? It means that DBA-user also needs to be a superuser.
So I conclude the request that how to revoke privileged from superuser in postgres.
As my knowledge, the superuser in PostgreSQL bypasses all the permission check.
So that, there is no way to do it in PostgreSQL, is that right?
Is there some DBAs are faced with this before?
Thanks,
--
bejita
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: [External] How to revoke privileged from PostgreSQL's superuser
@ 2018-08-06 10:19 Vijaykumar Jain <vjain@opentable.com>
parent: bejita0409@yahoo.co.jp
4 siblings, 0 replies; 40+ messages in thread
From: Vijaykumar Jain @ 2018-08-06 10:19 UTC (permalink / raw)
To: bejita0409@yahoo.co.jp <bejita0409@yahoo.co.jp>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
I am not sure superuser can be selectively restricted via queries, but I am not sure, have not tried.
But maybe you can try restricting the super user access to the db from all hosts via the pg_hba.conf.
Fore eg. I have a user
monitor | Superuser
and
in my /etc/postgresql/10/main/pg_hba.conf
host pgtesting monitor 0.0.0.0/0 reject
and then
psql -U monitor -p 5432 -d pgtesting -h 127.0.0.1
psql: FATAL: pg_hba.conf rejects connection for host "127.0.0.1", user "monitor", database "pgtesting", SSL on
FATAL: pg_hba.conf rejects connection for host "127.0.0.1", user "monitor", database "pgtesting", SSL off
psql -U monitor -p 5432 -d pgtesting -h localhost
psql: FATAL: pg_hba.conf rejects connection for host "127.0.0.1", user "monitor", database "pgtesting", SSL on
FATAL: pg_hba.conf rejects connection for host "127.0.0.1", user "monitor", database "pgtesting", SSL off
psql -U monitor -p 5432 -d pgtesting -h 173.16.6.3
psql: FATAL: pg_hba.conf rejects connection for host "173.16.6.3", user "monitor", database "pgtesting", SSL on
FATAL: pg_hba.conf rejects connection for host "173.16.6.3", user "monitor", database "pgtesting", SSL off
https://stackoverflow.com/questions/38942868/revoke-superuser-connect-a-specific-database
Thanks,
Vijay
From: "bejita0409@yahoo.co.jp" <bejita0409@yahoo.co.jp>
Reply-To: "bejita0409@yahoo.co.jp" <bejita0409@yahoo.co.jp>
Date: Monday, August 6, 2018 at 3:19 PM
To: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>, "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: [External] How to revoke privileged from PostgreSQL's superuser
Hello,
I am a newbie DBA.
I have a request for revoking the access to user's data from DBA-user.
I think the request is right because users should be the only ones can access their data.
But DBA-user also need full access to the other data? It means that DBA-user also needs to be a superuser.
So I conclude the request that how to revoke privileged from superuser in postgres.
As my knowledge, the superuser in PostgreSQL bypasses all the permission check.
So that, there is no way to do it in PostgreSQL, is that right?
Is there some DBAs are faced with this before?
Thanks,
--
bejita
^ permalink raw reply [nested|flat] 40+ messages in thread
* RE: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-06 10:33 Charles Clavadetscher <clavadetscher@swisspug.org>
parent: bejita0409@yahoo.co.jp
4 siblings, 0 replies; 40+ messages in thread
From: Charles Clavadetscher @ 2018-08-06 10:33 UTC (permalink / raw)
To: bejita0409@yahoo.co.jp; pgsql-admin@lists.postgresql.org; pgsql-general@lists.postgresql.org
Hello
From: bejita0409@yahoo.co.jp [mailto:bejita0409@yahoo.co.jp]
Sent: Montag, 6. August 2018 11:49
To: pgsql-admin@lists.postgresql.org; pgsql-general@lists.postgresql.org
Subject: How to revoke privileged from PostgreSQL's superuser
Hello,
I am a newbie DBA.
I have a request for revoking the access to user's data from DBA-user.
I think the request is right because users should be the only ones can access their data.
But DBA-user also need full access to the other data? It means that DBA-user also needs to be a superuser.
So I conclude the request that how to revoke privileged from superuser in postgres.
As my knowledge, the superuser in PostgreSQL bypasses all the permission check.
So that, there is no way to do it in PostgreSQL, is that right?
Yes, superuser have access to everything. The main question is why should a superuser be restricted in her access to an object?
I think that this is more a question of designing your user and groups in a clean way.
In theory you could restrict access using a before trigger, but this is not a good idea and is quite expensive.
You could also use a policy for the superuser with the clause using(false), but this also is not really a good idea and may become
very confusing.
In either way, this would not be a good solution.
Maybe, if you clarify better what is your purpose, there might be more useful answers and approaches.
Regards
Charles
Is there some DBAs are faced with this before?
Thanks,
--
bejita
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-06 13:19 David G. Johnston <david.g.johnston@gmail.com>
parent: bejita0409@yahoo.co.jp
4 siblings, 2 replies; 40+ messages in thread
From: David G. Johnston @ 2018-08-06 13:19 UTC (permalink / raw)
To: bejita0409@yahoo.co.jp <bejita0409@yahoo.co.jp>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Monday, August 6, 2018, <bejita0409@yahoo.co.jp> wrote:
>
> I have a request for revoking the access to user's data from DBA-user.
> I think the request is right because users should be the only ones can
> access their data.
>
User then needs to encrypt data prior to storing it. Superuser can still
access the data but would be challenged to make sense of it,
Usually DBAs are tasked with backups which requires read access to all
relevant data.
David J.
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-06 13:22 Bear Giles <bgiles@coyotesong.com>
parent: bejita0409@yahoo.co.jp
4 siblings, 1 reply; 40+ messages in thread
From: Bear Giles @ 2018-08-06 13:22 UTC (permalink / raw)
To: bejita0409@yahoo.co.jp; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
I think you may be conflating two things.
The "superuser" (or root) in an operating system is a special user (with
uid = 0 in unix/linux systems) that bypasses many checks. Ideally sysadmins
and regular users will never log in as root - they'll either use the
existing privilege framework (e.g., groups and set-uid programs) or briefly
use privilege escalation via a program like 'sudo'.
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.
Since they're regular users it's easy to revoke privileges. E.g., I think
the command you want is 'revoke all on database x from [ role | public]'
or 'revoke all on schema x from [ role | public ]'. I don't know if you can
revoke their admin privileges on a single database. If not then it's just a
small hurdle since they could grant themselves the necessary privilege, do
their work, and then revoke them so they'll never know unless a periodic
audit of privileges catches them in the act.*
However it's important that you make sure you don't block normal
operations. E.g., you'll probably want a user who has access to your tables
in order to perform backups. (This user could be limited to SELECT
privileges.) You'll also need a user who can perform a restoration from
backups - that's normally the DBA. It's tempting to limit these privileges
to the only times they're actually required, e.g., the backup script could
start by granting SELECT privileges to the backup user and finish by
revoking those privileges, but that's arguably worse since it requires a
second user with the admin privileges required for that.
Since you're a newbie are you aware of postgresql 'roles'? You should have
a DBA role but never allow anyone to log in as a DBA user. Instead every
DBA has their own user account who is a member of that role. That role has
the extra privileges, not individual users, so it's easy to control access
by adding and removing that role.
Finally I think you may be asking the wrong question. You need a DBA and
you need to trust the people the people who have DBA rights. If you don't
trust them then they shouldn't hold that job. All of the security standards
I'm familiar accept that some people will have high levels of access and
the focus is on vetting those people for trustworthiness.
(*) If you want to be really paranoid you could put a trigger on the pg_*
tables that contain privileges and triggers. That would prevent anyone from
giving themselves extra privileges, or disabling the trigger that would
alert you if they give themselves extra privileges, without at least some
warning. Heck, the trigger could even prevent them from making these
changes. But that's pretty advanced dba-fu.
On Mon, Aug 6, 2018 at 3:48 AM, <bejita0409@yahoo.co.jp> wrote:
> Hello,
>
> I am a newbie DBA.
>
> I have a request for revoking the access to user's data from DBA-user.
> I think the request is right because users should be the only ones can
> access their data.
> But DBA-user also need full access to the other data? It means that
> DBA-user also needs to be a superuser.
>
> So I conclude the request that how to revoke privileged from superuser in
> postgres.
>
> As my knowledge, the superuser in PostgreSQL bypasses all the permission
> check.
> So that, there is no way to do it in PostgreSQL, is that right?
>
> Is there some DBAs are faced with this before?
>
>
> Thanks,
> --
> bejita
>
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-06 13:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bear Giles <bgiles@coyotesong.com>
0 siblings, 2 replies; 40+ messages in thread
From: Tom Lane @ 2018-08-06 13:43 UTC (permalink / raw)
To: Bear Giles <bgiles@coyotesong.com>; +Cc: bejita0409@yahoo.co.jp, "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-06 13:44 Bear Giles <bgiles@coyotesong.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 40+ messages in thread
From: Bear Giles @ 2018-08-06 13:44 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: bejita0409@yahoo.co.jp <bejita0409@yahoo.co.jp>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Encrypting data within the database(*) severely limits its usability - you
can't use it in queries, etc. In some cases it's not a problem since you'll
never want to use it in a query anyway, or you can use a proxy(**). But as
a general rule I think if you're encrypting much of your data then a
traditional database isn't the right solution to your problem.
(*) the underlying filesystem and should still be encrypted. The backups
should also be encrypted - a lot of people forget to do that.
(**) for instance you might consider the person's email address to be
sensitive information that should be encrypted but you still want to be
able index the field so you can perform a rapid lookup. In that case you
can add a salted hash of the email and index that. Your app knows how to
perform the same hash so it can quickly find the record but it's totally
opaque to an intruder.
It's important to use a salted hash since an unsalted hash is no longer
secure since a knowledgeable intruder probably already has a list of emails
from other attacks and can easily compute the values to check. At the
minimum a salted hash is something like sha1(email + "my secret") (NOT
sha1("my secret" + email)) but you should really use one of the standard
algorithms to convert a passphrase and salt into an encryption key.
(PBE2K?) For performance reasons you might not want to perform all 1000+
iterations required for an encryption key but it's important to use a
standard algorithm since it's really easy to create hashes that aren't
nearly as strong as you think. E.g., there's a huge difference between
hash(value + salt) and hash(salt + value).
In this case the salt has to systemwide, or at least easily computed given
the email address but not derived from it (e.g., you can use the last few
digits of the hash of the email address as the index into a lookup table
but don't use the hash itself.) In most cases it's best to add a 'salt'
column to the record, perhaps in a shadow table that's not obvious to an
intruder, but you can't do that with anything used in a lookup since you
have no idea what value to use.
On Mon, Aug 6, 2018 at 7:19 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Monday, August 6, 2018, <bejita0409@yahoo.co.jp> wrote:
>
>>
>> I have a request for revoking the access to user's data from DBA-user.
>> I think the request is right because users should be the only ones can
>> access their data.
>>
>
> User then needs to encrypt data prior to storing it. Superuser can still
> access the data but would be challenged to make sense of it,
>
> Usually DBAs are tasked with backups which requires read access to all
> relevant data.
>
> David J.
>
>
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-06 13:45 Bear Giles <bgiles@coyotesong.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 40+ messages in thread
From: Bear Giles @ 2018-08-06 13:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: bejita0409@yahoo.co.jp, "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Thanks, I didn't realize that this was handled by a flag instead of just
the standard permissions.
On Mon, Aug 6, 2018 at 7:43 AM, 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
>
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-06 14:13 Evan Bauer <evanbauer@mac.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 40+ messages in thread
From: Evan Bauer @ 2018-08-06 14:13 UTC (permalink / raw)
To: bejita0409@yahoo.co.jp; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-06 14:45 Bear Giles <bgiles@coyotesong.com>
parent: Evan Bauer <evanbauer@mac.com>
1 sibling, 1 reply; 40+ messages in thread
From: Bear Giles @ 2018-08-06 14:45 UTC (permalink / raw)
To: Evan Bauer <evanbauer@mac.com>; +Cc: bejita0409@yahoo.co.jp, "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
I should clarify something I said above - I know that some data must be
encrypted within the database. My point was that during the design phase
you should take the schema, cross out all columns that will be encrypted,
and ask what's left. If it's nothing but the primary key and things you'll
never sort on (or require uniqueness) then it's reasonable to ask if
storing the information in a relational database is the best approach. An
alternative would be storing each encrypted record as an S3 file or Hadoop
HBase record. In both cases the filename/key would be based on something
like a hashed email address. In the case of S3 it's important to remember
that there is a limit on the number of entries although I don't remember
what it is. Some people have hit it when using it like this.
It's a little more work but it's arguably a lot more secure if you make
full use of AWS and Hadoop security. On the other hand keeping everything
in a single place is a lot more convenient and it only requires one
security audit, not two.
By 'shadow' table I mean that there's no reason why everyone needs access
to everything. A classic example is the user table. Nearly everyone keeps
the password (hopefully hashed!) in the user table. There's no reason to do
this - it's just as easy to keep the passwords in a separate table with the
same primary key. That will only affect the bits of your app that handle
authentication - verifying a password or changing it. If anyone manages to
do a SQL injection attack to list the contents of the user table they'll
learn the user accounts but probably won't get the user's passwords, esp.
if you give that table some innoculous name instead of 'password'.
But since such a limited amount of code that requires access to that table
you can make a design decision that you'll use a separate database user
when authenticating a user. That user will have access to the password
table but the regular user won't have any access to it. If you want to be
really secure you could put the password table into a different schema. The
table is then entirely hidden from someone who has full access to your main
schema. Hence "shadow" table. (Plus the fact that user passwords are kept
in the /etc/shadow file on unix/linux systems.)
This is a pretty powerful technique. E.g., some people add a column that
keeps a salted hash of the data in a row. An intruder won't know to change
the hash value so you can dramatically improve security by checking that
hash value when retrieving a value from the database. If it doesn't match
you throw an alert and refuse to use the value. However this leaves a trace
on the table to the intruder knows that their change will be detected.
That's often a Good Thing - it's a deterrent - but you might prefer to let
intruders make changes so you can determine they're in your system. You
could use a shadow table to hold the hashes and view so that your app
always sees the hash but an intruder doesn't.
You could even go a step further and have a trigger that updates a shadow
table with the 'before' and 'after' values on an insert/update/delete. (Or
pgaudit if you're on postgresql specifically - the general approach works
with any database that supports triggers.) Nobody intruder knows about
this, no application developer knows about this, only the DBA and security
team. That lets you track down every change - what changed, when, who did
it (including IP address if you capture connection info), etc. At most an
intruder might see there's a trigger but if it works via a stored procedure
with the appropriate permissions they won't know what you're doing with it.
The one thing to be careful about is that you can't use a trigger to update
the hash value mentioned above. That would also be fired by an intruder so
it won't tell you anything. It has to be done programmatically, but (at
least with java) it could be implemented by a security team that uses AOP
so the developers know there's a few extra columns but they don't do
anything with them... or if you're paranoid the security team has its own
persistence mechanism so the developers are completely unaware that this is
happening since the hash is written to a different schema & table.
Hmm, I should probably write a blog entry for this...
Bear
On Mon, Aug 6, 2018 at 8:13 AM, Evan Bauer <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
> >
>
>
>
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-06 22:27 Tim Cross <theophilusx@gmail.com>
parent: bejita0409@yahoo.co.jp
4 siblings, 0 replies; 40+ messages in thread
From: Tim Cross @ 2018-08-06 22:27 UTC (permalink / raw)
To: bejita0409@yahoo.co.jp; +Cc: pgsql-admin\@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-general\@lists.postgresql.org <pgsql-general@lists.postgresql.org>
bejita0409@yahoo.co.jp writes:
> Hello,
>
> I am a newbie DBA.
>
> I have a request for revoking the access to user's data from DBA-user.
> I think the request is right because users should be the only ones can access their data.
> But DBA-user also need full access to the other data? It means that DBA-user also needs to be a superuser.
>
> So I conclude the request that how to revoke privileged from superuser in postgres.
>
> As my knowledge, the superuser in PostgreSQL bypasses all the permission check.
> So that, there is no way to do it in PostgreSQL, is that right?
>
> Is there some DBAs are faced with this before?
>
There are certainly DBAs who have had very similar requests. Often, they
are generated by non-technical people who don't really understand how
the technology works and have concerns over who has access to the data
(a common one is people who are concerned about who has access to their
email - we had a similar challenge from our Chief legal officer who was
paranoid sys admins were reading his highly sensitive email, this is
despite the fact 1 week previously, I was on a flight sitting in the
seat behind him while he read his email on his iPad, which I could (but
didn't) easily read over his shoulder!).
The key to handling this sort of request is to dig deeper to understand
what the real risk is that they want addressed and work out how you can
do that within the constraints of the technology and what makes sense
within your context. 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, but if they are not appropriate for your business
context, will likely cripple the very process you are trying to
protect. All controls/restrictions cause some level of inconvenience -
the challenge is in getting the balance right so that the identified
risk is mitigated with the least level of inconvenience to normal
business operations.
The reality is that at various times, humans will need the ability to
access the data in ways which will limit, if not completely prevent,
your ability to restrict access. This is particularly relevant for
system and database administrators. It is pretty much 100% impossible to
guarantee that a sys admin or DBA cannot access data. However, what you
can do is approach the problem slightly differently and look at ways to
make this access harder and more importantly, make sure that all access
is logged appropriately and can be audited, ensuring the
logging/auditing system is also protected from deletion or modification.
Other posts in the thread include some good pointers on what you can do
to help with this. The principals are pretty straight forward. Possibly
the most important thing to do is ensure there is no 'anonymous' access
e.g. you cannot login to the database as 'postgres' or some other
generic account which multiple people have access to. Instead, ensure
that everyone with any level of administrator privilege has to login
using an account which is specific to them and not shared. The second
thing to do is ensure the logging level is appropriate and that all
logging is also stored/recorded on a system which the administrator does
not have access to and ensure the level of privileges every
individual has is at the minimum they require to get the job done. It is
also important that logs and audit trails are regularly reviewed to
ensure nobody is abusing the system and all controls are still
appropriate (things change, new systems come on line, old ones a
retired, business processes change etc).
If necessary, consider controls which restrict access to accounts with
extended privileges to certain hosts e.g. DBA 'Phil' can only log into
the database from server xxx.xxx.xxx.xxx and he can only log into that
server between 9am and 5pm Mon - Fri etc. Maybe he has to use a hardware
token etc.
In most cases, provided you can give strong guarantee that unauthorised
data access can be identified, you will satisfy the security
requirements and this is often far more feasible than outright blocking
of access.
Finally, it is also important that all staff are aware of the
organisations policies, procedures and controls regarding data
access. They need to know what is expected of them and what is
unacceptable.
--
Tim Cross
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-06 22:46 bejita0409@yahoo.co.jp
parent: Evan Bauer <evanbauer@mac.com>
1 sibling, 1 reply; 40+ messages in thread
From: bejita0409@yahoo.co.jp @ 2018-08-06 22:46 UTC (permalink / raw)
To: Evan Bauer <evanbauer@mac.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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 $B!=(B 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 $B!F(Bpostgres$B!G(B 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
> >
>
>
>
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-07 00:44 Bear Giles <bgiles@coyotesong.com>
parent: bejita0409@yahoo.co.jp
0 siblings, 0 replies; 40+ messages in thread
From: Bear Giles @ 2018-08-07 00:44 UTC (permalink / raw)
To: bejita0409@yahoo.co.jp; +Cc: Evan Bauer <evanbauer@mac.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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@yahoo.co.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@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
> > >
> >
> >
> >
>
>
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-10 19:41 Bruce Momjian <bruce@momjian.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 40+ messages in thread
From: Bruce Momjian @ 2018-08-10 19:41 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: bejita0409@yahoo.co.jp <bejita0409@yahoo.co.jp>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Mon, Aug 6, 2018 at 06:19:55AM -0700, David G. Johnston wrote:
> On Monday, August 6, 2018, <bejita0409@yahoo.co.jp> wrote:
>
>
> I have a request for revoking the access to user's data from DBA-user.
> I think the request is right because users should be the only ones can
> access their data.
>
>
> User then needs to encrypt data prior to storing it. Superuser can still
> access the data but would be challenged to make sense of it,
Keep in mind DBAs can often remove data with little detection, unless
you are using some kind of block chain, which itself can force
serialized data access, slowing things down.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-10 20:06 Benedict Holland <benedict.m.holland@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 40+ messages in thread
From: Benedict Holland @ 2018-08-10 20:06 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; bejita0409@yahoo.co.jp <bejita0409@yahoo.co.jp>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
The short answer I will provide from my experience is that you can't do it.
Your DBA will have access to just about anything across all tables and
databases.
The longer answer are ones that others have pointed out. If a DBA should be
restricted from tables, they probably shouldn't be your DBA. Your DBA will
likely be the one responsible, for example, for backing up all of the
databases on a server. That requires read access and understanding concepts
about secure backups of sensitive data. It is also possible that they are
running backups as their own user rather than postgres. If you don't want
DBAs to access your data you really do not want that data to not have
backups.
I also would take Bruce's comment with a massive grain of salt. Everything
that everyone does on a database is logged somewhere assuming proper
logging. Now do you have the person-power to go through gigs of plain text
logs to find out if someone is doing something shady... that is a question
for your management team. Also, if you suspect someone of doing something
shady, you should probably revoke their admin rights.
~Ben
On Fri, Aug 10, 2018 at 3:41 PM, Bruce Momjian <bruce@momjian.us> wrote:
> On Mon, Aug 6, 2018 at 06:19:55AM -0700, David G. Johnston wrote:
> > On Monday, August 6, 2018, <bejita0409@yahoo.co.jp> wrote:
> >
> >
> > I have a request for revoking the access to user's data from
> DBA-user.
> > I think the request is right because users should be the only ones
> can
> > access their data.
> >
> >
> > User then needs to encrypt data prior to storing it. Superuser can still
> > access the data but would be challenged to make sense of it,
>
> Keep in mind DBAs can often remove data with little detection, unless
> you are using some kind of block chain, which itself can force
> serialized data access, slowing things down.
>
> --
> Bruce Momjian <bruce@momjian.us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + As you are, so once was I. As I am, so you will be. +
> + Ancient Roman grave inscription +
>
>
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-10 20:12 Rui DeSousa <rui@crazybean.net>
parent: Bear Giles <bgiles@coyotesong.com>
0 siblings, 1 reply; 40+ messages in thread
From: Rui DeSousa @ 2018-08-10 20:12 UTC (permalink / raw)
To: Bear Giles <bgiles@coyotesong.com>; +Cc: Evan Bauer <evanbauer@mac.com>; bejita0409@yahoo.co.jp, "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
> On Aug 6, 2018, at 10:45 AM, Bear Giles <bgiles@coyotesong.com> wrote:
>
> then it's reasonable to ask if storing the information in a relational database is the best approach.
Why? Just because its encrypted doesn’t mean it shouldn’t be stored in the database. What about PITR, how would that be handled? You basically would have to reimplement things the RDBMS system gives you for free by storing it outside the database. Don’t forget it's called a management system for a reason.=
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-10 23:21 Benedict Holland <benedict.m.holland@gmail.com>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 1 reply; 40+ messages in thread
From: Benedict Holland @ 2018-08-10 23:21 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: Bear Giles <bgiles@coyotesong.com>; Evan Bauer <evanbauer@mac.com>; bejita0409@yahoo.co.jp, "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Oh this is an easy one. There are simpler solutions for PITR. Also, a PITR
is a very specific case of a database use, if it even uses one. Generally
speaking, you would not want to keep encrypted data within a database.
There simply isn't a need for it. Just use a file or a folder. You can't do
anything that you would normally do with a database if you can't read or
access any of your objects. It would just be a table of binary objects
without names, possibly access or creation dates depending on the level of
paranoia. Literally, you would have an int column and a binary object
column. What can you honestly do with that? You can't even link it to other
objects. It has no relational structure, hense the question. If there isn't
a relationship to anything then a relational database wouldn't really help
anything.
Also, I would probably keep the encryption key within the database anyway.
Otherwise, your objects could get permanently lost making the whole thing
moot in the first place.
Look, you either trust your DBAs or you don't. If you don't trust them, why
are they your DBA? This is like writing unit tests for unit tests or having
even higher levels of privilege than a superuser. It's turtles all the way
down.
~Ben
On Fri, Aug 10, 2018 at 4:12 PM, Rui DeSousa <rui@crazybean.net> wrote:
>
>
> On Aug 6, 2018, at 10:45 AM, Bear Giles <bgiles@coyotesong.com> wrote:
>
> then it's reasonable to ask if storing the information in a relational
> database is the best approach.
>
>
> Why? Just because its encrypted doesn’t mean it shouldn’t be stored in the
> database. What about PITR, how would that be handled? You basically would
> have to reimplement things the RDBMS system gives you for free by storing
> it outside the database. Don’t forget it's called a management system for
> a reason.
>
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-11 02:34 Rui DeSousa <rui@crazybean.net>
parent: Benedict Holland <benedict.m.holland@gmail.com>
0 siblings, 1 reply; 40+ messages in thread
From: Rui DeSousa @ 2018-08-11 02:34 UTC (permalink / raw)
To: Benedict Holland <benedict.m.holland@gmail.com>; +Cc: Bear Giles <bgiles@coyotesong.com>; Evan Bauer <evanbauer@mac.com>; bejita0409@yahoo.co.jp, "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
> On Aug 10, 2018, at 7:21 PM, Benedict Holland <benedict.m.holland@gmail.com> wrote:
>
> Oh this is an easy one. There are simpler solutions for PITR. Also, a PITR is a very specific case of a database use, if it even uses one. Generally speaking, you would not want to keep encrypted data within a database. There simply isn't a need for it. Just use a file or a folder. You can't do anything that you would normally do with a database if you can't read or access any of your objects. It would just be a table of binary objects without names, possibly access or creation dates depending on the level of paranoia. Literally, you would have an int column and a binary object column. What can you honestly do with that? You can't even link it to other objects. It has no relational structure, hense the question. If there isn't a relationship to anything then a relational database wouldn't really help anything.
>
> Also, I would probably keep the encryption key within the database anyway. Otherwise, your objects could get permanently lost making the whole thing moot in the first place.
>
> Look, you either trust your DBAs or you don't. If you don't trust them, why are they your DBA? This is like writing unit tests for unit tests or having even higher levels of privilege than a superuser. It's turtles all the way down.
>
> ~Ben
>
>
> On Fri, Aug 10, 2018 at 4:12 PM, Rui DeSousa <rui@crazybean.net <mailto:rui@crazybean.net>> wrote:
>
>
>> On Aug 6, 2018, at 10:45 AM, Bear Giles <bgiles@coyotesong.com <mailto:bgiles@coyotesong.com>> wrote:
>>
>> then it's reasonable to ask if storing the information in a relational database is the best approach.
>
> Why? Just because its encrypted doesn’t mean it shouldn’t be stored in the database. What about PITR, how would that be handled? You basically would have to reimplement things the RDBMS system gives you for free by storing it outside the database. Don’t forget it's called a management system for a reason.
>
With that logic then you should use flat files for encrypted data and unencrypted data. It’s what was done many moons ago; and its unstructured haphazard approach gave rise to RDBMS systems.
You cannot say that encrypted data does not belong in a RDBMS system… that is just false. Hell, I’ve stored blobs in a RDMBS system which could have easily been stored in a different system if need be. It’s a design choice and what fits the application and budget needs.
Encrypting sensitive information and storing in the database is a valid use case. It may be only a few columns that are encrypted or a complete document (blob); there is no need to increase complexity just to move those columns out of the database.=
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-14 19:51 Bruce Momjian <bruce@momjian.us>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 0 replies; 40+ messages in thread
From: Bruce Momjian @ 2018-08-14 19:51 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: Benedict Holland <benedict.m.holland@gmail.com>; Bear Giles <bgiles@coyotesong.com>; Evan Bauer <evanbauer@mac.com>; bejita0409@yahoo.co.jp, "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Fri, Aug 10, 2018 at 10:34:26PM -0400, Rui DeSousa wrote:
> With that logic then you should use flat files for encrypted data and
> unencrypted data. It’s what was done many moons ago; and its unstructured
> haphazard approach gave rise to RDBMS systems.
>
> You cannot say that encrypted data does not belong in a RDBMS system… that is
> just false. Hell, I’ve stored blobs in a RDMBS system which could have easily
> been stored in a different system if need be. It’s a design choice and what
> fits the application and budget needs.
>
> Encrypting sensitive information and storing in the database is a valid use
> case. It may be only a few columns that are encrypted or a complete document
> (blob); there is no need to increase complexity just to move those columns out
> of the database.
I think the point is that it makes sense to store data encrypted in a
database only if it is a payload on another piece of non-encrypted data.
You can't easily index, restrict, or join encrypted data, so it doesn't
have a huge value alone in a database.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-14 19:59 Bruce Momjian <bruce@momjian.us>
parent: Benedict Holland <benedict.m.holland@gmail.com>
0 siblings, 2 replies; 40+ messages in thread
From: Bruce Momjian @ 2018-08-14 19:59 UTC (permalink / raw)
To: Benedict Holland <benedict.m.holland@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; bejita0409@yahoo.co.jp <bejita0409@yahoo.co.jp>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Fri, Aug 10, 2018 at 04:06:40PM -0400, Benedict Holland wrote:
> I also would take Bruce's comment with a massive grain of salt. Everything that
> everyone does on a database is logged somewhere assuming proper logging. Now do
> you have the person-power to go through gigs of plain text logs to find out if
> someone is doing something shady... that is a question for your management
> team. Also, if you suspect someone of doing something shady, you should
> probably revoke their admin rights.
Agreed, the best way to limit the risk of undetected DBA removal of data
is secure auditing --- I should have mentioned that.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 01:00 dangal <danielito.gallo@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 40+ messages in thread
From: dangal @ 2018-08-15 01:00 UTC (permalink / raw)
To: pgsql-admin
How audit dba?
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 02:45 Evan Bauer <evanbauer@mac.com>
parent: dangal <danielito.gallo@gmail.com>
0 siblings, 1 reply; 40+ messages in thread
From: Evan Bauer @ 2018-08-15 02:45 UTC (permalink / raw)
To: dangal <danielito.gallo@gmail.com>; +Cc: pgsql-admin
The pgaudit extension provides the mechanism used for this by most organizations I know of with sensitive data in PostgreSQL databases. The documentation at www.pgaudit.org is a good place to start.
- Evan
Sent from my iPhone
> On Aug 14, 2018, at 21:00, dangal <danielito.gallo@gmail.com> wrote:
>
> How audit dba?
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 03:44 dangal <danielito.gallo@gmail.com>
parent: Evan Bauer <evanbauer@mac.com>
0 siblings, 1 reply; 40+ messages in thread
From: dangal @ 2018-08-15 03:44 UTC (permalink / raw)
To: pgsql-admin
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 03:50 Evan Rempel <erempel@uvic.ca>
parent: dangal <danielito.gallo@gmail.com>
0 siblings, 1 reply; 40+ messages in thread
From: Evan Rempel @ 2018-08-15 03:50 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
In my opinion that is exactly why you log to syslog. The syslog
infrastructure can also
forward in real time the log events to a remote log collector that the
DBAs don't even
have access to. This method provides for a secure and prestine log
stream for archiving
and audit review processes.
Evan.
On 08/14/2018 08:44 PM, dangal wrote:
> From what I saw pgaudit records the postgres log, any dba can modify that log
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 04:21 Tim Cross <theophilusx@gmail.com>
parent: Evan Rempel <erempel@uvic.ca>
0 siblings, 0 replies; 40+ messages in thread
From: Tim Cross @ 2018-08-15 04:21 UTC (permalink / raw)
To: erempel@uvic.ca; +Cc: pgsql-admin@lists.postgresql.org
On Wed, 15 Aug 2018 at 13:50, Evan Rempel <erempel@uvic.ca> wrote:
> In my opinion that is exactly why you log to syslog. The syslog
> infrastructure can also
> forward in real time the log events to a remote log collector that the
> DBAs don't even
> have access to. This method provides for a secure and prestine log
> stream for archiving
> and audit review processes.
>
> Evan.
>
> On 08/14/2018 08:44 PM, dangal wrote:
> > From what I saw pgaudit records the postgres log, any dba can modify
> that log
> >
> >
> >
> > --
> > Sent from:
> http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
> >
>
> +1 wrt syslog and remote logging. In any environment where security and
> access monitoring is important should always have logs copied to a remote,
> secure server with access limited to individuals who are not also
> responsible for administering key systems, such as the database server.
>
When compromising a system, it is normal to attempt to cover up your
activity by modifying or deleting log files. Having these copied to a
separate system means the threat actor has to now compromise multiple
servers.
Another useful setup is the 'ELK' stack, which uses logstash and eleastic
search to provide a powerful log storage and querying infrastructure (which
can also unify logs from different sources). This can make auditing and
monitoring much more powerful.
Tim
--
regards,
Tim
--
Tim Cross
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 14:59 Bruce Momjian <bruce@momjian.us>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 2 replies; 40+ messages in thread
From: Bruce Momjian @ 2018-08-15 14:59 UTC (permalink / raw)
To: Benedict Holland <benedict.m.holland@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; bejita0409@yahoo.co.jp <bejita0409@yahoo.co.jp>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Tue, Aug 14, 2018 at 03:59:19PM -0400, Bruce Momjian wrote:
> On Fri, Aug 10, 2018 at 04:06:40PM -0400, Benedict Holland wrote:
> > I also would take Bruce's comment with a massive grain of salt. Everything that
> > everyone does on a database is logged somewhere assuming proper logging. Now do
> > you have the person-power to go through gigs of plain text logs to find out if
> > someone is doing something shady... that is a question for your management
> > team. Also, if you suspect someone of doing something shady, you should
> > probably revoke their admin rights.
>
> Agreed, the best way to limit the risk of undetected DBA removal of data
> is secure auditing --- I should have mentioned that.
So, how do you securely audit? You ship the logs to a server that isn't
controlled by the DBA, via syslog? How do you prevent the DBA from
turning off logging when the want to so something undetected? Do you
log the turning off of logging?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 16:05 Evan Rempel <erempel@uvic.ca>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 40+ messages in thread
From: Evan Rempel @ 2018-08-15 16:05 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 08/15/2018 07:59 AM, Bruce Momjian wrote:
> On Tue, Aug 14, 2018 at 03:59:19PM -0400, Bruce Momjian wrote:
>> On Fri, Aug 10, 2018 at 04:06:40PM -0400, Benedict Holland wrote:
>>> I also would take Bruce's comment with a massive grain of salt. Everything that
>>> everyone does on a database is logged somewhere assuming proper logging. Now do
>>> you have the person-power to go through gigs of plain text logs to find out if
>>> someone is doing something shady... that is a question for your management
>>> team. Also, if you suspect someone of doing something shady, you should
>>> probably revoke their admin rights.�
>> Agreed, the best way to limit the risk of undetected DBA removal of data
>> is secure auditing --- I should have mentioned that.
> So, how do you securely audit? You ship the logs to a server that isn't
> controlled by the DBA, via syslog? How do you prevent the DBA from
> turning off logging when the want to so something undetected? Do you
> log the turning off of logging?
Some of that depends on how logging enable/disable is managed.
If the logging can be controlled by DB statements, or session controls, then
those statements should be logged prior to the logging being disabled. This just
makes auditing a black hole. You know who did it, but you don't know what they did.
If the logging is controlled by a configuration file, then that file should not
be managed by the DBA.
In the later case, you should have command shell logging as to who turned the
logging off. There are versions of bash that log every command executed and sudo commands
already log the commands.
At the end of the day someone has full access and control and can do anything without auditing database statements.
For instance, as the root user on the server, I can do:
- shutdown the server database
- copy the entire DB filespace to my workstation
- change the workstation config for no logging/auditing
- start the workstation Database
- make all the changes I want at the workstation.
- stop the workstation database
- copy all of the files back to the server
- start the server Database.
no logging of any kind and all of the data would be suspect.
Someone or some group of someones must be trusted.
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 19:28 Bruce Momjian <bruce@momjian.us>
parent: Evan Rempel <erempel@uvic.ca>
0 siblings, 1 reply; 40+ messages in thread
From: Bruce Momjian @ 2018-08-15 19:28 UTC (permalink / raw)
To: Evan Rempel <erempel@uvic.ca>; +Cc: pgsql-admin@lists.postgresql.org
On Wed, Aug 15, 2018 at 09:05:51AM -0700, Evan Rempel wrote:
> At the end of the day someone has full access and control and can do anything without auditing database statements.
>
> For instance, as the root user on the server, I can do:
>
> - shutdown the server database
> - copy the entire DB filespace to my workstation
> - change the workstation config for no logging/auditing
> - start the workstation Database
> - make all the changes I want at the workstation.
> - stop the workstation database
> - copy all of the files back to the server
> - start the server Database.
>
> no logging of any kind and all of the data would be suspect.
Well, that is an intersting attack, and I don't think it requires root
--- all it requires is access to the Postgres data directory. Frankly,
I don't know if there is a way to prevent the Postgres superuser from
silently disabling logging because the _data_ is fully under the control
of the Postgres superuser.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 20:13 Evan Rempel <erempel@uvic.ca>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 2 replies; 40+ messages in thread
From: Evan Rempel @ 2018-08-15 20:13 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-admin@lists.postgresql.org
On 08/15/2018 12:28 PM, Bruce Momjian wrote:
> On Wed, Aug 15, 2018 at 09:05:51AM -0700, Evan Rempel wrote:
>> At the end of the day someone has full access and control and can do anything without auditing database statements.
>>
>> For instance, as the root user on the server, I can do:
>>
>> - shutdown the server database
>> - copy the entire DB filespace to my workstation
>> - change the workstation config for no logging/auditing
>> - start the workstation Database
>> - make all the changes I want at the workstation.
>> - stop the workstation database
>> - copy all of the files back to the server
>> - start the server Database.
>>
>> no logging of any kind and all of the data would be suspect.
> Well, that is an intersting attack, and I don't think it requires root
> --- all it requires is access to the Postgres data directory. Frankly,
> I don't know if there is a way to prevent the Postgres superuser from
> silently disabling logging because the _data_ is fully under the control
> of the Postgres superuser.
Which highlights a small design flaw.
For security purposes many applications have moved the configuration files out of the data folder.
PHP and MySQL are two that come to mind.
The postgresql.conf and the pg_hba.conf should NOT be stored in the database data directory. These files
should NOT be owned or editable by the postgres user (OS user that postmaster is running as).
The result is that security options and configuration options (such as logging) could NOT be changed by
the postgres OS account, and thus NOT be cvhanged by the postgres superuser database account.
Ideally the audit logging and statement logging should NOT be permitted to be controlled via session
commands (i.e. the client)
Just my $0.02
--
Evan
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 20:26 Bruce Momjian <bruce@momjian.us>
parent: Evan Rempel <erempel@uvic.ca>
1 sibling, 1 reply; 40+ messages in thread
From: Bruce Momjian @ 2018-08-15 20:26 UTC (permalink / raw)
To: Evan Rempel <erempel@uvic.ca>; +Cc: pgsql-admin@lists.postgresql.org
On Wed, Aug 15, 2018 at 01:13:10PM -0700, Evan Rempel wrote:
> On 08/15/2018 12:28 PM, Bruce Momjian wrote:
> >On Wed, Aug 15, 2018 at 09:05:51AM -0700, Evan Rempel wrote:
> >>At the end of the day someone has full access and control and can do anything without auditing database statements.
> >>
> >>For instance, as the root user on the server, I can do:
> >>
> >>- shutdown the server database
> >>- copy the entire DB filespace to my workstation
> >>- change the workstation config for no logging/auditing
> >>- start the workstation Database
> >>- make all the changes I want at the workstation.
> >>- stop the workstation database
> >>- copy all of the files back to the server
> >>- start the server Database.
> >>
> >>no logging of any kind and all of the data would be suspect.
> >Well, that is an intersting attack, and I don't think it requires root
> >--- all it requires is access to the Postgres data directory. Frankly,
> >I don't know if there is a way to prevent the Postgres superuser from
> >silently disabling logging because the _data_ is fully under the control
> >of the Postgres superuser.
> Which highlights a small design flaw.
>
> For security purposes many applications have moved the configuration files out of the data folder.
> PHP and MySQL are two that come to mind.
>
> The postgresql.conf and the pg_hba.conf should NOT be stored in the database data directory. These files
> should NOT be owned or editable by the postgres user (OS user that postmaster is running as).
> The result is that security options and configuration options (such as logging) could NOT be changed by
> the postgres OS account, and thus NOT be cvhanged by the postgres superuser database account.
Well, the configuration files can be configured to not be in the data
directory, but the superuser can override those locations on startup, so
they can effectively be placed anywhere. I don't think it would help to
hard-code a root-owned directory, e.g. /etc, in the server binary ---
they could just use a custom binary.
This also doesn't help the case where the superuser copies the data
directory to a server where he is root on, modifies the database, then
copies it back.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 20:28 David G. Johnston <david.g.johnston@gmail.com>
parent: Evan Rempel <erempel@uvic.ca>
1 sibling, 0 replies; 40+ messages in thread
From: David G. Johnston @ 2018-08-15 20:28 UTC (permalink / raw)
To: Evan Rempel <erempel@uvic.ca>; +Cc: Bruce Momjian <bruce@momjian.us>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Wednesday, August 15, 2018, Evan Rempel <erempel@uvic.ca> wrote:
>
> For security purposes many applications have moved the configuration files
> out of the data folder.
> PHP and MySQL are two that come to mind.
>
> The postgresql.conf and the pg_hba.conf should NOT be stored in the
> database data directory. These files
> should NOT be owned or editable by the postgres user (OS user that
> postmaster is running as).
>
I believe this is a matter of default configuration and that what you
described is possible (if maybe not trivial depend on installation method
and auto-start setup).
David J.
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 20:52 Evan Rempel <erempel@uvic.ca>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 40+ messages in thread
From: Evan Rempel @ 2018-08-15 20:52 UTC (permalink / raw)
To: ; +Cc: pgsql-admin@lists.postgresql.org
On 08/15/2018 01:26 PM, Bruce Momjian wrote:
> On Wed, Aug 15, 2018 at 01:13:10PM -0700, Evan Rempel wrote:
>> On 08/15/2018 12:28 PM, Bruce Momjian wrote:
>>> On Wed, Aug 15, 2018 at 09:05:51AM -0700, Evan Rempel wrote:
>>>> At the end of the day someone has full access and control and can do anything without auditing database statements.
>>>>
>>>> For instance, as the root user on the server, I can do:
>>>>
>>>> - shutdown the server database
>>>> - copy the entire DB filespace to my workstation
>>>> - change the workstation config for no logging/auditing
>>>> - start the workstation Database
>>>> - make all the changes I want at the workstation.
>>>> - stop the workstation database
>>>> - copy all of the files back to the server
>>>> - start the server Database.
>>>>
>>>> no logging of any kind and all of the data would be suspect.
>>> Well, that is an intersting attack, and I don't think it requires root
>>> --- all it requires is access to the Postgres data directory. Frankly,
>>> I don't know if there is a way to prevent the Postgres superuser from
>>> silently disabling logging because the _data_ is fully under the control
>>> of the Postgres superuser.
>> Which highlights a small design flaw.
>>
>> For security purposes many applications have moved the configuration files out of the data folder.
>> PHP and MySQL are two that come to mind.
>>
>> The postgresql.conf and the pg_hba.conf should NOT be stored in the database data directory. These files
>> should NOT be owned or editable by the postgres user (OS user that postmaster is running as).
>> The result is that security options and configuration options (such as logging) could NOT be changed by
>> the postgres OS account, and thus NOT be cvhanged by the postgres superuser database account.
> Well, the configuration files can be configured to not be in the data
> directory, but the superuser can override those locations on startup, so
> they can effectively be placed anywhere. I don't think it would help to
> hard-code a root-owned directory, e.g. /etc, in the server binary ---
> they could just use a custom binary.
>
> This also doesn't help the case where the superuser copies the data
> directory to a server where he is root on, modifies the database, then
> copies it back.
Ya. Everyone is a pessimist :-) And rightfully so when it comes to security.
Just for discussion I provide the following thoughts, but the closing 2 paragraphs make them moot.
I guess you could have a startup script that defines all of the configuration file locations.
Then you could take make the postmaster and postgres obinaries nly executable by root.
Then you could give postgres the ability to run the startup script.
Then you take away execute permissions on all filesystems other than those that are writeable
by postgresl (postgres home, tmp, postgres data folder, postgres backup folder, WAL folder etc).
The configuration files would only be editable by root. The only executable postgres binary
is the one installed in /usr and is only executable by root.
The only way for postgres to start the postmaster is via the startup script, which enforces the
configuration files.
This still does not stop the attack of copy/replace, but it does ensure that all postgres database activities
committed on the system are logged.
It is a lot of details to go through without closing the gaping hole of the copy/replace attack, so does not
really gain you any security if you have to trust the DBAs 100% anyway.
The only way to accomplish this is to NOT give the DBA access to the OS account of postgres.
The DBAs would only have PostgreSQL API access.
In reality this would not be acceptable in most shops. Many DBA functions could not be performed
- monitoring scripts
- many backup scenarios
- recovery
- failover
There are just a ton of configuration elements that the DBAs need to decide on and implement that require
configuration of components that are outside of the database proper.
It was a worthwhile discussion. One needs to trust the data stewards.
--
Evan
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 20:57 Bruce Momjian <bruce@momjian.us>
parent: Evan Rempel <erempel@uvic.ca>
0 siblings, 2 replies; 40+ messages in thread
From: Bruce Momjian @ 2018-08-15 20:57 UTC (permalink / raw)
To: Evan Rempel <erempel@uvic.ca>; +Cc: pgsql-admin@lists.postgresql.org
On Wed, Aug 15, 2018 at 01:52:43PM -0700, Evan Rempel wrote:
> There are just a ton of configuration elements that the DBAs need to decide on and implement that require
> configuration of components that are outside of the database proper.
>
> It was a worthwhile discussion. One needs to trust the data stewards.
Agreed. I just wish it had a more positive outcome. ;-)
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 21:03 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 40+ messages in thread
From: Scott Ribe @ 2018-08-15 21:03 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Evan Rempel <erempel@uvic.ca>; pgsql-admin@lists.postgresql.org
> On Aug 15, 2018, at 2:57 PM, Bruce Momjian <bruce@momjian.us> wrote:
>
> On Wed, Aug 15, 2018 at 01:52:43PM -0700, Evan Rempel wrote:
>> There are just a ton of configuration elements that the DBAs need to decide on and implement that require
>> configuration of components that are outside of the database proper.
>>
>> It was a worthwhile discussion. One needs to trust the data stewards.
>
> Agreed. I just wish it had a more positive outcome. ;-)
Well, it probably elucidated the issues enough that an expert in SELinux could configure a server such that DBAs could not disable logging. Of course, you still have to trust somebody with that configuration, but it is possible to separate responsibilities if you work hard enough at it.
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 21:09 Bruce Momjian <bruce@momjian.us>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 40+ messages in thread
From: Bruce Momjian @ 2018-08-15 21:09 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Evan Rempel <erempel@uvic.ca>; pgsql-admin@lists.postgresql.org
On Wed, Aug 15, 2018 at 03:03:41PM -0600, Scott Ribe wrote:
> > On Aug 15, 2018, at 2:57 PM, Bruce Momjian <bruce@momjian.us> wrote:
> >
> > On Wed, Aug 15, 2018 at 01:52:43PM -0700, Evan Rempel wrote:
> >> There are just a ton of configuration elements that the DBAs need
> >> to decide on and implement that require configuration of components
> >> that are outside of the database proper.
> >>
> >> It was a worthwhile discussion. One needs to trust the data
> >> stewards.
> >
> > Agreed. I just wish it had a more positive outcome. ;-)
>
> Well, it probably elucidated the issues enough that an expert in
> SELinux could configure a server such that DBAs could not disable
> logging. Of course, you still have to trust somebody with that
> configuration, but it is possible to separate responsibilities if you
> work hard enough at it.
Well, since the superuser can start the server with whatever arguments
they want, I am not sure how SELinux would help here.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 21:16 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 40+ messages in thread
From: Scott Ribe @ 2018-08-15 21:16 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Evan Rempel <erempel@uvic.ca>; pgsql-admin@lists.postgresql.org
> On Aug 15, 2018, at 3:09 PM, Bruce Momjian <bruce@momjian.us> wrote:
>
> Well, since the superuser can start the server with whatever arguments
> they want, I am not sure how SELinux would help here.
root, yes, postgres "superuser", no--assuming things being locked down enough
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 21:41 raf <raf@raf.org>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 40+ messages in thread
From: raf @ 2018-08-15 21:41 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Bruce Momjian wrote:
> On Tue, Aug 14, 2018 at 03:59:19PM -0400, Bruce Momjian wrote:
> > On Fri, Aug 10, 2018 at 04:06:40PM -0400, Benedict Holland wrote:
> > > I also would take Bruce's comment with a massive grain of salt. Everything that
> > > everyone does on a database is logged somewhere assuming proper logging. Now do
> > > you have the person-power to go through gigs of plain text logs to find out if
> > > someone is doing something shady... that is a question for your management
> > > team. Also, if you suspect someone of doing something shady, you should
> > > probably revoke their admin rights.
> >
> > Agreed, the best way to limit the risk of undetected DBA removal of data
> > is secure auditing --- I should have mentioned that.
>
> So, how do you securely audit? You ship the logs to a server that isn't
> controlled by the DBA, via syslog? How do you prevent the DBA from
> turning off logging when the want to so something undetected? Do you
> log the turning off of logging?
>
> --
> Bruce Momjian <bruce@momjian.us> http://momjian.us
Yes. You can set up terminal session logging with redhat's
tlog (https://github.com/Scribery/tlog) which can record all
terminal activity done via ssh, ship it offsite and replay it
for auditing purposes. So if an administrator does turn off any
logging (presumably including tlog itself), you'll at least be
able to see them turning it off.
cheers,
raf
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 21:43 Bruce Momjian <bruce@momjian.us>
parent: raf <raf@raf.org>
0 siblings, 0 replies; 40+ messages in thread
From: Bruce Momjian @ 2018-08-15 21:43 UTC (permalink / raw)
To: raf <raf@raf.org>; +Cc: pgsql-admin@lists.postgresql.org
On Thu, Aug 16, 2018 at 07:41:11AM +1000, raf wrote:
> Bruce Momjian wrote:
>
> > On Tue, Aug 14, 2018 at 03:59:19PM -0400, Bruce Momjian wrote:
> > > On Fri, Aug 10, 2018 at 04:06:40PM -0400, Benedict Holland wrote:
> > > > I also would take Bruce's comment with a massive grain of salt. Everything that
> > > > everyone does on a database is logged somewhere assuming proper logging. Now do
> > > > you have the person-power to go through gigs of plain text logs to find out if
> > > > someone is doing something shady... that is a question for your management
> > > > team. Also, if you suspect someone of doing something shady, you should
> > > > probably revoke their admin rights.
> > >
> > > Agreed, the best way to limit the risk of undetected DBA removal of data
> > > is secure auditing --- I should have mentioned that.
> >
> > So, how do you securely audit? You ship the logs to a server that isn't
> > controlled by the DBA, via syslog? How do you prevent the DBA from
> > turning off logging when the want to so something undetected? Do you
> > log the turning off of logging?
> >
> > --
> > Bruce Momjian <bruce@momjian.us> http://momjian.us
>
> Yes. You can set up terminal session logging with redhat's
> tlog (https://github.com/Scribery/tlog) which can record all
> terminal activity done via ssh, ship it offsite and replay it
> for auditing purposes. So if an administrator does turn off any
> logging (presumably including tlog itself), you'll at least be
> able to see them turning it off.
Ah, yes, I can see that as helpful.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 22:30 Tim Cross <theophilusx@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 40+ messages in thread
From: Tim Cross @ 2018-08-15 22:30 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Evan Rempel <erempel@uvic.ca>; pgsql-admin@lists.postgresql.org
Bruce Momjian <bruce@momjian.us> writes:
> On Wed, Aug 15, 2018 at 01:52:43PM -0700, Evan Rempel wrote:
>> There are just a ton of configuration elements that the DBAs need to decide on and implement that require
>> configuration of components that are outside of the database proper.
>>
>> It was a worthwhile discussion. One needs to trust the data stewards.
>
> Agreed. I just wish it had a more positive outcome. ;-)
I think the key points to note are
1. At some point in the hierarchy of privileges, there is a need to have
confidence and trust in at least one individual who will have (and need)
sufficient privileges that restricting them via technology will become
impossible as they will have sufficient power to circumvent
anything. Typically, it will be more than a single individual to avoid
the proverbial 'hit by a bus' risk.
2. Security comes at a cost. That cost is reduced convenience and
increased bureaucracy. The challenge is getting the right balance where
that cost is kept as low as possible while mitigating the identified
risks. There is no one model which will suit all.
3. The principals of minimal privileges and separation of
responsibilities is often a useful guideline. I have seen places where a
'Westminster' model is applied (distinct executive (C level),
legislative (policy & Governance), judiciary (risk & audit).
The real challenge with security is that it isn't actually a technology
problem. It is a business problem. The technology can provide mechanisms
to help address the issues, but technology alone cannot solve them.
Where it becomes challenging is at the cross-over points. The executive
should define overall high level strategy and direction, the legislature
clarifies and codifies the strategies and business processes to enable
staff to make appropriate decisions and the judiciary ensures everyone
is playing by the rules. However, these three areas typically have only
limited understanding of the technology (knowledge will typically
increase as you work down from executive, legislature to judiciary). As
DBAs, we need to understand the principals and risks and apply the
technology in the best way possible to support the business and the
defined strategies.
Tim
--
Tim Cross
^ permalink raw reply [nested|flat] 40+ messages in thread
* Re: How to revoke privileged from PostgreSQL's superuser
@ 2018-08-15 23:08 Evan Bauer <evanbauer@mac.com>
parent: Tim Cross <theophilusx@gmail.com>
0 siblings, 0 replies; 40+ messages in thread
From: Evan Bauer @ 2018-08-15 23:08 UTC (permalink / raw)
To: Tim Cross <theophilusx@gmail.com>; +Cc: Bruce Momjian <bruce@momjian.us>; Evan Rempel <erempel@uvic.ca>; pgsql-admin@lists.postgresql.org
Bruce and Tim,
All good points — some of which go back to my quick response to Bejita’s original “newbie DBA” question back on 6-Aug. We’ve been talking about this for nine days now — it is clearly a challenging and thought-provoking issue.
I agree that you have to start with security business requirements that become policies — and often these policies need to implement governmental or industry regulatory requirements. Least-Privileged access and segregation of duties are just two of the policy principals that need to be applied to the business architecture that makes use of the technical architecture.
Controls that implement these policies have to be applied in-depth for successful NIST 800-171, NIST 800-53, HIPAA HITRUST, and similar sensitive environments. (My apologies to my international colleagues for my US-centric background.)
An effective (and auditable as effective) solution to building a highly secure information system with PostgreSQL to meet these business security requirements requires engineering the whole environment, including (but not limited to):
network firewall rules,
jump boxes with logging shells,
Identity and access management (e.g. LDAP or AD),
key and credential management,
SELinux roles, access controls, and privileges,
off-node logging to a SIEM with log inspection,
in addition to securely configuring the PostgreSQL cluster itself (including pgaudit).
Joe Conway of Crunchy delivered a meaty presentation on locking down Postgres to meet US Federal requirements at PGcon in Ottawa. His slides are at https://www.joeconway.com/presentations/SecurePostgreSQL-PGCon-2018.pdf — there is a lot there and it is worth taking the time to go through them. (Needless to say, Joe didn’t speak to all 69 slides in a 45 minute time slot.) He makes the superuser abuse possibilities clear, illustrating the reasons that the other layers are needed to provide a state-of-the-practice secure Postgres implementation.
Security architecture is an increasingly discipline within systems architecture — it applies to database applications as much or more as anything else in the IT infrastructure.
- Evan
Evan Bauer
eb@evanbauer.com
+1 646 641 2973
Skype: evanbauer
> On Aug 15, 2018, at 18:30, Tim Cross <theophilusx@gmail.com> wrote:
>
>
> Bruce Momjian <bruce@momjian.us> writes:
>
>> On Wed, Aug 15, 2018 at 01:52:43PM -0700, Evan Rempel wrote:
>>> There are just a ton of configuration elements that the DBAs need to decide on and implement that require
>>> configuration of components that are outside of the database proper.
>>>
>>> It was a worthwhile discussion. One needs to trust the data stewards.
>>
>> Agreed. I just wish it had a more positive outcome. ;-)
>
> I think the key points to note are
>
> 1. At some point in the hierarchy of privileges, there is a need to have
> confidence and trust in at least one individual who will have (and need)
> sufficient privileges that restricting them via technology will become
> impossible as they will have sufficient power to circumvent
> anything. Typically, it will be more than a single individual to avoid
> the proverbial 'hit by a bus' risk.
>
> 2. Security comes at a cost. That cost is reduced convenience and
> increased bureaucracy. The challenge is getting the right balance where
> that cost is kept as low as possible while mitigating the identified
> risks. There is no one model which will suit all.
>
> 3. The principals of minimal privileges and separation of
> responsibilities is often a useful guideline. I have seen places where a
> 'Westminster' model is applied (distinct executive (C level),
> legislative (policy & Governance), judiciary (risk & audit).
>
> The real challenge with security is that it isn't actually a technology
> problem. It is a business problem. The technology can provide mechanisms
> to help address the issues, but technology alone cannot solve them.
>
> Where it becomes challenging is at the cross-over points. The executive
> should define overall high level strategy and direction, the legislature
> clarifies and codifies the strategies and business processes to enable
> staff to make appropriate decisions and the judiciary ensures everyone
> is playing by the rules. However, these three areas typically have only
> limited understanding of the technology (knowledge will typically
> increase as you work down from executive, legislature to judiciary). As
> DBAs, we need to understand the principals and risks and apply the
> technology in the best way possible to support the business and the
> defined strategies.
>
> Tim
>
> --
> Tim Cross
>
^ permalink raw reply [nested|flat] 40+ messages in thread
end of thread, other threads:[~2018-08-15 23:08 UTC | newest]
Thread overview: 40+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-08-06 09:48 How to revoke privileged from PostgreSQL's superuser bejita0409@yahoo.co.jp
2018-08-06 10:19 ` Vijaykumar Jain <vjain@opentable.com>
2018-08-06 10:33 ` Charles Clavadetscher <clavadetscher@swisspug.org>
2018-08-06 13:19 ` David G. Johnston <david.g.johnston@gmail.com>
2018-08-06 13:44 ` Bear Giles <bgiles@coyotesong.com>
2018-08-10 19:41 ` Bruce Momjian <bruce@momjian.us>
2018-08-10 20:06 ` Benedict Holland <benedict.m.holland@gmail.com>
2018-08-14 19:59 ` Bruce Momjian <bruce@momjian.us>
2018-08-15 01:00 ` dangal <danielito.gallo@gmail.com>
2018-08-15 02:45 ` Evan Bauer <evanbauer@mac.com>
2018-08-15 03:44 ` dangal <danielito.gallo@gmail.com>
2018-08-15 03:50 ` Evan Rempel <erempel@uvic.ca>
2018-08-15 04:21 ` Tim Cross <theophilusx@gmail.com>
2018-08-15 14:59 ` Bruce Momjian <bruce@momjian.us>
2018-08-15 16:05 ` Evan Rempel <erempel@uvic.ca>
2018-08-15 19:28 ` Bruce Momjian <bruce@momjian.us>
2018-08-15 20:13 ` Evan Rempel <erempel@uvic.ca>
2018-08-15 20:26 ` Bruce Momjian <bruce@momjian.us>
2018-08-15 20:52 ` Evan Rempel <erempel@uvic.ca>
2018-08-15 20:57 ` Bruce Momjian <bruce@momjian.us>
2018-08-15 21:03 ` Scott Ribe <scott_ribe@elevated-dev.com>
2018-08-15 21:09 ` Bruce Momjian <bruce@momjian.us>
2018-08-15 21:16 ` Scott Ribe <scott_ribe@elevated-dev.com>
2018-08-15 22:30 ` Tim Cross <theophilusx@gmail.com>
2018-08-15 23:08 ` Evan Bauer <evanbauer@mac.com>
2018-08-15 20:28 ` David G. Johnston <david.g.johnston@gmail.com>
2018-08-15 21:41 ` raf <raf@raf.org>
2018-08-15 21:43 ` Bruce Momjian <bruce@momjian.us>
2018-08-06 13:22 ` Bear Giles <bgiles@coyotesong.com>
2018-08-06 13:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-08-06 13:45 ` Bear Giles <bgiles@coyotesong.com>
2018-08-06 14:13 ` Evan Bauer <evanbauer@mac.com>
2018-08-06 14:45 ` Bear Giles <bgiles@coyotesong.com>
2018-08-10 20:12 ` Rui DeSousa <rui@crazybean.net>
2018-08-10 23:21 ` Benedict Holland <benedict.m.holland@gmail.com>
2018-08-11 02:34 ` Rui DeSousa <rui@crazybean.net>
2018-08-14 19:51 ` Bruce Momjian <bruce@momjian.us>
2018-08-06 22:46 ` bejita0409@yahoo.co.jp
2018-08-07 00:44 ` Bear Giles <bgiles@coyotesong.com>
2018-08-06 22:27 ` Tim Cross <theophilusx@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox