Re: How to revoke privileged from PostgreSQL's superuser

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