Thread: Minimising windows installer password confusion

Minimising windows installer password confusion

From
Dave Page
Date:
Probably the most common issue we see from Windows users of PostgreSQL
is confusion over the passwords the installer asks for during
installation and upgrade. I'd like to try to reduce that, but am
struggling to come up with clear, simple wording.

Some background: By default the installer will use 'postgres' for both
the service (OS) account, and the database superuser account. It will
use the same password for both (though, users have complete control at
the command line if they want it, which is why the account names are
shown in the installer). Unlike *nix installations, the service
account must have a password, which is required during both
installation and upgrade to configure the PostgreSQL service. We
therefore ask for the password during both installation and upgrade.
If the user account exists (which can happen if there has previously
been an installation of Postgres on the system), the user must specify
the existing password for the account during installation (and of
course, during all upgrades). This is where users normally get stuck,
as they've forgotten the password they set in the past.

Attached are some screenshots of the current installation and upgrade
steps in question, and the password incorrect message box. The
suggested new text for the steps that I've come up with is below. I'd
welcome suggestions or improvements to help reduce pgsql-bugs traffic!

Thanks.

Installation step:

*******************************
Please provide a password for the database superuser (postgres) and
service account (postgres). If the service account already exists in
Windows, you must enter the current password for the account. If the
account does not exist, it will be created when you click 'Next'.

Password        [                   ]
Retype password [                   ]

If the service account already exists on your computer and you don't
know the password, you can reset it using the Computer Management tool
in the Administrative Tools section of the Control Panel. You may need
update any other services that are using this account with the new
password.

Note that this password is not related to any used on the PostgreSQL
or EnterpriseDB websites.
*******************************

Upgrade step:

*******************************
Please provide the password for the service account (postgres).

Password        [                   ]
Retype password [                   ]

If you don't know the password, you can reset it using the Computer
Management tool in the Administrative Tools section of the Control
Panel. You may need update any other services that are using this
account with the new password.

Note that this password is not related to any used on the PostgreSQL
or EnterpriseDB websites.
*******************************

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Attachment

Re: Minimising windows installer password confusion

From
"Kevin Grittner"
Date:
Dave Page  wrote:
> Probably the most common issue we see from Windows users of
> PostgreSQL is confusion over the passwords the installer asks for
> during installation and upgrade.
Yeah, I think so.
> Attached are some screenshots of the current installation and
> upgrade steps in question, and the password incorrect message box.
> The suggested new text for the steps that I've come up with is
> below. I'd welcome suggestions or improvements to help reduce
> pgsql-bugs traffic!
Are they running the installation as a system administrator?  If so,
rather than throwing up an error message and telling them to go use
other tools to reset the password, is it possible for the
administrator account to force a password change?  If that is
possible, it seems like it would be a lot more friendly.  If not,
perhaps the old postgres user could be renamed, and a new one created
with the password?
Unless there are technical limitations of the Windows environment
which make it impossible to fix this from within the installer, it
just seems like we should fix the problem rather than putting up an
error dialog box.
-Kevin


Re: Minimising windows installer password confusion

From
Magnus Hagander
Date:
On Tue, Jun 12, 2012 at 2:26 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Dave Page  wrote:
>
>> Probably the most common issue we see from Windows users of
>> PostgreSQL is confusion over the passwords the installer asks for
>> during installation and upgrade.
>
> Yeah, I think so.
>
>> Attached are some screenshots of the current installation and
>> upgrade steps in question, and the password incorrect message box.
>> The suggested new text for the steps that I've come up with is
>> below. I'd welcome suggestions or improvements to help reduce
>> pgsql-bugs traffic!
>
> Are they running the installation as a system administrator?  If so,
> rather than throwing up an error message and telling them to go use
> other tools to reset the password, is it possible for the
> administrator account to force a password change?  If that is
> possible, it seems like it would be a lot more friendly.  If not,
> perhaps the old postgres user could be renamed, and a new one created
> with the password?

That might break another app running nuder that account. Such as a
different version of PostgreSQL...

But an option could be to create a different account to run it under,
I guess... Leaving the old one where it is. I think that's better than
renaming the old one, really.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


Re: Minimising windows installer password confusion

