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 --system
The --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 (~/.psql_history).
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.