By skyforbes If you encounter the following error while creating a database using postgres:
FATAL: Password authentication failed for user “user”; then you have come to the right place.
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.
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.
The name represents your user name which stands as a superuser with a password.
To override the password permanently, go to your pg_hba.conf file (you can find this in your data file inside the postgres folder)
change the peer and md5 to trust.
Next, on your command line enter : createuser name.
The name represents your user name which stands as a superuser with a password.At this point, you should be able to create a database without hitches.