From
"Kevin Grittner"
Date:
Magnus Hagander  wrote:
> Kevin Grittner  wrote:
>> Are they running the installation as a system administrator? If
>> so, rather than throwing up an error message and telling them to
>> go use other tools to reset the password, is it possible for the
>> administrator account to force a password change? If that is
>> possible, it seems like it would be a lot more friendly. If not,
>> perhaps the old postgres user could be renamed, and a new one
>> created with the password?
> 
> That might break another app running nuder that account. Such as a
> different version of PostgreSQL...
> 
> But an option could be to create a different account to run it
> under, I guess... Leaving the old one where it is. I think that's
> better than renaming the old one, really.
That makes sense.  I just think we should try very hard to make the
installer "just work" to the extent possible, rather than trying to
direct the user in how to use system tools in the middle of the
process.
-Kevin


Re: Minimising windows installer password confusion

From
Dave Page
Date:
On Tue, Jun 12, 2012 at 1:35 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Magnus Hagander  wrote:
>> Kevin Grittner  wrote:
>
>>> Are they running the installation as a system administrator? If
>>> so, rather than throwing up an error message and telling them to
>>> go use other tools to reset the password, is it possible for the
>>> administrator account to force a password change? If that is
>>> possible, it seems like it would be a lot more friendly. If not,
>>> perhaps the old postgres user could be renamed, and a new one
>>> created with the password?
>>
>> That might break another app running nuder that account. Such as a
>> different version of PostgreSQL...

Right.

>> But an option could be to create a different account to run it
>> under, I guess... Leaving the old one where it is. I think that's
>> better than renaming the old one, really.

I'm not keen on adding additional user accounts - that's a security
problem imho. It'll leave the unaware user with multiple accounts on
the system, and may cause those that do understand what's going on
pain because they'll have to deal with multiple accounts for things
like server-side copy.

It also doesn't solve the problem during upgrades, though admittedly
that seems to be less common.

> That makes sense.  I just think we should try very hard to make the
> installer "just work" to the extent possible, rather than trying to
> direct the user in how to use system tools in the middle of the
> process.

Right - that's what always aim to do (and in fact was the number one
driver behind the current generation of installers), and provided the
user remembers their password it works just fine.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Minimising windows installer password confusion

From
Magnus Hagander
Date:
On Tue, Jun 12, 2012 at 2:48 PM, Dave Page <dpage@pgadmin.org> wrote:
> On Tue, Jun 12, 2012 at 1:35 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
>> Magnus Hagander  wrote:
>>> Kevin Grittner  wrote:
>>
>>>> Are they running the installation as a system administrator? If
>>>> so, rather than throwing up an error message and telling them to
>>>> go use other tools to reset the password, is it possible for the
>>>> administrator account to force a password change? If that is
>>>> possible, it seems like it would be a lot more friendly. If not,
>>>> perhaps the old postgres user could be renamed, and a new one
>>>> created with the password?
>>>
>>> That might break another app running nuder that account. Such as a
>>> different version of PostgreSQL...
>
> Right.
>
>>> But an option could be to create a different account to run it
>>> under, I guess... Leaving the old one where it is. I think that's
>>> better than renaming the old one, really.
>
> I'm not keen on adding additional user accounts - that's a security
> problem imho. It'll leave the unaware user with multiple accounts on
> the system, and may cause those that do understand what's going on
> pain because they'll have to deal with multiple accounts for things
> like server-side copy.

Oh, I certainly wouldn't do it without *informing* and verifying it
with the user.


> It also doesn't solve the problem during upgrades, though admittedly
> that seems to be less common.

Why do you need the account at all during upgrades? Don't you just
stop the service and replace the binaries?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


Re: Minimising windows installer password confusion

From
Dave Page
Date:
On Tue, Jun 12, 2012 at 1:49 PM, Magnus Hagander <magnus@hagander.net> wrote:
>
>>
>> I'm not keen on adding additional user accounts - that's a security
>> problem imho. It'll leave the unaware user with multiple accounts on
>> the system, and may cause those that do understand what's going on
>> pain because they'll have to deal with multiple accounts for things
>> like server-side copy.
>
> Oh, I certainly wouldn't do it without *informing* and verifying it
> with the user.

That'll add additional steps for all users, and likely confuse the
novices even more.

>> It also doesn't solve the problem during upgrades, though admittedly
>> that seems to be less common.
>
> Why do you need the account at all during upgrades? Don't you just
> stop the service and replace the binaries?

Because re-running the current installer or running an upgrade should
repair an existing installation as well as doing any upgrades
required.


-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Minimising windows installer password confusion

From
Robert Haas
Date:
On Tue, Jun 12, 2012 at 8:53 AM, Dave Page <dpage@pgadmin.org> wrote:
>> Oh, I certainly wouldn't do it without *informing* and verifying it
>> with the user.
>
> That'll add additional steps for all users, and likely confuse the
> novices even more.

