Thread: How to revoke privileged from PostgreSQL's superuser

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.

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.

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

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

Bejita,

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

1. Determine and document your organizations data access policies.  They could be very simple, but it is important to
documentand share them. 
2. Make use of a privileged access management scheme so that no one has unfettered access to superuser (postgres, root,
etal) passwords, but has to check them out from an audited system for a specific task and time period, with appropriate
approvalprocesses if needed. 
3. Use pgaudit to maintain an independent record of all sensitive access. The doc is at:
https://github.com/pgaudit/pgaudit/blob/master/README.md
4. Create a set of administrative roles privileged to only the needs of the tasks required.  Under normal
circumstances,no one should use the ‘postgres’ account for production access.  This also provides a means of enforcing
complianceto your policies.  Tom Vondra wrote a good introduction here:
https://blog.2ndquadrant.com/auditing-users-and-roles-in-postgresql/
5. Setup automated (I tend to use ELK or Splunk) examination of the audit logs for violations and anomalies.  Human
reviewat regular intervals will also make your regulators or security auditors happier (they are never really happy.) 
6. Make use of row-level access control and encryptions as appropriate to protect your data.  This blog post by
JonathanKatz is a good introduction:
https://info.crunchydata.com/blog/a-postgresql-row-level-security-primer-creating-large-policies 

There is a lot of thought and work that goes into executing the steps above, but administering systems and databases
thathandle sensitive data is a serious responsibility and requires requirements definition, planning, architecture,
execution,and then continuous monitoring and improvement.  As someone new to the DBA role, you should talk to your
architecturecolleagues as you have some good and serious work ahead of you. 

Cheers,

- Evan



> On Aug 6, 2018, at 09:43, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Bear Giles <bgiles@coyotesong.com> writes:
>> In postgresql the equivalent user is 'postgres'. Nobody should ever be
>> logged in as that user once you've created the initial user(s). What
>> postgresql calls a 'superuser' is just a user with a few permissions set by
>> default. It's easy to grant the same privileges to any user, or drop them
>> from someone created as a superuser.
>
> Well, more to the point, a superuser is somebody with the rolsuper bit
> set in their pg_authid entry.  You can revoke the bootstrap superuser's
> superuserness if you have a mind to -- see ALTER USER.  However, as
> everyone has pointed out already, this is a bad idea and you will end
> up undoing it.  (Figuring out how to do that without a reinstall is left
> as penance for insisting on a bad idea.  It is possible, and I think
> even documented.)
>
> However: a whole lot of what the bootstrap superuser can do is inherent
> in being the owner of all the built-in database objects, and that you
> cannot get rid of.  Objects have to be owned by somebody.
>
>             regards, tom lane
>

Bejita,

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

1. Determine and document your organizations data access policies.  They could be very simple, but it is important to
documentand share them. 
2. Make use of a privileged access management scheme so that no one has unfettered access to superuser (postgres, root,
etal) passwords, but has to check them out from an audited system for a specific task and time period, with appropriate
approvalprocesses if needed. 
3. Use pgaudit to maintain an independent record of all sensitive access. The doc is at:
https://github.com/pgaudit/pgaudit/blob/master/README.md
4. Create a set of administrative roles privileged to only the needs of the tasks required.  Under normal
circumstances,no one should use the ‘postgres’ account for production access.  This also provides a means of enforcing
complianceto your policies.  Tom Vondra wrote a good introduction here:
https://blog.2ndquadrant.com/auditing-users-and-roles-in-postgresql/
5. Setup automated (I tend to use ELK or Splunk) examination of the audit logs for violations and anomalies.  Human
reviewat regular intervals will also make your regulators or security auditors happier (they are never really happy.) 
6. Make use of row-level access control and encryptions as appropriate to protect your data.  This blog post by
JonathanKatz is a good introduction:
https://info.crunchydata.com/blog/a-postgresql-row-level-security-primer-creating-large-policies 

There is a lot of thought and work that goes into executing the steps above, but administering systems and databases
thathandle sensitive data is a serious responsibility and requires requirements definition, planning, architecture,
execution,and then continuous monitoring and improvement.  As someone new to the DBA role, you should talk to your
architecturecolleagues as you have some good and serious work ahead of you. 

Cheers,

- Evan



> On Aug 6, 2018, at 09:43, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Bear Giles <bgiles@coyotesong.com> writes:
>> In postgresql the equivalent user is 'postgres'. Nobody should ever be
>> logged in as that user once you've created the initial user(s). What
>> postgresql calls a 'superuser' is just a user with a few permissions set by
>> default. It's easy to grant the same privileges to any user, or drop them
>> from someone created as a superuser.
>
> Well, more to the point, a superuser is somebody with the rolsuper bit
> set in their pg_authid entry.  You can revoke the bootstrap superuser's
> superuserness if you have a mind to -- see ALTER USER.  However, as
> everyone has pointed out already, this is a bad idea and you will end
> up undoing it.  (Figuring out how to do that without a reinstall is left
> as penance for insisting on a bad idea.  It is possible, and I think
> even documented.)
>
> However: a whole lot of what the bootstrap superuser can do is inherent
> in being the owner of all the built-in database objects, and that you
> cannot get rid of.  Objects have to be owned by somebody.
>
>             regards, tom lane
>

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

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

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
protectingdata from improper use by administrators.  Some of the elements that (to my mind) ought to be part of
achievingthat outcome are:
 
