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.
In the output list, you see the PostgreSQL user.
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.
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;
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.
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.
Our Sales and Support teams are available 24 hours by phone or e-mail to assist.
Onboard to Liquid Web: A Step-By-Step Guide to Migrating Your Data to a Virtual ServerRead Article