The real issue here is that it's nuts to tell the user "please enter
either a new password or the password for the account that already
exists, but I'm not telling you which one".

What we need is to display a different dialogue based on the situation.

If the account already exists, we should say "Please enter the
password for the existing postgres account.  If you do not know the
password, you can reset it using the Windows control panel."

But if it doesn't already exist, we should say "The installer will
create a postgres account on this machine.  Please enter a password
for the new account."

If we can do that, all of these problems will go away.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Minimising windows installer password confusion

From
Dave Page
Date:
On Tue, Jun 12, 2012 at 2:57 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Jun 12, 2012 at 8:53 AM, Dave Page <dpage@pgadmin.org> wrote:
>>> Oh, I certainly wouldn't do it without *informing* and verifying it
>>> with the user.
>>
>> That'll add additional steps for all users, and likely confuse the
>> novices even more.
>
> The real issue here is that it's nuts to tell the user "please enter
> either a new password or the password for the account that already
> exists, but I'm not telling you which one".

That's a good point.

> What we need is to display a different dialogue based on the situation.
>
> If the account already exists, we should say "Please enter the
> password for the existing postgres account.  If you do not know the
> password, you can reset it using the Windows control panel."
>
> But if it doesn't already exist, we should say "The installer will
> create a postgres account on this machine.  Please enter a password
> for the new account."
>
> If we can do that, all of these problems will go away.

Yeah - that'll require some additional code to check if the account
exists, but we can probably copy/paste that from the existing utility
that creates the account (or better yet, refactor it to allow us to
check or check & create as it does now).

Ashesh/Sachin/Dharam - do you see any potential issues with that?

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Minimising windows installer password confusion

From
Sachin Srivastava
Date:


On Tue, Jun 12, 2012 at 7:43 PM, Dave Page <dpage@pgadmin.org> wrote:
On Tue, Jun 12, 2012 at 2:57 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Jun 12, 2012 at 8:53 AM, Dave Page <dpage@pgadmin.org> wrote:
>>> Oh, I certainly wouldn't do it without *informing* and verifying it
>>> with the user.
>>
>> That'll add additional steps for all users, and likely confuse the
>> novices even more.
>
> The real issue here is that it's nuts to tell the user "please enter
> either a new password or the password for the account that already
> exists, but I'm not telling you which one".

That's a good point.

> What we need is to display a different dialogue based on the situation.
>
> If the account already exists, we should say "Please enter the
> password for the existing postgres account.  If you do not know the
> password, you can reset it using the Windows control panel."
>
> But if it doesn't already exist, we should say "The installer will
> create a postgres account on this machine.  Please enter a password
> for the new account."
>
> If we can do that, all of these problems will go away.

Yeah - that'll require some additional code to check if the account
exists, but we can probably copy/paste that from the existing utility
that creates the account (or better yet, refactor it to allow us to
check or check & create as it does now).

Ashesh/Sachin/Dharam - do you see any potential issues with that?
Nope.. We do have the code to check whether the user exists or not..  

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



--
Regards,
Sachin Srivastava
EnterpriseDB, India

Re: Minimising windows installer password confusion

From
Craig Ringer
Date:
On 06/12/2012 08:48 PM, Dave Page wrote:
> I'm not keen on adding additional user accounts - that's a security 
> problem imho.
It's also an issue for add-ons like PgAgent that aren't necessarily tied 
to one exact version of Pg.
>> That makes sense.  I just think we should try very hard to make the
>> installer "just work" to the extent possible, rather than trying to
>> direct the user in how to use system tools in the middle of the
>> process.
> Right - that's what always aim to do (and in fact was the number one
> driver behind the current generation of installers), and provided the
> user remembers their password it works just fine.
Users don't remember passwords, though. It's one of those constants, and 
is why practically every web site etc out there offers password recovery.

The installer IMO needs to store the postgres account password in a 
registry key with permissions set so that only users with local admin 
rights (ie: who can use the installer) can view it. I don't like the 
idea of storing a password, but it's only going to be accessible if you 
already have rights to the registry as local admin, in which case the 
attacker can just reset it themselves (or root your machine). So long as 
they installer warns that the password shouldn't be one you use 
elsewhere because it can be recovered from your computer, I don't see a 
problem.---

--
Craig Ringer




POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/


Re: Minimising windows installer password confusion

