PostgreSQL - Setting up - Linux Ubuntu

Submitted by Mi-K on Saturday, January 7, 2023 - 2:22pm

PostgreSQL is one of the most known and used SQL server.

We’re going to see how to install it on a Linux opearting system, to be precise on Linux Ubuntu.

First of all

For this tutorial, our Linux distribution will be Ubuntu 22.04: https://ubuntu.com/

Then PostgreSQL 14.5: https://www.postgresql.org/

As a bonus we'll use pgAdmin 4 v6.15 to manage PostgreSQL as a GUI: https://www.pgadmin.org/

Let's get started

To install PostgreSQL on Ubuntu, just follow the documentation.

And after the installation, let's install postgresql-contrib package in order to get useful tools:

$ sudo apt install postgresql postgresql-contrib

If you had already PostgreSQL installed it will suggest you to upgrade to the new version.

Say Y or Yes.

To start the PostgreSQL server do the following:

$ sudo systemctl start postgresql.service

Your Server is now running.

We are now going to enter in the SQL mode directly from the terminal.

For the moment, on this server, there is only 1 user and only 1 database.

This user is postgres and the database is also postgres.

Be careful because the user and the database are 2 different concepts but here with the same name.

It could seem obvious but it’s not.

So to enter in the PostgreSQL mode let’s do the following:

$ sudo -i -u postgres

You are now connected as user postgres on your Linux session:

postgres@badprog:~$

To enter in the postgres database you have to do the following:

postgres@badprog:~$ psql

You should see something like this:

postgres@badprog:~$ psql

psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1))

Type "help" for help.


postgres=#

You are now connected to the postgres database as postgres user.

To verify the users list, try the \du command like this:

postgres=# \du

You should see:

   List of roles

 Role name |                         Attributes                         | Member of 

-----------+------------------------------------------------------------+-----------

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Then let’s create a database with the following PostgreSQL query:

postgres=# CREATE DATABASE data1;

The CREATE DATABASE line should appear just below your query.

It means that our request has been taken into account and the server has executed your query correctly.

Of course do not forget the semicolon ";" at the end of your SQL request because it’s indeed a real PostgreSQL request.

To see if this new database has been added, let’s check it with the PSQL command \l with a "l" as in "light":

postgres=# \l

You should see something like this:

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   

-----------+----------+----------+-------------+-------------+-----------------------

 data1     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

(4 rows)

As you can see this new data1 database has been created.

Modifying the pg_hba.conf file

Let’s continue our psql journey by modifying the pg_hba.conf file:

$ /etc/postgresql/14/main/pg_hba.conf

It’s a text file so let’s modify it.

At the end of this file, replace the corresponding data with the following:

# DO NOT DISABLE!

# If you change this first entry you will need to make sure that the

# database superuser can access the database using some other method.

# Noninteractive access to all databases is required during automatic

# maintenance (custom daily cronjobs, replication, and similar tasks).

#

# Database administrative login by Unix domain socket

local   all             postgres                                scram-sha-256


# TYPE  DATABASE        USER            ADDRESS                 METHOD


# "local" is for Unix domain socket connections only

local   all             all                                     scram-sha-256

# IPv4 local connections:

host    all             all             127.0.0.1/32            scram-sha-256

# IPv6 local connections:

host    all             all             ::1/128                 scram-sha-256

# Allow replication connections from localhost, by a user with the

# replication privilege.

local   replication     all                                     peer

host    replication     all             127.0.0.1/32            scram-sha-256

host    replication     all             ::1/128                 scram-sha-256

We essentially modify the first line by changing the connection from peer to scram-sha-256:

Indeed with the peer connection you are only able to connect to a database with your Linux session.

So as we want to create many users and interect with them, we set a password instead in order to connect to the PostgreSQL server.

Close this file.

Then let’s restart our PostgreSQL server:

$ sudo systemctl start postgresql.service

Connecting with a specific user (-U option) to set a password

Okay, let’s back to the psql connection from our terminal by reconnecting to the database postgres as the postgres user:

$ psql -U postgres

You are now connected to the postgres database as postgres user.

Indeed with the -U option, from the psql tool, it's possible to select the user.

But, if we don't pass the database we want to connect to, psql tries to connect to the database with the same name as the user (in our case postgres).

We'll see later in this tutorial how to connect to a different database (with the -d option).

We want now create a password for the postgres user.

Type the following:

postgres=# \password

Enter new password for user "postgres": 

Enter it again:

You have now a password for the user postgres.

It’s important to have a password because the history from your psql prompt is accessible each time you enter as postgres user.

You can also see this history from this file:

$ /var/lib/postgresql/.psql_history

So to avoid someone to access this history, entering a password can prevent this situation.

Note that this password is exclusively for the psql connection, not for the sudo command.

By default the password for the postgres user is not accessible.

It’s a kind of automatic protection.

So now each time you want to connect to your psql server, you’ll have to use your password.

Let’s try it.

First, log out from your psql connection with CTRL + D or the command quit.

Then to log out from your postgres Linux user, you have to do again the CTRL + D key or the command exit.

Now you are back to the Linux prompt.

Let’s try to connect to the data1 database with the postgres user.

This time we'll use the -d option in order to specify the database.

$ psql -U postgres -d data1

Password for user postgres: 

psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1))

Type "help" for help.


data1=#

You are now connected to the data1 database as postgres user.

Creating a new user

Let’s now create a new user.

Still from the data1 database connection, we are going to create a new user with this PostgreSQL query:

data1=# CREATE ROLE “Player1” WITH ENCRYPTED PASSWORD ‘aNicePassword';

Be extremely careful when you create your user name because uppercase or lowercase matter when you use the double quotes.

This is indeed case sensitive.

But, when you don’t use any double quotes, the user name is saved in lowercase.

It means you can create the both users Player1 and player1 with double quotes.

But also another one pLaYeR1.

So in this case you’ll have 3 different users with 3 different name.

But, if you create the user without the double quotes, even with all letters in uppercase, the user name saved will be the same but with lowercase only.

In the same time to remove a role (a user) you have to explicitly write the user name surrounded with double quotes otherwise you'll remove the user in lowercase, so here also be careful.

Let's try it:

postgres=# DROP ROLE “Player1”;

Let’s verify with :

postgres=# \du

The user has been dropped.

To alter a role (a user) you have to use this command to for exemple grant the SUPERUSER specification:

postgres=# ALTER ROLE "Player1" WITH SUPERUSER;

If you check again with \du you should see the new attributes for the Player1 user:

                                  List of roles

 Role name |                         Attributes                         | Member of 

-----------+------------------------------------------------------------+-----------

 Player1   | Superuser                                                  | {}

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

To remove these attributes you have to use the opposite of the specification already set.

There is a documentation for that: https://www.postgresql.org/docs/current/sql-alterrole.html

So let’s remove the SUPERUSER specification from the Player1 user by using the NOSUPERUSER specification this time:

postgres=# ALTER ROLE "Player1" WITH NOSUPERUSER;

With \du it shows:

List of roles

 Role name |                         Attributes                         | Member of 

-----------+------------------------------------------------------------+-----------

 Player1   |                                                            | {}

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

For the purpose of this tutorial, let’s set back the Player1 with SUPERUSER specification:

postgres=# ALTER ROLE “Player1” WITH SUPERUSER;

Let’s back to the Linux prompt with CTRL + D.

We want now to connect with the user Player1 to the data1 database.

Let’s do the following:

$ psql -U Player1 -d data1

You should see something like this:

$ psql -U Player1 -d data1

Password for user Player1: 

psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1))

Type "help" for help.


data1=#

Great, you are now connected to the data1 database as Player1.

To check that, write the following command:

data1=# \conninfo

You should see this message:

You are connected to database "data1" as user "Player1" via socket in "/var/run/postgresql" at port "5432".

To create a table in this database you need to write it like that:

data1=# CREATE TABLE t1 ();

The space character between the table name and the paranthesis has to be respected.

The role who creates the table is the owner of this table.

The notion of owner is important because only this owner can for example drop the table created.

As you can see the t1 table is now created in the data1 database.

This creation is made by the Player1 user.

In order to see that let’s use the pg_tables view command:

data1=# SELECT * FROM pg_tables WHERE tablename = 't1';

It should display:

schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 

------------+-----------+------------+------------+------------+----------+-------------+-------------

 public     | t1        | Player1    |            | f          | f        | f           | f

(1 row)

As you can see Player1 is the tableowner of the t1 tablename.

So only the Player1 role can modify this t1 table (except of course other roles with SUPERUSER privileges).

To verify that let’s create another role Player2:

data1=# CREATE ROLE "Player2" WITH LOGIN PASSWORD 'hello';

The Player2 user is now created with privilege to log in to the databases.

Let’s go back to the Linux prompt (with CTRL + D) in order to create a new psql connection:

$ psql -U Player2 -d data1

Password for user Player2: 

psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1))

Type "help" for help.


data1=>

Now let’s try to alter the t1 table:

data1=> ALTER TABLE t1 ADD COLUMN name VARCHAR(40);

ERROR:  must be owner of table t1

The ERROR message is clear, we can’t modify this table because we aren’t the owner.

Bonus with pgAdmin

As a bonus let’s see how to manage a PostgreSQL server directly from pgAdmin 4 web-browser GUI:

Of course, we admit that you succefully installed it on your Linux Ubuntu.

We have to connect to the http://localhost/pgadmin4/browser/ URL.

Then from the top menu > Menu > Object > Create > Server Group > Enter the name group-badprog.

It should appear on the left panel.

Right click this group-badprog server group then > Register > Server… > A popup appears.

Enter the following information and for the ones not used, let them by default:

General
- Name: s1
Connection
- Host name/address: localhost
- Port: 5432
- Maintenance database: data1
- Username: Player1
- Password: the password you created previously with the Player1 user.

Click Save.

The s1 server is now part of the group-badprog server group.

So to see our t1 table from data1 database, we have to use the tiny arrow on each section:

group-badprog > s1 > Databases > data1 > Schemas > public > Tables > t1.

At this moment you can do a right click on t1 then Query Tool.

You should see the query area appears.

You are now ready to create your PostgreSQL queries directly from this area.

For example, you can create the badprog_user table like this:

-- badprog_user

CREATE TABLE IF NOT EXISTS public.badprog_user

(

    id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    firstName text NOT NULL,

    lastName text NOT NULL,

    password text NOT NULL,

    ts timestamp without time zone NOT NULL DEFAULT clock_timestamp()

);

Then click the Execute/Refresh pushButton (the black triangle) to execute this query.

If everything went fine then you should see the following message in the Message area:

======

CREATE TABLE

Query returned successfully in 106 msec.

=====

As in the command line, you can see the CREATE TABLE message without any error.

So the query has been executed.

To see this new table in the list, right click the Tables menu from the left panel then click Refresh.

Select the badprog_user table then Columns to see all the columns added from the PostgreSQL query.

Conclusion

A good start when you want to use PostgreSQL as a server.

This tutorial was for setting PostgreSQL on Linux and in Localhost.

Free to you to adapt it for another OS or for a remote server.

Anyway, good job if you made it through this installation.