Introduction

PostgreSQL is a widely used open-source Relational Database Management System(RDBMS) in web application development. If you have decided to use PostgreSQL as a database, there are a couple of things you should know about PostgreSQL. Before jumping straight into writing SQL queries, it is also very essential to first get familiar with the software itself so that you can use its core features and make the system more secure, and robust.

There are a couple of things that seem confusing to individuals who are just starting with PostgreSQL - User, Role and Authentication related concepts are some of them. Hopefully, after reading this article you'll get some insights about them.

Note:

I am using Ubuntu 22.04 LTS, and PostgreSQL 12, however, these concepts should be the same for any OS or PostgreSQL version.

What happens when you install PostgreSQL?

The first thing you should know about PostgreSQL at the time of installation is that you are installing software and not a database. It manages databases and access to databases in a very secure environment.

When you first install Postgres, it creates a new OS user called postgres with superuser privileges, why? Because when you run the psql command to go to the Postgres server shell, it defaults to opening the shell under a username that matches the current OS users and opens a database by the same name. You can run the below command to see all the users in Ubuntu

gaurav@gaurav-TravelMate:~$ sudo cat /etc/passwd

This command will list all the users of your system. You can see postgres on of them.

this user is created so that you can log in to PostgreSQL. But, why it is required at all? The answer is 'default authentication of PostgreSQL'. PostgreSQL, by default, it uses Ident Authentication which means that when you try to log in to PostgreSQL, it will try to authenticate you as your currently active OS user.

For example, you are logged in on your laptop with username gaurav and if you try to login into the PostgreSQL server shell, an error will occur.

gaurav@gaurav-TravelMate:~$ psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "gaurav" does not exist

What's wrong?

Here I am logged in as gaurav user, hence, PostgreSQL tries to authenticate as gaurav, but this user does not exist in the PostgreSQL system hence error occurred.

Let's try to log in as postgres user, which was created by PostgreSQL itself.

gaurav@gaurav-TravelMate:~$ sudo su postgres  # logined as gaurav
[sudo] password for gaurav: 
postgres@gaurav-TravelMate:/home/gaurav$ # logged in as postgres
postgres@gaurav-TravelMate:/home/gaurav$ cd ~ # optional
postgres@gaurav-TravelMate:~$ psql # log in to psql
psql (12.13 (Ubuntu 12.13-1.pgdg22.04+1))
Type "help" for help.

postgres=#

as you can see, we logged in successfully to the PostgreSQL server.

Tip #1:

instead of executing two commands, we can use one command to log in to the PostgreSQL server.

gaurav@gaurav-TravelMate:~$ sudo -u postgres psql
could not change directory to "/home/gaurav": Permission denied
psql (12.13 (Ubuntu 12.13-1.pgdg22.04+1))
Type "help" for help.

postgres=#

In this command, we are telling the kernel that we want to execute the command psql as postgres user, hence authenticated successfully.

One more thing you should know is that in postgres=# postgres is the name of the database (not the user), and # indicates that the current user has superuser privileges.

Tip #2: To see the currently active user in PostgreSQL, run the below query

postgres=# SELECT current_user;
 current_user 
--------------
 postgres
(1 row)

postgres=#

We are logged in as postgres user to postgres database.

Tip #3: If you do not specify the name of the database, it will try to log you into the database having the same name as the username you want to log in with. Hence, in case there is no database with the same name as your user, it will throw an error.

Example:

sudo -u postgres psql this command asks the PostgreSQL server to authorize a user with the name 'postgres' to a database named 'postgres'

How to log in to PostgreSQL?

There are several ways to log in, three of them that you should know besides Ident authentication are peer authentication, and trust authentication.

The first and easiest way to log in is using postgres default user. However, there are some other ways as well,

psql -U [database_user] -d [databasename]

Where,

-U: user

-d: name of the database you want to log into

Example:

Suppose, you want to login as test_user to a database named postgres then the command may look like

gaurav@gaurav-TravelMate:~$ psql -U test_user -d postgres
psql (12.13 (Ubuntu 12.13-1.pgdg22.04+1))
Type "help" for help.

postgres=>

Note:

There is no password set for test_user that's why a password argument was not required. Also, I have modified the pg_hba.conf file so that for local Unix socket connection, I am using trust as an authentication mechanism, which is another way for authentication.

In case you are interested, here is what I have changed.

Role and User

What if I tell you users are roles in PostgreSQL?

Well, there is a command in psql called \du, it will list all the users and their assigned roles. Let's run it.

As the name suggests, \du the command essentially means to list out all the users, but what we are seeing here are only roles, where are users then?

Actually, in PostgreSQL, there is no such thing as users. Users are also roles. But what makes a role different from a user is that role cannot log in but a user can log in to a database. However, with a single command, we can create a role or user. If you assign login permission to any role, it becomes a user and in the same way, if you remove the login permission from a user, it becomes a role.

Let's see how we can create a new role and a new user.

postgres=# CREATE ROLE test_role;
CREATE ROLE
postgres=# 
postgres=# CREATE USER test_user;
CREATE ROLE   -- message says ROLE was created though we created USER
postgres=#

As you can see, it does not matter if you use CREATE USER command or CREATE ROLE command, PostgreSQL will create a role in both cases, but with only one different permission. To know the difference, let's execute the \du command to list current users.

As we can see that the only difference is, roles haven't permission to log in but users have.

So, you may be thinking that if the user and role are almost the same then where are there different commands for user creation and role creation?

Well, they can be created with one single command

• We can create a new user using the CREATE ROLEcommand

• Also, we can create a new role using CREATE USER command, as shown below

let's try to login as test_user and test_role:

All good, successfully logged in as test_user, now let's try with test_role

as expected, test_role is not permitted to log in.

Now, let's take a moment and give login permission to test_role and remove login permission from test_user and execute the same commands again and see what happens:

Let's try to log in one more time with test_user and test_role credentials.

You can verify the permissions by executing \du command from the psql console as shown below:

as you can be a user and the role can be treated like the same, and can be switched easily.

That's all for this tutorial, see you in the next one. Keep Learning, Ta-Ta!!