From
Craig Ringer
Date:
On 06/12/2012 08:08 PM, Dave Page wrote:
> Some background: By default the installer will use 'postgres' for both
> the service (OS) account, and the database superuser account. It will
> use the same password for both (though, users have complete control at
> the command line if they want it, which is why the account names are
> shown in the installer). Unlike *nix installations, the service
> account must have a password, which is required during both
> installation and upgrade to configure the PostgreSQL service. We
> therefore ask for the password during both installation and upgrade.
> If the user account exists (which can happen if there has previously
> been an installation of Postgres on the system), the user must specify
> the existing password for the account during installation (and of
> course, during all upgrades). This is where users normally get stuck,
> as they've forgotten the password they set in the past.
They may not even have set it, because the EnterpriseDB installer can be 
run unattended and may've been used by some 3rd party package.

I'd be interested in seeing what Microsoft installers that create 
isolated user accounts do. I think .NET creates one for ASP, so that'd 
be one option to look into.

--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/


Re: Minimising windows installer password confusion

From
Craig Ringer
Date:
On 06/13/2012 01:19 AM, Sachin Srivastava wrote:
>
> On Tue, Jun 12, 2012 at 7:43 PM, Dave Page <dpage@pgadmin.org
> <mailto:dpage@pgadmin.org>> wrote:
>
>     On Tue, Jun 12, 2012 at 2:57 PM, Robert Haas <robertmhaas@gmail.com
>     <mailto:robertmhaas@gmail.com>> wrote:

>      > What we need is to display a different dialogue based on the
>     situation.
>      >
>      > If the account already exists, we should say "Please enter the
>      > password for the existing postgres account.  If you do not know the
>      > password, you can reset it using the Windows control panel."

Why "using the windows control panel" ?

They're running an installer with the rights to create/alter/delete 
users. Shouldn't the installer just offer to reset the "postgres" 
password, after warning them that it'll break other versions of 
PostgreSQL and tools like PgAgent?

IMO, it'd be better for the installer to just take care of this behind 
the scenes. Generate a random password. Store it in the registry in a 
key that only the services manager ( SYSTEM account? ) and local 
administrators can read. Use it in subsequent installs. Make the 
"postgres" database password completely unrelated to it.

--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/


Re: Minimising windows installer password confusion

From
Dave Page
Date:
On Wed, Jun 13, 2012 at 2:12 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
>
> Users don't remember passwords, though. It's one of those constants, and is
> why practically every web site etc out there offers password recovery.
>
> The installer IMO needs to store the postgres account password in a registry
> key with permissions set so that only users with local admin rights (ie: who
> can use the installer) can view it. I don't like the idea of storing a
> password, but it's only going to be accessible if you already have rights to
> the registry as local admin, in which case the attacker can just reset it
> themselves (or root your machine). So long as they installer warns that the
> password shouldn't be one you use elsewhere because it can be recovered from
> your computer, I don't see a problem.---

The idea of storing the password in clear text in the registry gives
me nervous twitches. Whilst is should be secure if done as you
suggest, a) a simple mistake could leave it vulnerable and give us an
embarrassing security issue to deal with. It also doesn't help us in
the cases where users have another installation of PostgreSQL from
somewhere that doesn't store the password (which is likely to be the
case for years to come, even if it was our installer that was used
previously).

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Minimising windows installer password confusion

From
Dave Page
Date:
On Wed, Jun 13, 2012 at 2:18 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> On 06/12/2012 08:08 PM, Dave Page wrote:
>>
>> Some background: By default the installer will use 'postgres' for both
>> the service (OS) account, and the database superuser account. It will
>> use the same password for both (though, users have complete control at
>> the command line if they want it, which is why the account names are
>> shown in the installer). Unlike *nix installations, the service
>> account must have a password, which is required during both
>> installation and upgrade to configure the PostgreSQL service. We
>> therefore ask for the password during both installation and upgrade.
>> If the user account exists (which can happen if there has previously
>> been an installation of Postgres on the system), the user must specify
>> the existing password for the account during installation (and of
>> course, during all upgrades). This is where users normally get stuck,
>> as they've forgotten the password they set in the past.
>
> They may not even have set it, because the EnterpriseDB installer can be run
> unattended and may've been used by some 3rd party package.
>
> I'd be interested in seeing what Microsoft installers that create isolated
> user accounts do. I think .NET creates one for ASP, so that'd be one option
> to look into.

They tend to use the localsystem or networkservice accounts for most
things, which don't have passwords. The reason we don't do that is
that since the early days of 8.0 we've said we want to enable users to
use the service account as if it were a regular account, so that they
can do things like access network resources (useful for server-side
copy for example).

