Install PostgreSQL 9.6 on Ubuntu 16.04 and reset postgres password

Posted on by

PostgreSQL is really easy to install on Ubuntu 16.04 LTS. This example uses command line to install PostgreSQL and vim to edit files. These settings are for non-production use.

Set source to get PostgreSQL

Set up the right source so that we can install PostgreSQL with apt-get.

  • Enter sudo vim /etc/apt/sources.list.d/pgdg.list
  • Type i to go to INSERT mode in vim
  • Enter deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main
  • Press ESC
  • Type :wq to save and exit vim

Reference: https://www.postgresql.org/download/linux/ubuntu/

Update package information

On command line, type sudo apt-get update

Install PostgreSQL 9.6

On command line, type sudo apt-get install postgresql -y

Try logging in to PostgreSQL

Type psql -U postgres

You are likely to see Peer authentication failed

Type psql

You are likely to see psql: FATAL:  role “<username>” does not exist

Let’s fix these errors.

Fix psql: FATAL:  role “<username>” does not exist

PostgreSQL uses /etc/postgresql/9.6/main/pg_hba.conf file to run some checks to determine how to allow access. pg_hba.conf’s name stands for PostgreSQL (pg) Host Based Authentication (hba) configuration (conf) file. By default, at the time of this writing, pg_hba.conf’s first uncommented line is

local   all             postgres                                peer

With peer authentication PostgreSQL asks Linux the currently logged in user who is running psql. In my case, that username is zedfoxus. So, psql tries to log zedfoxus into PostgreSQL database, but there is no such user/role in the database. Therefore, this error appears. This is an over-simplified example for general understanding.

To overcome this error:

  • Type sudo su – postgres
  • psql

This will now allow us access to the database. In the first command we switched the user to postgres and then ran psql. PostgreSQL used postgres username to log in. Since postgres IS a user in PostgreSQL, we were able to access the database.

sudo su –  postgres can quickly become an inconvenience. If user zedfoxus wants to log on to PostgreSQL as postgres user, what do we need to do? Read on.

Fix psql: FATAL:  Peer authentication failed for user “postgres”

The error is happening because PostgreSQL is trying to do peer authentication by default, as specified by its pg_hba.conf file. Linux tells PostgreSQL my currently logged in user (which is zedfoxus) and that’s not postgres user I asked psql to log in with when I did psql -U postgres. That leads to PostgresSQL throwing this error. This is also an over-simplified example for general understanding.

To overcome this error, we will need to change pg_hba.conf file.

  • Type sudo vim /etc/postgresql/9.6/main/pg_hba.conf
  • Type i to go to INSERT mode in vim
  • Scroll down to find the following line. Change it from
    local   all             postgres                                peer

    to

    local   all             postgres                                trust
  • Press ESC
  • :wq to save and exit vim
  • Type sudo service postgresql restart
  • Then, type psql -U postgres

This will now allow us access to the database using postgres user from any Linux user.

Reset postgres password

Setting postgres user’s password is really easy. ALTER ROLE statement will be used.

  • If not already in the PostgreSQL, type psql -U postgres
  • Type ALTER ROLE postgres WITH PASSWORD ‘test’;
  • Type \q to exit PostgreSQL

Allow PostgreSQL access from non-local/remote systems

Current settings of /etc/postgresql/9.6/main/postgresql.conf allows local connections. In order for systems other than local system to access PostgreSQL, certain changes are needed. Assumption is made that you can edit files with vim. If not, feel free to edit files with a text editor most comfortable to you.

  • Type sudo vim /etc/postgresql/9.6/main/pg_hba.conf
  • Comment the line below and add another line above it
    host    all             all             127.0.0.1/32            md5

    so that the lines look like this:

    host    all             all             0.0.0.0/0               md5
#host    all             all             127.0.0.1/32            md5
  • Save the file and exit
  • Type sudo vim /etc/postgresql/9.6/main/postgresql.conf
  • Change the line below
    #listen_addresses = 'localhost'     # what IP address(es) to listen on;

    to

    listen_addresses = '*'     # what IP address(es) to listen on;
  • Save the file and exit
  • Type sudo service postgresql restart

This will now allow us access to the database from any user you are logged.

From another system, type psql -h <IP or hostname of PostgreSQL server> -U postgres. When asked for a password, type test.

Allow password-less access to PostgreSQL

In general testing, if the desire is to log on to PostgreSQL without requiring a password change this line in /etc/postgresql/9.6/main/pg_hba.conf from

host    all             all             0.0.0.0/0               md5

to

host    all             all             0.0.0.0/0               trust

Then type sudo service postgresql restart.

From another system, type psql -h <IP or hostname of PostgreSQL server> -U postgres. You should be logged into PostgreSQL without requiring a password.

If you’d like to do connection pooling with PgBouncer, see Configure PgBouncer to authenticate against PostgreSQL and rely less on userlist.txt