How to Set up a Password as a Default User in PostgreSQL

PostgreSQL often called just Postgres, is an object-relational database management system with an emphasis on flexibility and standards compliance.

Before we begin, I feel it's crucial to explain that for almost all Unix distributions, the default Postgres user neither uses nor needs a password for authentication purposes. As an alternative, the default authentication operation is ident or peer, but it varies based on how Postgres was first installed on your machine and what version you are running.

Ident authentication works by using the OS' own identification server running at TCP port 113 to authenticate the user's login credentials. On the other hand, peer verification is only utilized for local connections and authentications that the logged in the username of the OS matches the username in the Postgres database.

Login as a default user

In almost all systems, the standard Postgres user is "postgres" and they do not need a password at all for verification. So, in order to add a password for default users in PostgreSQL, we need to first log in and connect as the "postgres" user.

If you are successful in your attempt to connect as "postgres" and are seeing the psql prompt, then the next step is to go down to the section that says "Changing the Password". You may get an error that says that the database "postgres" isn't there, and if that happens, try connecting to the template1 database instead and if it works, continue to Changing the Password.

Also, you may get an authentication error when trying to connect to the psqlclient, in that case, you might have to modify the Postgres authentication config file (pg_hfa.conf). Enter the config file (it is usually found in at /etc/postgresql/#.#/main/pg_hba.conf, where #.# is the current Postgres version you are using). The authentication configuration file is basically a list of authentication parameters.

You have to go down the file until you find the first line displaying the postgres user in the third column. Remove the semicolon if you have to, or add if the line is missing entirely, then add this line to the top of the file "local all postgres peer" and save the file. This simple modification will instruct Postgres that for any local connections established to and all databases for the user "postgres" (which is you, in case you don't remember), it must verify using the peer protocol.

Keep in mind that older versions of Postgres may favor the default verification method of ident, but for the current version, it will use peer as said above instead.

After you're done with your configuration file and it has been properly modified, repeat the steps I laid out at the beginning of the article to connect as the default postgres user. If it works you can go ahead with changing the password.

How to change the default password

If you have managed successfully establish a stable connection to Postgres at the psql prompt it's time to change the password. Use the ALTER USER command to modify the password. If it worked, Postgres will show a text of ALTER ROLE as seen above.
The confirmation text looks like this: "postgres=# ALTER USER postgres PASSWORD 'myPassword';ALTER ROLE"

Once you're done you can leave the psql client by using the \q command.