I wasn't overly convinced back then that that was necessary, and I'm
still not now. I suppose we potentially could use the networkservice
account by default, and let advanced users override that on the
command line if they need to. Then remembering the password becomes
their responsibility.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Minimising windows installer password confusion

From
Dave Page
Date:
On Wed, Jun 13, 2012 at 3:07 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> On 06/13/2012 01:19 AM, Sachin Srivastava wrote:
>>
>>
>> On Tue, Jun 12, 2012 at 7:43 PM, Dave Page <dpage@pgadmin.org
>> <mailto:dpage@pgadmin.org>> wrote:
>>
>>    On Tue, Jun 12, 2012 at 2:57 PM, Robert Haas <robertmhaas@gmail.com
>>    <mailto:robertmhaas@gmail.com>> wrote:
>
>
>>     > What we need is to display a different dialogue based on the
>>    situation.
>>     >
>>     > If the account already exists, we should say "Please enter the
>>     > password for the existing postgres account.  If you do not know the
>>     > password, you can reset it using the Windows control panel."
>
>
> Why "using the windows control panel" ?

Because when I wrote the email I was looking for a simple solution
that wouldn't require writing code that has potential to fail
depending on how the users environment is configured (the user account
stuff tends to go wrong in weird ways, for example when used on
domains in unusual (or high security) configurations. We're spending a
lot of effort at the moment getting the 9.2 buildfarm together, and
updating all the StackBuilder add-on packages (think multiple
man-months) - I'm trying not to add to that too much.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Minimising windows installer password confusion

From
Florian Pflug
Date:
On Jun13, 2012, at 11:10 , Dave Page wrote:
> On Wed, Jun 13, 2012 at 2:12 AM, Craig Ringer
> <craig@postnewspapers.com.au> wrote:
>>
>> Users don't remember passwords, though. It's one of those constants, and is
>> why practically every web site etc out there offers password recovery.
>>
>> The installer IMO needs to store the postgres account password in a registry
>> key with permissions set so that only users with local admin rights (ie: who
>> can use the installer) can view it. I don't like the idea of storing a
>> password, but it's only going to be accessible if you already have rights to
>> the registry as local admin, in which case the attacker can just reset it
>> themselves (or root your machine). So long as they installer warns that the
>> password shouldn't be one you use elsewhere because it can be recovered from
>> your computer, I don't see a problem.---
>
> The idea of storing the password in clear text in the registry gives
> me nervous twitches. Whilst is should be secure if done as you
> suggest, a) a simple mistake could leave it vulnerable and give us an
> embarrassing security issue to deal with. It also doesn't help us in
> the cases where users have another installation of PostgreSQL from
> somewhere that doesn't store the password (which is likely to be the
> case for years to come, even if it was our installer that was used
> previously).

Hm, doesn't the registry already contain the postgres service account's
password? AFAIR, on windows you cannot really impersonate an account without
knowing it's password, which is the reason why a) the password of a user
account is stored in the registry if you enable auto-logon and b) you need
to know the service account's password to create a service.

Some googling brought up a tool called isvcpwd[1] which seems to be able to
change service account passwords without breaking services. Judging from a brief
glance over the source code, it does so by iterating over all services
domain-wide, and adjusting the service definition of those which rely on the
modified account(s). So that seems to support the theory that the passwords
are stored in the individual machine's registries.

Some further googling indicates that, yes, the service account passwords
are stored in the registry, but are only accessible to the LocalSystem
account [2]. Querying them from the postgres installer thus isn't really an
option. But what you could do, I guess, is to offer the user the ability to
change the password, and using the approach from [1] to update the affected
service definitions afterwards.

best regards,
Florian Pflug

[1] https://www.itefix.no/i2/isvcpwd
[2] http://www.windowsnetworking.com/kbase/windowstips/windowsnt/registrytips/miscellaneous/LSASecrets.html



Re: Minimising windows installer password confusion

From
Craig Ringer
Date:
On 06/13/2012 05:10 PM, Dave Page wrote:
> The idea of storing the password in clear text in the registry gives
> me nervous twitches.

Me too. It's horrible, and I really dislike the idea. I can't imagine 
that Microsoft don't have a better solution to this.

I talked to some Microsoft people at an event yesterday, and they said 
that they just don't use completely isolated user accounts for services. 
Microsoft's services install into the three standard service access levels:
  LocalService  NetworkService  LocalSystem