> 
> 1. Determine and document your organizations data access policies.  They could be very simple, but it is important to
documentand share them.
 
> 2. Make use of a privileged access management scheme so that no one has unfettered access to superuser (postgres,
root,et al) passwords, but has to check them out from an audited system for a specific task and time period, with
appropriateapproval 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
complianceto your policies.  Tom Vondra wrote a good introduction here:
https://blog.2ndquadrant.com/auditing-users-and-roles-in-postgresql/
> 5. Setup automated (I tend to use ELK or Splunk) examination of the audit logs for violations and anomalies.  Human
reviewat regular intervals will also make your regulators or security auditors happier (they are never really happy.)
 
> 6. Make use of row-level access control and encryptions as appropriate to protect your data.  This blog post by
JonathanKatz is a good introduction:
https://info.crunchydata.com/blog/a-postgresql-row-level-security-primer-creating-large-policies
 
> 
> There is a lot of thought and work that goes into executing the steps above, but administering systems and databases
thathandle sensitive data is a serious responsibility and requires requirements definition, planning, architecture,
execution,and then continuous monitoring and improvement.  As someone new to the DBA role, you should talk to your
architecturecolleagues as you have some good and serious work ahead of you.
 
> 
> Cheers,
> 
> - Evan
> 
> 
> 
> > On Aug 6, 2018, at 09:43, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > 
> > Bear Giles <bgiles@coyotesong.com> writes:
> >> In postgresql the equivalent user is 'postgres'. Nobody should ever be
> >> logged in as that user once you've created the initial user(s). What
> >> postgresql calls a 'superuser' is just a user with a few permissions set by
> >> default. It's easy to grant the same privileges to any user, or drop them
> >> from someone created as a superuser.
> > 
> > Well, more to the point, a superuser is somebody with the rolsuper bit
> > set in their pg_authid entry.  You can revoke the bootstrap superuser's
> > superuserness if you have a mind to -- see ALTER USER.  However, as
> > everyone has pointed out already, this is a bad idea and you will end
> > up undoing it.  (Figuring out how to do that without a reinstall is left
> > as penance for insisting on a bad idea.  It is possible, and I think
> > even documented.)
> > 
> > However: a whole lot of what the bootstrap superuser can do is inherent
> > in being the owner of all the built-in database objects, and that you
> > cannot get rid of.  Objects have to be owned by somebody.
> > 
> >             regards, tom lane
> > 
> 
> 
> 

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
protectingdata from improper use by administrators.  Some of the elements that (to my mind) ought to be part of
achievingthat outcome are:
 
> 
> 1. Determine and document your organizations data access policies.  They could be very simple, but it is important to
documentand share them.
 
> 2. Make use of a privileged access management scheme so that no one has unfettered access to superuser (postgres,
root,et al) passwords, but has to check them out from an audited system for a specific task and time period, with
appropriateapproval 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
complianceto your policies.  Tom Vondra wrote a good introduction here:
https://blog.2ndquadrant.com/auditing-users-and-roles-in-postgresql/
> 5. Setup automated (I tend to use ELK or Splunk) examination of the audit logs for violations and anomalies.  Human
reviewat regular intervals will also make your regulators or security auditors happier (they are never really happy.)
 
> 6. Make use of row-level access control and encryptions as appropriate to protect your data.  This blog post by
JonathanKatz is a good introduction:
https://info.crunchydata.com/blog/a-postgresql-row-level-security-primer-creating-large-policies
 
> 
> There is a lot of thought and work that goes into executing the steps above, but administering systems and databases
thathandle sensitive data is a serious responsibility and requires requirements definition, planning, architecture,
execution,and then continuous monitoring and improvement.  As someone new to the DBA role, you should talk to your
architecturecolleagues as you have some good and serious work ahead of you.
 
> 
> Cheers,
> 
> - Evan
> 
> 
> 
> > On Aug 6, 2018, at 09:43, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > 
> > Bear Giles <bgiles@coyotesong.com> writes:
> >> In postgresql the equivalent user is 'postgres'. Nobody should ever be
> >> logged in as that user once you've created the initial user(s). What
> >> postgresql calls a 'superuser' is just a user with a few permissions set by
> >> default. It's easy to grant the same privileges to any user, or drop them
> >> from someone created as a superuser.
> > 
> > Well, more to the point, a superuser is somebody with the rolsuper bit
> > set in their pg_authid entry.  You can revoke the bootstrap superuser's
> > superuserness if you have a mind to -- see ALTER USER.  However, as
> > everyone has pointed out already, this is a bad idea and you will end
> > up undoing it.  (Figuring out how to do that without a reinstall is left
> > as penance for insisting on a bad idea.  It is possible, and I think
> > even documented.)
> > 
> > However: a whole lot of what the bootstrap superuser can do is inherent
> > in being the owner of all the built-in database objects, and that you
> > cannot get rid of.  Objects have to be owned by somebody.
> > 
> >             regards, tom lane
> > 
> 
> 
> 

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 +

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 +

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 +

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 +

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 +

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 +

How audit dba? 



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

The pgaudit extension provides the mechanism used for this by most organizations I know of with sensitive data in
PostgreSQLdatabases.  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
>

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

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
>

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 +

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 +

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.

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 +

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

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 +

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).

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

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 +

> 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
notdisable logging. Of course, you still have to trust somebody with that configuration, but it is possible to separate
responsibilitiesif you work hard enough at it. 

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 +

> 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

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

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 +

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