Skip to content

PostgreSQL installation and configuration on Ubuntu Linux

postgresql logo

Installing Postgres

To install on Ubuntu:

$ sudo apt update
$ apt install postgresql

We’ll use $ to indicate the command line (shell) prompt.

You can find other OS specific installation instructions on the official postgres website.

Postgres configuration

By default, postgres will add a database superuser postgres as well as a database with the same name.

To connect to postgres as this user, we’ll use the psql command line tool. It’s installed along with postgresql.

$ sudo -u postgres psql

With sudo -u postgres, we execute the psql tool as user postgres, which is the only postgres user just after installation.

The above command is equivalent to:

$ sudo -u postgres psql postgres

The psql command connects to a database. If none is given, it will default to the current active username as the database name.

Once connected, we’ll get the following prompt:

postgres=#

We can get the connection information using the command \c. Additionally, a complete list of psql commands can be obtained with \?.

postgres=# \c
You are now connected to database "postgres" as user "postgres".

This shows that we’re connected to the database as user postgres.

Creating users

The postgres user has all privileges. It’s bad practice to manipulate databases using it. To avoid messing up, we’ll create a new user with fewer privileges:

postgres=# CREATE USER my_user WITH PASSWORD '123456';

This will create a new user my_user with the password above. We can list all users using \du.

If necessary, we could give it SUPERUSER privileges.

postgres=# ALTER USER my_user WITH SUPERUSER;

For a list of other privilege options, see here.

Creating a database

We can create a new database like so:

postgres=# CREATE DATABASE db WITH OWNER='my_user';

This will create database db. Setting my_user as the database owner will grant this user all the necessary privileges.

There is no need to grant privileges to the owner of an object (usually the user that created it), as the owner has all privileges by default.

Postgres Docs

Finally, we can disconnect from the database as user postgres with \q.

Connecting to the database

The final step is to connect to the database db.

$ psql db

Depending on your OS and Postgres configuration, you might get the following error message:

FATAL:  Peer authentication failed for user "my_user"

This happens because, as a security measure, postgres will verify the user identity from the kernel of the OS. If we aren’t logged in to the OS as user my_user, postgres will complain.

One solution is to create the postgres user with the same name as your Ubuntu username. If that’s the case, you will not receive the error message above when trying to connect.

Alternatively, we can create a new Ubuntu user with the postgres username:

$ sudo adduser my_user --no-create-home --system

The --no-create-home and --system options are used so that no home directory is created for this user. It will be like a “process” user used only to access the postgres database.

Then, we’ll be able to connect to the database executing the psql command as my_user:

$ sudo -u my_user psql db

We’ll receive the following prompt:

db=#

That shows that we’re successfully connected to database db.

db=# \c
You are now connected to database "db" as user "my_user".

From here, we’re able to execute any SQL commands we’d like in our database.

db=# CREATE TABLE events(id serial primary key, name text);

This concludes this postgres configuration tutorial. To see how to interact with the database using Node.js, check this post.

Published inArticles
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments