Обсуждение: Minimising windows installer password confusion
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
Вложения
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
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/
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.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
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/
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
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
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
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:That's a good point.
> 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".Yeah - that'll require some additional code to check if the account
> 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.
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..
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/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/
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/
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
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
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
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
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: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/
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/
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
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
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
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
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/