[SOLVED] Postgresql: How do I set the password for the user 'postgres' when I can't access the postgresql database?

Issue

I'm running postgresql 10.12 on Ubuntu 18.04.

I'd like to experiment with a software package that uses postgres. This means I should figure out how to set up users, passwords and databases under postgres.

Postgres is running, but there's no way to log in to it.

I'm pretty sure there is a user called 'postgres'.

Logging in as this user without providing a password fails. Also, attempting to use the passwords 'postgres' or 'root' fail.

How do I change the password for the user 'postgres' without being able to access the database?

Solution

This is a newbie-level recipe to modify initial password, which works on all fresh installations of the postgresql package on Linux Debian/Ubuntu and derivatives.

1. Go to the shell and switch user to postgres

    (in user shell) sudo su - postgres
   

2. connect to the postgres database as postgres user

    (in postgres shell) psql postgres postgres
   

3. now you can modify password of postgres user

    (in postgres psql) ALTER USER postgres PASSWORD 'newsecret';
   

4. quit psql

    (in postgres psql) \q
   

5. quit postgres shell

    (in postgres shell) exit
   

6. test connection with new password

    (in user shell) psql -h localhost postgres postgres
   

Note on remote postgres servers

In step 1 above, you can use ssh or kubectl exec or anything like that, if you have this kind of access.

Best Practice note

Above recipe (though it answers the OP question) is not a good practice. The best approach is:

1. Read and understand client auth -> https://www.postgresql.org/docs/current/client-authentication.html

2. Do not use postgres database user (or any other superuser!) for applications/development. Create your own user instead. For the simplest setup, use this:

    (in psql shell)
    CREATE USER myapp PASSWORD 'secret';
    CREATE DATABASE myapp OWNER myapp;
   

   This should be done instead of modifying postgres user and/or postgres database.

Answered By - filiprem

Answer Checked By - Marie Seifert (WPSolving Admin)