Re: BUG #5475: Problem during Instalation

On 09/06/10 16:29, Dave Page wrote:
> On Wed, Jun 9, 2010 at 8:56 AM, Craig Ringer
> <craig(at)postnewspapers(dot)com(dot)au> wrote:
>
>> Only because the PostgreSQL system user account password is coupled to
>> the account of the "postgres" user in the PostgreSQL database cluster
>> (right?). I'm not at a Windows box right now so I can't test to see if
>> altering the Pg role's password changes the system password or vice
>> versa, but I'd be surprised if they did.
>
> It won't, but the point is that we have to ask for a password anyway
> so we don't gain anything by not asking the user for one of them.

The issue is that the user has to know if they've installed Pg before
(and thus already have a "postgres" service account) or not. Some
clearly don't, or have forgotten the service account password long ago.

In general, users expect that an uninstall of an app will remove the
app's configuration. Pg ... sort of ... does. It leaves the user account
in place, though it created it in the first place. So the user has to
know if Pg has been installed previously and if so what the service
account password was. This demonstrably confuses people who try to
uninstall and reinstall Pg to resolve issues (service account
configuration related or other).

As you've pointed out good reasons why just storing a generated password
may not be a good idea, let me propose another approach that might help
users with what's clearly a confusing point for many:

- During the install, test for the existence of a 'postgres' user
account before displaying the password prompt panel of the installer
"wizard".

- If such an account does not exist, display the existing UI password
prompt ui with simplified language indicating that you're creating a new
password not entering one already set. People seem to struggle with this
at present.

- If such an account already exists, tell the user that PostgreSQL has
been installed in the past or another version is currently installed, so
they must provide the password that was supplied during that prior
installation. If they do not know the password, they may press an
offered "reset password" button to enter a new one, but they're warned
that doing so will cause any other versions of PostgreSQL to stop working.

------ or possibly ..... --------

- When the postgres user password is reset on the PostgreSQL service via
the Pg installer, update or offer to update *all* service registrations
for any service using the postgres account so that they use the same
password, thus avoiding breaking old versions when the user has
forgotten the service account password.

>> Personally I'm firmly of the opinion that the user should never need
>> to know anything about the password (if any) for the "postgres"
>> Windows user account that's used for the service account.
>
> So how would you install something like pgAgent, which you would most
> likely want to run under the same account?

Installers run as admin; it'd read the registry key during installation
and use it for the CreateService call.

> You can have multiple administrators on a machine, and storage of a
> plain text password in the registry would allow knowledge of that
> password to leak from one administrator to another, which may cause
> security concerns in a tightly controlled environment. As far as I'm
> aware, Windows doesn't provide any way for an Administrator to read
> any other local/domain passwords in anything other than an
> encrypted/hashed form, so this would be a new issue, not normally
> seen.

True, and a good point, particularly on domain setups.

OTOH, if it's a generated password, leaking it has little effect as it's
unique to the machine and only grants access to a service account that
the admin user already has access to by virtue of their admin status on
that machine. It's not re-used anywhere and if you can read it you can
change it or become that user.

Nonetheless, I'll grant that I understand and agree with your caution
about that notion. I don't like it much either, I just don't like the
way things work right now any more, as the questions and posts here
suggest that it causes plenty of confusion for users.

> Most other services use one of the 'special' accounts like 'Network
> Service', however doing that with Postgres doesn't necessarily play
> well with features like COPY, which is why we've avoiding doing that
> since 8.0.

That much makes perfect sense and I certainly wasn't arguing that it
should live in the generic service account. There are clear and good
reasons to run Pg in its own service account, well beyond merely
separating it from other services.

A bit of digging reveals that most decent services are using local or
domain accounts, with passwords, and are just living with the
administrator hassle this creates, expecting admins to be competent
enough to change and maintain service passwords. I'm not sure Pg can
rely on that for desktop installs, though, so it might need to offer a
way to take service account password management into its own hands.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/