Installing and setting up Postgres in Ubuntu

At work, we handle a lot of short-term projects, typically lasting 3-8 weeks. These projects don't require a complex deployment setup. Instead, we deploy each project on a single VPS instance, with all the stacks bundled together on the same system.

Sure, it's a single point of failure and not the ideal setup, it however matches our clients’ budget and allows us to deliver quickly. Luckily, we've never faced a catastrophic failure with this approach (yet).

In these types of projects, which come and go, each one is deployed on its own VPS. Which also means I set up a DB instance on each VPS. Honestly, this routine task could be scripted, but I actually enjoy doing it manually.

Overall, the routine involves installing the packages, ensuring they're running and will restart on reboot or failure, setting up the users, creating a database, and granting user privileges to the database. Depending on the circumstances, additional configuration might be needed if necessary or requested. However, this routine is what I consider the minimum or baseline.

It's worth noting that I'm mostly deploying on Ubuntu systems, so some commands are specific to this distro.

Installing the Postgres

The PostgreSQL package is available in the main repository, so it can be installed with the following command:

$ sudo apt install postgres postgresql-contrib

Once installed, PostgreSQL is available on your system, but it isn't running yet!

PostgreSQL service up and running

The PostgreSQL process is managed by systemd. After installation, a systemd service unit file is provided. You just need to start the service and enable it to start on reboot.

To start the PostgreSQL service, simply enter the command:

$ sudo systemctl start postgres.service

You can verify that it is running using the status command in systemctl.

To ensure that the PostgreSQL service automatically starts on reboot, use the following command:

$ sudo systemctl enable postgres.service

Now that you have a PostgreSQL instance up and running, the next step is to set up users to connect to it.

Creating a new user

Out of the box, after installing the PostgreSQL package, a postgres user is created with administrative privileges by default.
To create a new DB user, you need to switch to the postgres user. You can do this with the following command:

$ sudo su postgres

You can confirm if the switch was successful by checking your username with the following command:

$ whoami

To create a new DB user, run the createuser command. It will prompt you for the role name and ask if the new role should be a superuser. Enter y to confirm if you want the new role to have superuser privileges.

$ createuser –interative

     Enter name of role to add: {{username}}
     Shall the new role be a superuser? (y/n) y

Creating a new database

Creating a new database is very easy. Simply use the following command:

$ createdb {{database_name}}

Once that's done, you have created a new database. However, the new user doesn't have privileges to access or modify the database yet. To grant these permissions, you can use an SQL query to assign the appropriate privileges to your user.

Enter psql to start a session where you can run SQL queries:

$ psql

GRANT CONNECT ON DATABASE {{database_name}} T O {{username}};
GRANT pg_read_all_data TO {{username}};
GRANT pg_write_all_data TO {{username}};

This grants the user permission to connect to the database and provides read and write access to all the data in the database.

Authenticating user

Currently, the user you just created has been granted access to connect to the database. However, you still need to log in as that user.

There are several types of authentication you can use to connect, depending on your use case. For this routine, I use password-based authentication to connect to the PostgreSQL instance.

What I typically do is set or change the password for the newly created user. As the postgres user, which has the highest authority, you can do this with the following SQL command:

ALTER USER {{username}} WITH PASSWORD '{{new_password}}';

Once that's done, you can exit the SQL shell and then exit the postgres user shell. This requires two exit commands:

\q exit

...

Others

There are easier ways to set up Postgres, but I find using Docker images more efficient. It's quick and simple. For large-scale server deployments, Ansible scripts are often a better option.

Reference Notes

Installing PostgreSQL on Ubuntu 20.04
https://www.digitalocean.com/community/tutorials/how-to-install-postgresql-on-ubuntu-20-04-quickstart

Granting permissions to a user/role for a database.
https://stackoverflow.com/questions/22483555/postgresql-give-all-permissions-to-a-user-on-a-postgresql-database