Fix Postgres root-enable

[Petr Malik]

Fix PostgreSQL root functions

The default PostgreSQL administration account is 'postgres'.

In the current implementation Trove uses the 'postgres' account and
return a new superuser called 'root' when the root access is requested.
The user 'root' has however no special meaning in PostgreSQL and the
existing applications may rely on the default superuser name 'postgres'.

Trove should be using its own administrative account (os_admin) instead.

Notes:

 The current implementation is broken for variaous reasons:

 - It uses UUIDs in place of 'secure' password.
 - It creates a 'root' user, but no database for it.
   The clients won't be able to authenticate without explicitly
   providing an existing database name.
 - The created 'root' user has no 'SUPERUSER' attribute and
   hence is not a real superuser (cannot perform certain tasks)...
 - The implementation suffers a defect that allows a non-root user
   gain root access to an instance without marking is as 'root-enabled'
   A similar defect exists in other datastores (MySQL) too:

 1. Create an instance.
 2. Enable root.
 3. Use your root access to change the password of the built-in
'postgres' account (Trove will still work because it uses the
'peer' authentication method - the UNIX account).
 4. Login as 'postgres' using the changed password and drop the
created 'root' account.
 5. Backup & restore the instance.
 6. Trove reports the root has never been enabled (it checks for existence of
superuser accounts other than the built-in 'postgres').
 7. You enjoy the root access of the 'postgres' user
(the password is not reset on restore).

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/243292

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/243292
Committed: https://git.openstack.org/cgit/openstack/trove/commit/?id=84e901852439a76b2c7b4b6d79d4f47fb20291e2
Submitter: Jenkins
Branch: master

commit 84e901852439a76b2c7b4b6d79d4f47fb20291e2
Author: Petr Malik <email address hidden>
Date: Mon Sep 21 14:19:57 2015 -0400

    Fix PostgreSQL root functions

    The default PostgreSQL administration account is 'postgres'.

    This account always exists and cannot be removed.
    Its attributes and access can however be altered.

    Clients can connect from the localhost or remotely via TCP/IP:

    Local clients (e.g. psql) can connect from a preset *system* account
    called 'postgres'.
    This system account has no password and is *locked* by default,
    so that it can be used by *local* users only.
    It should *never* be enabled (or it's password set)!!!
    That would just open up a new attack vector on the system account.

    Remote clients should use a build-in *database* account of the same
    name. It's password can be changed using the "ALTER USER" statement.

    Access to this account is disabled by Trove exposed only once the
    superuser access is requested.

    In the current implementation Trove uses the 'postgres' account and
    return a new superuser called 'root' when the root access is requested.
    The user 'root' has however no special meaning in PostgreSQL and the
    existing applications may rely on the default superuser name 'postgres'.

    This patch includes the following fixes:

     - Make Trove create and use its own administrative account (os_admin).
     - Disable (disable logins) the built-in 'postgres' account by default.
     - Enable and return the 'postgres' user when the root access is
       requested.
     - Fix failure on repeated 'root-enable' calls. The new implementation
       just regenerates the user's password.
     - Prevent users from hijacking the 'os_admin' account by allowing only
       local access.
     - Use the existing framework to generate secure passwords.
     - Stop using the system 'postgres' user to execute command-line client
       calls. Communicate with database via a native Python interface
       (like other datastores) instead.
     - Remove unnecessary complex result-set caching on the guest.
     - Simplify the status-checking code using the native exceptions.
       Avoid performing complex checks with uncertain benefits
       (i.e. attempt to guess the state of the process).

    Notes:

     The current implementation is broken for variaous reasons:

     - It uses UUIDs in place of 'secure' password.
     - It creates a 'root' user, but no database for it.
       The clients won't be able to authenticate without explicitly
       providing an existing database name.
     - The created 'root' user has no 'SUPERUSER' attribute and
       hence is not a real superuser (cannot perform certain tasks)...
     - The implementation suffers a defect that allows a non-root user
       gain root access to an instance without marking is as 'root-enabled'
       A similar defect exists in other datastores (MySQL) too:
  ...

[Thierry Carrez (ttx)]

This issue was fixed in the openstack/trove 5.0.0.0b2 development milestone.