By far the most common issues we see reported with the "one-click"
PostgreSQL installers that we build here at
EnterpriseDB are password related. In this post I'll explain what the passwords are, why we need them, and how to reset them.
Superuser Password
The Superuser Password is used by Postgres to secure the built-in "postgres" superuser account in the database itself. This is the only account found in a fresh installation. The password is setup during the initial installation of the database server, and may be changed at any point in the future using
pgAdmin, or by issuing an SQL query such as:
ALTER USER postgres WITH PASSWORD 'VeryVerySecret';
The superuser password is not required to upgrade Postgres to a newer version, however it will be required if you install certain StackBuilder modules, such as PostGIS or any of the PHP applications that are available. The password is required for these installers because they connect to the database server and create databases and other objects required to run the software.
Service Password
The service password is the one that tends to confuse people. Postgres runs under a special operating system user account for security reasons. This account is created on your machine when the installer runs, and unless overridden on the command line, it will be called "postgres". On Unix-like operating systems such as Linux and Mac OS X, the account is setup without a password and users generally never need to worry about it again.
On Windows however, things are slightly different. In a normal installation of Postgres we'll setup the service account on Windows to use the same password that we use for the superuser account (expert users can override this behaviour using command line options for the installer). We need to do this because Windows requires that service accounts have secured passwords.
Note: If you change the superuser password in the future, that does not change the service password as well.
When you upgrade Postgres on Windows, in order to re-install the service we need to know the service password which is why the installer will require that it be re-entered correctly.
If you have forgotten the password, you can reset it on the command line (there are GUI tools that can be used as well, but they are not available on all versions of Windows). Start a command prompt, and then enter a command like:
net user postgres *
Update from Craig Ringer (19/11/2012): Note that this command must be run in a command prompt with Administrative rights. Remember, under UAC, even if you have local admin access you have to explicitly run programs with admin rights. Shift-right-click on "Command Prompt" in the Start menu and choose "Run as administrator" from the context menu.
You will be prompted to enter and confirm a new password for the user. If you do this, you must then also update the password in the service configuration for any Postgres or related services (such as pgAgent or pgBouncer) that may use the account, or expect them to fail to start at the next reboot. You can do this using the Services management console which can be found in the Administrative Tools folder. Just right-click each service, select Properties, and enter the new password and click OK.
We get a lot of people asking us to reset their service password, as they mistakenly think it's related to either their Postgres Community Login, or their account on the EnterpriseDB website. It's not - and we cannot change it for you!
Uninstallation
If you uninstall Postgres, the service account will not be removed from the operating system. This is because each individual uninstaller has no way of knowing if you are using the service account with other packages or installations. This isn't a huge problem unless you come to reinstall Postgres at a later time and realise you've completely forgotten what the password was set to - in which case you can reset it as shown above. If you prefer to remove the account, you can use a command like:
net user postgres /delete
Update from Craig Ringer (19/11/2012): Note that this command must be run in a command prompt with Administrative rights. Remember, under UAC, even if you have local admin access you have to explicitly run programs with admin rights. Shift-right-click on "Command Prompt" in the Start menu and choose "Run as administrator" from the context menu.
Summary
So to recap, we have Superuser passwords which are used to secure the database server's superuser account, and on Windows a service password to secure the operating system service account used by Postgres. Accounts use the same username (postgres) and the same password in a normal installation, but either password can be changed independently of the other post-installation. Both passwords are specific to your own machine, and can be changed (or the service account deleted) as shown.
For a walkthrough of the installation process, please see
this guide.
If you need any further assistance having read this post, please use the
pgsql-general@postgresql.org mailing list, or the
EnterpriseDB Forums.
