CODEWITHSUNDEEP
postgresqlubuntu
bob
bob

Reputation: 943

Postgresql: 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 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?

Upvotes: 6

Views: 42488

Answers (1)

filiprem
filiprem

Reputation: 7144

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. now you can reset 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, 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. Create your own user instead. For the simplest setup, use this:

     (in psql shell)
 CREATE USER myapp PASSWORD 'secret';
 CREATE DATABASE myapp;
 ALTER DATABASE myapp OWNER TO myapp;
 -- alternative if you want to keep default ownership:
 -- GRANT ALL ON DATABASE myapp TO myapp;

    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.

Upvotes: 30

Related Questions