postgresqlubuntu

How do I set the password for the user 'postgres' when I can't access the PostgreSQL database?

I'm running PostgreSQL 10.12 on Ubuntu 18.04.

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

PostgreSQL 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 reset the superuser password, which works on all fresh installations of PostgreSQL on Linux.

    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. Reset password of postgres user.

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

      To hide the secret on screen, use interactive \password command instead:

       (in postgres psql) \password postgres

    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, kubectl exec, aws ssm or anything like that, if you have this kind of shell 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. Instead run psql as postgres user just once and use following commands to create a dedicated database & login for your app:

       CREATE USER myapp_admin PASSWORD 'secret';
 -- (to hide secret on screen, use \password myapp_admin instead)
 CREATE DATABASE myapp;
 ALTER DATABASE myapp OWNER TO myapp_admin;
 -- alternative if you want to keep default ownership:
 -- GRANT ALL ON DATABASE myapp TO myapp_admin;

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

    Note on Managed postgres solutions

    This answer applies only for self-managed PostgreSQL, where you have superuser shell access. It will not work for managed solutions like Aurora, CloudSQL or alike - use cloud provider tools to reset db passwords in that case.