What is the Default Password for PostgreSQL?

To answer what is the default password for the PostgreSQL user, there isn’t one. When connecting to PostgreSQL on Linux for the first time, many admins have this question, especially if they are from the MySQL world. One important task is how to access PostgreSQL. This tutorial walks you through setting up the default user for PostgreSQL and connecting to the service.

Viewing the Default PostgreSQL User

When PostgreSQL is installed, a Postgres user is also added by default.

View the PostgreSQL user in the /etc/passwd file by running the command.

cat /etc/passwd

In the output list, you see the PostgreSQL user.

postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash

Viewing the pg_hba.conf file, the default authentication mode for PostgreSQL is ident. The ident authentication method takes the OS username you’re operating as and compares it with the allowed database usernames. This means that in order to connect to PostgreSQL you must be logged in as the correct OS user. Fortunately, there is optional username mapping.

Access the pg_hba.conf file with the following command.

cat /var/lib/pgsql/9.3/data/pg_hba.conf

Here is the output where we see the indent authentication mode.

# IPv4 local connections:
host    all              all             127.0.0.1/32             ident
# IPv6 local connections:
host    all              all             ::1/128                  ident

Becoming the Default User in PostgreSQL

In this tutorial, the root user is logged into the server but is not an allowed database user. Trying to connect to PostgreSQL yields the following error.

psql
psql: FATAL: role "root" does not exist

However, becoming the default PostgreSQL user and then attempting a connection to PostgreSQL provides a valid response.

su - postgres
psql
psql (9.3.9)
Type "help" for help.
postgres=#

Create a PostgreSQL User and Password

You can create a PostgreSQL user with a password using the following command. Replace xxx with your desired username and yyy with your password.

postgres=# CREATE USER xxx PASSWORD yyy;

User Configuration

To grant the user privileges on a new database schema, such as creating tables, use the following command. Replace test with the desired schema name.

postgres=# GRANT ALL ON SCHEMA test TO xxx;

Use the following command to grant the user the ability to insert data to the tables in the new schema.

postgres=# GRANT ALL ON ALL TABLES IN SCHEMA test TO xxx;

To disconnect from PostgreSQL in the terminal, use this command.

postgres=# \q

Wrapping Up

Connecting to PostgreSQL the right way the first time saves valuable time. Once your user has the appropriate credentials and permissions, they can move on to their administrative tasks.

Is your lack of resources slowing down your PostgreSQL instance? Liquid Web’s Dedicated Servers are the solution. They outmatch the competition on performance and support. Check out how our Dedicated Servers can skyrocket your site’s performance, and contact our sales team to get started.