Re: Minimising windows installer password confusion

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

On Tue, Jun 12, 2012 at 1:35 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)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

On Tue, Jun 12, 2012 at 2:48 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> On Tue, Jun 12, 2012 at 1:35 PM, Kevin Grittner
> <Kevin(dot)Grittner(at)wicourts(dot)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/

On Tue, Jun 12, 2012 at 1:49 PM, Magnus Hagander <magnus(at)hagander(dot)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

On Tue, Jun 12, 2012 at 8:53 AM, Dave Page <dpage(at)pgadmin(dot)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

On Tue, Jun 12, 2012 at 2:57 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Jun 12, 2012 at 8:53 AM, Dave Page <dpage(at)pgadmin(dot)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

On Tue, Jun 12, 2012 at 7:43 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:

> On Tue, Jun 12, 2012 at 2:57 PM, Robert Haas <robertmhaas(at)gmail(dot)com>
> wrote:
> > On Tue, Jun 12, 2012 at 8:53 AM, Dave Page <dpage(at)pgadmin(dot)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

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/

On 06/13/2012 01:19 AM, Sachin Srivastava wrote:
>
> On Tue, Jun 12, 2012 at 7:43 PM, Dave Page <dpage(at)pgadmin(dot)org
> <mailto:dpage(at)pgadmin(dot)org>> wrote:
>
> On Tue, Jun 12, 2012 at 2:57 PM, Robert Haas <robertmhaas(at)gmail(dot)com
> <mailto:robertmhaas(at)gmail(dot)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/

On Wed, Jun 13, 2012 at 2:12 AM, Craig Ringer
<craig(at)postnewspapers(dot)com(dot)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

On Wed, Jun 13, 2012 at 3:07 AM, Craig Ringer
<craig(at)postnewspapers(dot)com(dot)au> wrote:
> On 06/13/2012 01:19 AM, Sachin Srivastava wrote:
>>
>>
>> On Tue, Jun 12, 2012 at 7:43 PM, Dave Page <dpage(at)pgadmin(dot)org
>> <mailto:dpage(at)pgadmin(dot)org>> wrote:
>>
>>    On Tue, Jun 12, 2012 at 2:57 PM, Robert Haas <robertmhaas(at)gmail(dot)com
>>    <mailto:robertmhaas(at)gmail(dot)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

On Jun13, 2012, at 11:10 , Dave Page wrote:
> On Wed, Jun 13, 2012 at 2:12 AM, Craig Ringer
> <craig(at)postnewspapers(dot)com(dot)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

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/

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/

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/