Subject: Re: How to revoke privileged from PostgreSQL's superuser
To: Bruce Momjian <bruce@momjian.us>
Cc: 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>
 <10cde505-db4b-bbeb-7d8a-7c4e145c6042@uvic.ca>
 <20180815192838.GA5005@momjian.us>
From: Evan Rempel <erempel@uvic.ca>
Message-ID: <c6ef7b1d-2ed1-623a-ea9e-e6470aca1da7@uvic.ca>
Date: Wed, 15 Aug 2018 13:13:10 -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: <20180815192838.GA5005@momjian.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Language: en-MW
Precedence: bulk

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