On 09/06/10 16:29, Dave Page wrote: > On Wed, Jun 9, 2010 at 8:56 AM, Craig Ringer > <cr...@postnewspapers.com.au> wrote: > >> Only because the PostgreSQL system user account password is coupled to >> the account of the "postgres" user in the PostgreSQL database cluster >> (right?). I'm not at a Windows box right now so I can't test to see if >> altering the Pg role's password changes the system password or vice >> versa, but I'd be surprised if they did. > > It won't, but the point is that we have to ask for a password anyway > so we don't gain anything by not asking the user for one of them.
The issue is that the user has to know if they've installed Pg before (and thus already have a "postgres" service account) or not. Some clearly don't, or have forgotten the service account password long ago. In general, users expect that an uninstall of an app will remove the app's configuration. Pg ... sort of ... does. It leaves the user account in place, though it created it in the first place. So the user has to know if Pg has been installed previously and if so what the service account password was. This demonstrably confuses people who try to uninstall and reinstall Pg to resolve issues (service account configuration related or other). As you've pointed out good reasons why just storing a generated password may not be a good idea, let me propose another approach that might help users with what's clearly a confusing point for many: - During the install, test for the existence of a 'postgres' user account before displaying the password prompt panel of the installer "wizard". - If such an account does not exist, display the existing UI password prompt ui with simplified language indicating that you're creating a new password not entering one already set. People seem to struggle with this at present. - If such an account already exists, tell the user that PostgreSQL has been installed in the past or another version is currently installed, so they must provide the password that was supplied during that prior installation. If they do not know the password, they may press an offered "reset password" button to enter a new one, but they're warned that doing so will cause any other versions of PostgreSQL to stop working. ------ or possibly ..... -------- - When the postgres user password is reset on the PostgreSQL service via the Pg installer, update or offer to update *all* service registrations for any service using the postgres account so that they use the same password, thus avoiding breaking old versions when the user has forgotten the service account password. >> Personally I'm firmly of the opinion that the user should never need >> to know anything about the password (if any) for the "postgres" >> Windows user account that's used for the service account. > > So how would you install something like pgAgent, which you would most > likely want to run under the same account? Installers run as admin; it'd read the registry key during installation and use it for the CreateService call. > You can have multiple administrators on a machine, and storage of a > plain text password in the registry would allow knowledge of that > password to leak from one administrator to another, which may cause > security concerns in a tightly controlled environment. As far as I'm > aware, Windows doesn't provide any way for an Administrator to read > any other local/domain passwords in anything other than an > encrypted/hashed form, so this would be a new issue, not normally > seen. True, and a good point, particularly on domain setups. OTOH, if it's a generated password, leaking it has little effect as it's unique to the machine and only grants access to a service account that the admin user already has access to by virtue of their admin status on that machine. It's not re-used anywhere and if you can read it you can change it or become that user. Nonetheless, I'll grant that I understand and agree with your caution about that notion. I don't like it much either, I just don't like the way things work right now any more, as the questions and posts here suggest that it causes plenty of confusion for users. > Most other services use one of the 'special' accounts like 'Network > Service', however doing that with Postgres doesn't necessarily play > well with features like COPY, which is why we've avoiding doing that > since 8.0. That much makes perfect sense and I certainly wasn't arguing that it should live in the generic service account. There are clear and good reasons to run Pg in its own service account, well beyond merely separating it from other services. A bit of digging reveals that most decent services are using local or domain accounts, with passwords, and are just living with the administrator hassle this creates, expecting admins to be competent enough to change and maintain service passwords. I'm not sure Pg can rely on that for desktop installs, though, so it might need to offer a way to take service account password management into its own hands. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs