How to Login and Authenticate to PostgreSQL

1. Connecting to Database in PostgreSQL
2. Use createuser and createdb in PostgreSQL
3. Use the SQL Administration Commands and Connect With a Password Over TCP

This article discusses how to log in and be authenticated to access a particular PostgreSQL database.

Connecting to Database in PostgreSQL

When connecting to a PostgreSQL database, you should create the database you want to connect to, the host address of the server, the port number, and your username. The parameters can be provided using the following command-line arguments.

david@david-HP-ProBook-6470b:~$ psql -h localhost -U postgres -p 5432

There are default connection parameters that are used when an argument is omitted. If we omit the username and the database, the server uses the operating system’s username.

The database selected also has the same name as the operating system, and if these parameters do not exist, the following error might be encountered.

david@david-HP-ProBook-6470b:~$ psql
psql: error: FATAL:  role "david" does not exist

In the above example, the server attempts to access a database named david with the username david, which does not exist.

Use createuser and createdb in PostgreSQL

By default, the PostgreSQL server has a user with the username postgres and a database named postgres. Use the following command to create a database user with the same name as our operating system.

david@david-HP-ProBook-6470b:~$ sudo -u postgres createuser -s $USER

Next, create a database a database using the createdb command for the new user david.

david@david-HP-ProBook-6470b:~$ createdb employee_db

To verify that we can log in using PSQL now, we must specify the database we want to connect to.

david@david-HP-ProBook-6470b:~$ psql -d employee_db

Output:

psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.

employee_db=# 

Suppose you want your database to default to the operating system, i.e., david in my case. Use the createdb command without using the -d argument, which asks for a database name.

david@david-HP-ProBook-6470b:~$ createdb

If you try to login to the PostgreSQL server using PSQL without specifying any parameters, the default user and database used to login will be david and david.

david@david-HP-ProBook-6470b:~$ psql

Output:

psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.

david=# 

Use the SQL Administration Commands and Connect With a Password Over TCP

SQL has commands that we can leverage to create a user and their database in a PostgreSQL database. The following command is to log in to the PostgreSQL server as a superuser.

david@david-HP-ProBook-6470b:~$ sudo -u postgres psql postgres

Enter your operating system’s password, and press enter when a password is prompted.

[sudo] password for david: 

Output:

psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.

postgres=#

In the PSQL shell, create a user and a database and associate the user with the created database.

postgres=# CREATE ROLE john LOGIN PASSWORD 'john';
CREATE ROLE
postgres=# CREATE DATABASE products_db WITH OWNER = john;
CREATE DATABASE
postgres=# 

Finally, login using the PSQL command over TCP specifies the host, the database, the user, and the port number. The PostgreSQL server’s default port number is 5432.

The next command verifies that we can log in with our new user over TCP.

david@david-HP-ProBook-6470b:~$ psql -h localhost -d products_db -U john -p 5432

When a password is prompted, enter the password we set as john, the same name as the username, and press the enter button on your keyboard.

Password for user john: 

Output:

psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

products_db=>