Subject: Re: How to revoke privileged from PostgreSQL's superuser
To: pgsql-admin@lists.postgresql.org
References: <913907.28546.qm@web103014.mail.kks.yahoo.co.jp>
 <CAKFQuwa0SyyzaKEtQzBc008HvtR7GVzv4Nd75Qe_OXtAqzgGGA@mail.gmail.com>
 <20180810194105.GD7840@momjian.us>
 <CAD+mzow89NrETPt57G21BN9cdf90uHYGnCiC_8wR8REoudnt5g@mail.gmail.com>
 <20180814195919.GA29140@momjian.us> <20180815145912.GA11573@momjian.us>
From: Evan Rempel <erempel@uvic.ca>
Message-ID: <10cde505-db4b-bbeb-7d8a-7c4e145c6042@uvic.ca>
Date: Wed, 15 Aug 2018 09:05:51 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.6.0
MIME-Version: 1.0
In-Reply-To: <20180815145912.GA11573@momjian.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-MW
Precedence: bulk

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.