Free AI web copilot to create summaries, insights and extended knowledge, download it at here

2080

Abstract

have have running postgres sql server on the virtual machine. Double check that this is the case by checking up on the service by issuing the following command</p><div id="7175"><pre>sudo systemctl status postgresql</pre></div><h1 id="2b2e">Setting up our first non-superuser</h1><p id="3283">With the installation of postgres comes a user called postgres. I like to leave this user untouched. Not give it any password in postgres and not allow it to log in externally. This user will remain the local admin, accessible to users that are superusers on the Linux machine.</p><p id="c738">We will instead create an application user, that is meant to be used when connecting from your application. If multiple applications connect to the same postgres server, then I recommend that you create one user for each application and give the appropriate permissions.</p><p id="e145">To create our application user we begin to switch our terminal sessions to use the linux postgres user.</p><div id="d8cd"><pre>sudo su - postgres</pre></div><p id="3623">As the linux postgres user you create postgres users by running createuser from the terminal.</p><div id="03a3"><pre>createuser --interactive --pwprompt</pre></div><p id="7bb7">This will launch a interactive setup of the user, fill in the information and when you are done, the user should have been created.</p><h1 id="7b30">Allow external access</h1><p id="0351">By default, postgres will not allow anybody to login or even connect from the outside. This might be fine, if you only connect to the server from within the same server, which is not the case for us in this guide, therefore we will open it up.</p><p id="dc22">We start by configuring postgres to listen to all network interfaces on the machine. This means that it will be accessible on any of the IP addresses that is attached to the machine. This setting is found in the postgres.conf file located at /etc/postgresql/13/main/postgresql.conf for me. You need to be superuser to edit this file, and you can use which editor you want, i use nano here as its the one that is

Options

the hardest to get stuck in (this is not a guide about vim 😅).</p><div id="f293"><pre>nano /etc/postgresql/13/main/postgresql.conf</pre></div><p id="ac46">If you cannot find the file at that location, it could be that you have a newer version of postgres. Try typing /etc/postgresql/ then hit TAB, and we what get autocompleted for you.</p><p id="22f5">Inside of the postgresql.conf file, we want to scroll down to the line that says listen_addresses. Remove the initial # (uncomment) on the line and change it to look like this</p><div id="1ba5"><pre><span class="hljs-attr">listen_addresses</span> = <span class="hljs-string">'*'</span></pre></div><p id="f7b3">The server now binds on all ip addresses.</p><p id="b930">We still are not allowed to login using username and password from other IP addresses. This access is controlled by another config file, called pg_hba.conf, probably located in the same folder as your postgres.conf file.</p><p id="16b4">Open it in the editor</p><div id="19da"><pre>nano /etc/postgresql/13/main/pg_hba.conf</pre></div><p id="e978">In here we want to change the ipv4 line that starts with host, and make it look like this. If you cannot find which one to change, just add this to the list, you could also watch the video where i show which one i change.</p><div id="2d7c"><pre>hostssl <span class="hljs-attribute">all</span> <span class="hljs-attribute">all</span> <span class="hljs-number">0.0</span>.<span class="hljs-number">0.0</span>/<span class="hljs-number">0</span> md5</pre></div><p id="0d74">Save the field and exit out of the linux postgres user.</p><div id="93bc"><pre><span class="hljs-built_in">exit</span></pre></div><p id="8bd5">All we have to do now is to restart the postgres service and we are good to go.</p><div id="0237"><pre>sudo systemctl restart postgresql</pre></div><p id="8d86">Thats it! If something didn't work out as expected. Feel free to reach out to me on <a href="https://twitter.com/lindblomdev">twitter</a> or leave a comment on the youtube video.</p></article></body>

Free PostgreSQL Server Setup on Ubuntu

Hello folks, we will be setting up our own PostgreSQL server (simply called postgres in the rest of the guide).

You will need to have a server to do this (any virtual machine should work). If you don't have a server, i have a suggestion below.

If you are more of a visual learner, then i have made a video for you over on my web development youtube channel.

Free hosting suggestion

A postgres server can run 24/7 for free within the free tier on google cloud platform. Its crazy that this is possible, but it is 😊.

When creating the virtual server, its important that you read the free tier limits if you want to get it for free. If you configure your vm outside of the free tier, then you will have to pay for it. To be on the safe side, i will refer you to the google cloud free tier website where you can check which one to create. When you have done that you can come back here and we will execute the steps together.

Preparing the machine

Lets make sure that we have the prerequisites installed by running the following commands

sudo apt update 
sudo apt install wget curl ca-certificates gnupg

Then we need to add the repository to apt

wget -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

Install database

Now we are ready to actually install the database. You do this by running the following commands

sudo apt update 
sudo apt install postgresql postgresql-contrib

When you have completed that we should have have running postgres sql server on the virtual machine. Double check that this is the case by checking up on the service by issuing the following command

sudo systemctl status postgresql

Setting up our first non-superuser

With the installation of postgres comes a user called postgres. I like to leave this user untouched. Not give it any password in postgres and not allow it to log in externally. This user will remain the local admin, accessible to users that are superusers on the Linux machine.

We will instead create an application user, that is meant to be used when connecting from your application. If multiple applications connect to the same postgres server, then I recommend that you create one user for each application and give the appropriate permissions.

To create our application user we begin to switch our terminal sessions to use the linux postgres user.

sudo su - postgres

As the linux postgres user you create postgres users by running createuser from the terminal.

createuser --interactive --pwprompt

This will launch a interactive setup of the user, fill in the information and when you are done, the user should have been created.

Allow external access

By default, postgres will not allow anybody to login or even connect from the outside. This might be fine, if you only connect to the server from within the same server, which is not the case for us in this guide, therefore we will open it up.

We start by configuring postgres to listen to all network interfaces on the machine. This means that it will be accessible on any of the IP addresses that is attached to the machine. This setting is found in the postgres.conf file located at /etc/postgresql/13/main/postgresql.conf for me. You need to be superuser to edit this file, and you can use which editor you want, i use nano here as its the one that is the hardest to get stuck in (this is not a guide about vim 😅).

nano /etc/postgresql/13/main/postgresql.conf

If you cannot find the file at that location, it could be that you have a newer version of postgres. Try typing /etc/postgresql/ then hit TAB, and we what get autocompleted for you.

Inside of the postgresql.conf file, we want to scroll down to the line that says listen_addresses. Remove the initial # (uncomment) on the line and change it to look like this

listen_addresses = '*'

The server now binds on all ip addresses.

We still are not allowed to login using username and password from other IP addresses. This access is controlled by another config file, called pg_hba.conf, probably located in the same folder as your postgres.conf file.

Open it in the editor

nano /etc/postgresql/13/main/pg_hba.conf

In here we want to change the ipv4 line that starts with host, and make it look like this. If you cannot find which one to change, just add this to the list, you could also watch the video where i show which one i change.

hostssl    all             all             0.0.0.0/0            md5

Save the field and exit out of the linux postgres user.

exit

All we have to do now is to restart the postgres service and we are good to go.

sudo systemctl restart postgresql

Thats it! If something didn't work out as expected. Feel free to reach out to me on twitter or leave a comment on the youtube video.