To install on Ubuntu:
$ sudo apt update $ apt install postgresql
$ to indicate the command line (shell) prompt.
You can find other OS specific installation instructions on the official postgres website.
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
$ sudo -u postgres psql
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
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:
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.
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
If necessary, we could give it
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
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
Connecting to the database
The final step is to connect to the database
$ 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 --system
--system option is passed to create a system user and disable logins. It’s also possible to omit it.
EDIT: Previously, we suggested passing the
--no-create-home option to avoid creating a
/home/my_user dir. However, this directory is needed by Postgres to store the history file (
Then, we’ll be able to connect to the database executing the
psql command as
$ sudo -u my_user psql db
We’ll receive the following prompt:
That shows that we’re successfully connected to database
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.