as mentioned:
  http://msdn.microsoft.com/en-us/library/ms143504.aspx

http://msdn.microsoft.com/en-us/library/windows/desktop/ms686005(v=vs.85).aspx


... so maybe the answer is that we're trying to do it too UNIX-ish  (ie: 
securely) and we should by default use the NetworkService, allowing 
users to change the service account if they want to as an advanced feature.

Personally I think that'd be better than the current situation, which is 
not user friendly, and has a much lower squick-factor than storing 
passwords in the registry.

This'd also solve issues with other Pg installs; we just switch smoothly 
over to installing in NetworkService by default, giving users a radiobox 
to switch to "custom service user account" where the name "postgres" is 
prefilled.

--
Craig Ringer


POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/


Re: Minimising windows installer password confusion

From
Craig Ringer
Date:
On 06/13/2012 05:14 PM, Dave Page wrote:
> On Wed, Jun 13, 2012 at 2:18 AM, Craig Ringer
> <craig@postnewspapers.com.au> wrote:
>> On 06/12/2012 08:08 PM, Dave Page wrote:
>>> Some background: By default the installer will use 'postgres' for both
>>> the service (OS) account, and the database superuser account. It will
>>> use the same password for both (though, users have complete control at
>>> the command line if they want it, which is why the account names are
>>> shown in the installer). Unlike *nix installations, the service
>>> account must have a password, which is required during both
>>> installation and upgrade to configure the PostgreSQL service. We
>>> therefore ask for the password during both installation and upgrade.
>>> If the user account exists (which can happen if there has previously
>>> been an installation of Postgres on the system), the user must specify
>>> the existing password for the account during installation (and of
>>> course, during all upgrades). This is where users normally get stuck,
>>> as they've forgotten the password they set in the past.
>> They may not even have set it, because the EnterpriseDB installer can be run
>> unattended and may've been used by some 3rd party package.
>>
>> I'd be interested in seeing what Microsoft installers that create isolated
>> user accounts do. I think .NET creates one for ASP, so that'd be one option
>> to look into.
> They tend to use the localsystem or networkservice accounts for most
> things, which don't have passwords. The reason we don't do that is
> that since the early days of 8.0 we've said we want to enable users to
> use the service account as if it were a regular account, so that they
> can do things like access network resources (useful for server-side
> copy for example).
>
> I wasn't overly convinced back then that that was necessary, and I'm
> still not now. I suppose we potentially could use the networkservice
> account by default, and let advanced users override that on the
> command line if they need to. Then remembering the password becomes
> their responsibility.
+1 from me on this, though I think making the service account part of 
the install process makes sense.

SERVICE ACCOUNT
-------------------------

You can probably just accept the default here.

PostgreSQL runs in the background as a network service in a user account 
that
only has limited access to the files and programs on the computer. This 
is fine
for most purposes, but will prevent certain operations like server-side 
COPY and
direct access by the server to resources on shared folders from working. 
If you
need these capabilities, we recommend that you create a "postgres" user 
account
below and have the PostgreSQL server run in that instead of the default
NetworkService account.

-- [service account] -----------------------
|                                          |
| [*] LocalService                         |
|                                          |
| [ ] Custom Service Account               |
|                                          |
| -- [custom account]------------------|   |
| |                                    |   |
| | Username: [                 ]      |   |
| | Password: [                 ]      |   |
| | Domain:   [ THISCOMPUTER    ]      |   |
| |                                    |   |
| |------------------------------------|   |
|------------------------------------------|

Reasonable option?

--
Craig Ringer



POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/


Re: Minimising windows installer password confusion

From
Craig Ringer
Date:
On 06/13/2012 05:18 PM, Dave Page wrote:
> On Wed, Jun 13, 2012 at 3:07 AM, Craig Ringer

>> Why "using the windows control panel" ?
>
> Because when I wrote the email I was looking for a simple solution
> that wouldn't require writing code that has potential to fail
> depending on how the users environment is configured (the user account
> stuff tends to go wrong in weird ways, for example when used on
> domains in unusual (or high security) configurations. We're spending a
> lot of effort at the moment getting the 9.2 buildfarm together, and
> updating all the StackBuilder add-on packages (think multiple
> man-months) - I'm trying not to add to that too much.

Ah, sorry. I'm *not* trying to say that any of this is stuff that EDB 
"should" just up and do. I have no say over what you do and how, I'm 
just trying to raise possible usability points that might be useful, 
either soon or to inform design of later releases.

--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/


Re: Minimising windows installer password confusion

From
Craig Ringer
Date:
On 06/13/2012 06:32 PM, Florian Pflug wrote:
> Some further googling indicates that, yes, the service account passwords
> are stored in the registry, but are only accessible to the LocalSystem
> account [2]. Querying them from the postgres installer thus isn't really an
> option. But what you could do, I guess, is to offer the user the ability to
> change the password, and using the approach from [1] to update the affected
> service definitions afterwards.

Yep, that fits with how MS SQL server does things:

"Always use SQL Server tools such as SQL Server Configuration Manager to 
change the account used by the SQL Server Database Engine or SQL Server 
Agent services, or to change the password for the account. In addition 
to changing the account name, SQL Server Configuration Manager performs 
additional configuration such as updating the Windows local security 
store which protects the service master key for the Database Engine. 
Other tools such as the Windows Services Control Manager can change the 
account name but do not change all the required settings."

http://msdn.microsoft.com/en-us/library/ms143504.aspx

--
Craig Ringer



POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/


Re: Minimising windows installer password confusion

From
Dave Page
Date:
On Thu, Jun 14, 2012 at 12:55 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> On 06/13/2012 05:14 PM, Dave Page wrote:
>>
>> On Wed, Jun 13, 2012 at 2:18 AM, Craig Ringer
>> <craig@postnewspapers.com.au> wrote:
>>>
>>> On 06/12/2012 08:08 PM, Dave Page wrote:
>>>>
>>>> Some background: By default the installer will use 'postgres' for both
>>>> the service (OS) account, and the database superuser account. It will
>>>> use the same password for both (though, users have complete control at
>>>> the command line if they want it, which is why the account names are
>>>> shown in the installer). Unlike *nix installations, the service
>>>> account must have a password, which is required during both
>>>> installation and upgrade to configure the PostgreSQL service. We
>>>> therefore ask for the password during both installation and upgrade.
>>>> If the user account exists (which can happen if there has previously
>>>> been an installation of Postgres on the system), the user must specify
>>>> the existing password for the account during installation (and of
>>>> course, during all upgrades). This is where users normally get stuck,
>>>> as they've forgotten the password they set in the past.
>>>
>>> They may not even have set it, because the EnterpriseDB installer can be
>>> run
>>> unattended and may've been used by some 3rd party package.
>>>
>>> I'd be interested in seeing what Microsoft installers that create
>>> isolated
>>> user accounts do. I think .NET creates one for ASP, so that'd be one
>>> option
>>> to look into.
>>
>> They tend to use the localsystem or networkservice accounts for most
>> things, which don't have passwords. The reason we don't do that is
>> that since the early days of 8.0 we've said we want to enable users to
>> use the service account as if it were a regular account, so that they
>> can do things like access network resources (useful for server-side
>> copy for example).
>>
>> I wasn't overly convinced back then that that was necessary, and I'm
>> still not now. I suppose we potentially could use the networkservice
>> account by default, and let advanced users override that on the
>> command line if they need to. Then remembering the password becomes
>> their responsibility.
>
> +1 from me on this, though I think making the service account part of the
> install process makes sense.
>
> SERVICE ACCOUNT
> -------------------------
>
> You can probably just accept the default here.
>
> PostgreSQL runs in the background as a network service in a user account
> that
> only has limited access to the files and programs on the computer. This is
> fine
> for most purposes, but will prevent certain operations like server-side COPY
> and
> direct access by the server to resources on shared folders from working. If
> you
> need these capabilities, we recommend that you create a "postgres" user
> account
> below and have the PostgreSQL server run in that instead of the default
> NetworkService account.
>
> -- [service account] -----------------------
> |                                          |
> | [*] LocalService                         |
> |                                          |
> | [ ] Custom Service Account               |
> |                                          |
> | -- [custom account]------------------|   |
> | |                                    |   |
> | | Username: [                 ]      |   |
> | | Password: [                 ]      |   |
> | | Domain:   [ THISCOMPUTER    ]      |   |
> | |                                    |   |
> | |------------------------------------|   |
> |------------------------------------------|
>
> Reasonable option?

Too complicated - it'll confuse users too (and it goes against the
primary goal of the installers which is to setup the server in a
suitable way for production use, with the absolute minimum of user
interaction). We try to provide more complex config options that 99%
of users won't need through the command line only (though, in the
future I guess it might make sense to have a "Show advanced options"
checkbox on the first page, though that's definitely out of scope for
9.2).

I'll have a play with it and see if a simple switch to NetworkService
seems feasible.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Minimising windows installer password confusion

From
Dave Page
Date:
On Thu, Jun 14, 2012 at 11:43 AM, Dave Page <dpage@pgadmin.org> wrote:
>
> I'll have a play with it and see if a simple switch to NetworkService
> seems feasible.

OK, I worked up a patch which uses "NT AUTHORITY\NetworkService" as
the service account by default. This doesn't need a password, so
allows us to simply prompt during installation for the superuser
password for the cluster, and not at all during upgrade. If you run
the installer from the command line with "--serviceaccount postgres"
(or some other account name), you get the current behaviour.

I've posted it on our internal ReviewBoard system for the rest of the
team to review and test on various platforms (I've only tried it on XP
so far). Now would be a very good time for people to yelp if they
think this is a bad idea (the only downside I can see if accessing
files for server-side copy etc, but users that want to do that can
install with a non-default account). If we go ahead, we'll include it
in 9.2.

Thanks for the feedback folks.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Minimising windows installer password confusion

From
Robert Haas
Date:
On Thu, Jun 14, 2012 at 11:59 AM, Dave Page <dpage@pgadmin.org> wrote:
> On Thu, Jun 14, 2012 at 11:43 AM, Dave Page <dpage@pgadmin.org> wrote:
>>
>> I'll have a play with it and see if a simple switch to NetworkService
>> seems feasible.
>
> OK, I worked up a patch which uses "NT AUTHORITY\NetworkService" as
> the service account by default. This doesn't need a password, so
> allows us to simply prompt during installation for the superuser
> password for the cluster, and not at all during upgrade. If you run
> the installer from the command line with "--serviceaccount postgres"
> (or some other account name), you get the current behaviour.
>
> I've posted it on our internal ReviewBoard system for the rest of the
> team to review and test on various platforms (I've only tried it on XP
> so far). Now would be a very good time for people to yelp if they
> think this is a bad idea (the only downside I can see if accessing
> files for server-side copy etc, but users that want to do that can
> install with a non-default account). If we go ahead, we'll include it
> in 9.2.

What happens if they try to use this to upgrade from the EDB 9.1 installers?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Minimising windows installer password confusion

From
Dave Page
Date:
On Thu, Jun 14, 2012 at 5:38 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Thu, Jun 14, 2012 at 11:59 AM, Dave Page <dpage@pgadmin.org> wrote:
>> On Thu, Jun 14, 2012 at 11:43 AM, Dave Page <dpage@pgadmin.org> wrote:
>>>
>>> I'll have a play with it and see if a simple switch to NetworkService
>>> seems feasible.
>>
>> OK, I worked up a patch which uses "NT AUTHORITY\NetworkService" as
>> the service account by default. This doesn't need a password, so
>> allows us to simply prompt during installation for the superuser
>> password for the cluster, and not at all during upgrade. If you run
>> the installer from the command line with "--serviceaccount postgres"
>> (or some other account name), you get the current behaviour.
>>
>> I've posted it on our internal ReviewBoard system for the rest of the
>> team to review and test on various platforms (I've only tried it on XP
>> so far). Now would be a very good time for people to yelp if they
>> think this is a bad idea (the only downside I can see if accessing
>> files for server-side copy etc, but users that want to do that can
>> install with a non-default account). If we go ahead, we'll include it
>> in 9.2.
>
> What happens if they try to use this to upgrade from the EDB 9.1 installers?

The installers don't support major version upgrades - it'll install
alongside 9.1.

If someone has an existing beta installation of 9.2, it'll use the
service account that was installed with, just as past minor-version
upgrades would (including asking for the password).

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Minimising windows installer password confusion

From
Craig Ringer
Date:
On 06/14/2012 11:59 PM, Dave Page wrote:
> On Thu, Jun 14, 2012 at 11:43 AM, Dave Page <dpage@pgadmin.org> wrote:
>> I'll have a play with it and see if a simple switch to NetworkService
>> seems feasible.
> OK, I worked up a patch which uses "NT AUTHORITY\NetworkService" as
> the service account by default. This doesn't need a password, so
> allows us to simply prompt during installation for the superuser
> password for the cluster, and not at all during upgrade. If you run
> the installer from the command line with "--serviceaccount postgres"
> (or some other account name), you get the current behaviour.
>
> I've posted it on our internal ReviewBoard system for the rest of the
> team to review and test on various platforms (I've only tried it on XP
> so far).
Cool. Feel free to lob me a link if you want, I have several unimportant 
systems I can test it on too.

--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/