PostgreSQL in LEMP Stack and Ubuntu 20.04

Complete the set up for an Ubuntu modified LEMP stack. Install PostgreSQL and PHP. Be sure to read the prior blog for Part 1.

In servers running Ubuntu with Nginx web server, completing the LEMP stack requires two more services. It will need a database and scripting language. A complete LEMP stack allows the web content creator (you) to publish to your web app; and allows users to supply data you can use in your web app. Instead of the usual Mariadb or MySQL, I will show you how to integrate PostgreSQL in the LEMP stack. The scripting language will remain PHP.

The scripting language allows data bewteen the user and the web app server to be verified, authenticated, manipulated and otherwise processed. Automated scripts interacting with our database is great. But sometimes we need granular analysis and control of our database. And so we need a user friendly way to administer the database. I'll also go over how to install phpPgAdmin.

LE in LEMP

As a reminder, the L in LEMP stands for Linux, and the E stands for (E)Nginx.

Go ahead and install both, if you haven't already.

• Linux as Ubuntu 20.04 encrypted on the cloud
• Nginx web server (Part 1 of 2 for modified LEMP stack)

If you would rather install PostgreSQL and PHP on Ubuntu 18.04, following along in my prior tutorial

M in LEMP (modified) - PostgreSQL

The M in LEMP is supposed to stand for Mariadb or MySQL. Because one of the apps I needed on my server required PostgreSQL, I will show you how to install this instead. Unfortunately, some of the SQL commands in PostgreSQL are different from MySQL or Mariadb. But the overall syntax is very similar. It's kind of like, if you can understand people speaking English in Seattle, you probably can understand people speaking English in South Africa. But the differences are notable.

Update the apt respositories, and install the PostgreSQL package.

sudo apt update
sudo apt install postgresql postgresql-contrib

Create a local PostgreSQL super user (or role). The PostgreSQL root user/role is still valid, but some apps don't like us using it. Feel free to use your own username and password combination (red in the example below). I'd stay away from symbols for the password. When using our SQL username/password combinations in scripting languages, you may need to escape symbol characters. To avoid this, just don't use symbols. Here is a simple script to create a random password: cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 20 | head -n 1.

sudo su postgres
createuser -P --interactive
Enter name of role to add: sql_root
Enter password for new role: password
Enter it again: password
Shall the new role be a superuser? (y/n) y
exit

Let's run a few SQL codes to introduce you to it. Below, we'll set the timzone to America/Los_Angeles. Any PHP recommended timezones should work with PostgreSQL. To find how your timezone is written, feel free to look it up through the PHP Manual.

sudo -i -u postgres psql -c "ALTER DATABASE postgres SET timezone TO 'America/Los_Angeles';"
sudo -i -u postgres psql -c "SELECT pg_reload_conf();"
sudo -i -u postgres psql -c "SELECT NOW();"

And that's it. Our database is ready, and we have access to it. Now to allow scripted (or automated) access to the database, we need a programming language.

P in LEMP - PHP

PHP is an open-source scripting language used ubiqutously for web apps. It doesn't gain as much prestige in job hiring as other languages, but it gets the job done. The biggest bonus for me, many forums on line to help me troubleshoot issues. There are many tutorials out there on how to start using PHP. It is beyond the scope of this tutorial, and I welcome you to Bing it your self.

There are many packages you can add onto PHP. The packages included here are likely needed for the more common web apps. php-pgsql is required with PostgreSQL installed. Feel free to start with the basic install, and later adding the packages you actually need.

sudo apt install php php-bz2 php-curl php-fpm php-gd php-mbstring php-pgsql php-xml php-zip curl

As you probably noticed, this automatically installed Apache. We will not be using the Apache web server. So let's disable and stop the service.

sudo systemctl disable apache2.service
sudo service apache2 stop

Let's back up and modify some of the basic PHP settings. You'll notice two main versions of PHP we will focus on. FPM (or FastCGI Process Manager) is a way the Nginx web server can interface with a server process that focuses just on PHP scripts. CLI (or command line interface) is a way for us to run PHP scripts from the command line.

sudo cp /etc/php/7.4/fpm/php.ini /etc/php/7.4/fpm/php.ini.bak
sudo cp /etc/php/7.4/cli/php.ini /etc/php/7.4/cli/php.ini.bak
sudo nano /etc/php/7.4/fpm/php.ini

Similar to PostgreSQL, let's change the PHP timezone. Modify the line that starts with date.timezone = (use Ctrl+w to search), and insert your time zone. Here's the list of available time zones. Do the same for the command line interface, e.g., sudo nano /etc/php/7.4/cli/php.ini.

SQL web management - phpPgAdmin

Using the SQL database by command line is a good way to go. For me, I like visual. One of the most popular web apps used for SQL management is the MySQL or Mariadb program called phpMyAdmin. Unfortunately, this doesn't work with PostgreSQL. However, there does exist phpPgAdmin. As the app name suggests, this interface between a web browser and your database uses the scripting language we just installed, PHP.

Install phpPgAdmin by apt package installer.

sudo apt install phppgadmin

Now that the bones of phpPgAdmin is installed, we need a way to access it from the web. But first, let's secure the access from the web to the database first.

Nginx, Apache and htpasswd

To access this public facing website, the private data you store in the SQL database will be at risk. I don't want people snooping in and seeing the log-in page for phpPgAdmin and know they can try hacking into it and getting the data. To help minimize this risk, I added an additional layer of security. Nginx allows use of validating users and passwords through Apache's htpasswd module.

Install the Apache utils package, which includes the program htpasswd.

sudo apt install apache2-utils

Create and save the web password file. Give access only to www-data user and group in your Ubuntu system.

sudo htpasswd -c /usr/share/nginx/.htpasswd username
New password: password
sudo chown www-data:www-data /usr/share/nginx/.htpasswd
sudo chmod 600 /usr/share/nginx/.htpasswd

We'll set up the http password authentication later on, once we start setting up the virtual host for SQL web administration.

Nginx virtual host

Pick a subdomain that suits your needs that will be specifically pointed to phpPgAdmin. For this tutorial, I'll use db as the subdomain. In an FQDN, that comes out to db.domain.com, using the naming convention I've used with all my blogs.

You must add this subdomain (or host) to your domain name registrar. Add an A record for db to point to your ipv4 address. If you have an ipv6 address, then be sure to add an AAAA record as well. It may take minutes to days for the change to propagate among all the DNS servers. In my experience, it takes no more than a few minutes. Sometimes, flushing your local DNS cache may be needed (but usually not needed). If you're unsure about all this, then Bing it.

The next step isn't necessary. And if you choose to forgo it, then you will have to modify other steps down the road. I like to put all my hosted web apps in one location, even if they are just symbolically linked there. That's so that I can quickly see what web services I'm running.

Create a symbolic link from the phpPgAdmin install folder to the Nginx hosted web files folder. Note that the terminal folder should be the subdomain name you pick. I'll highlight mine in red; you put in your own.

sudo ln -s /usr/share/phppgadmin /usr/share/nginx/db
sudo chown -R www-data:www-data /usr/share/phppgadmin

We have to create a basic Nginx configuration file for our subdomain. Remember, substitute your own subdomain and domain names below. The file extension .conf is important. We will modify it further down the road. Run the command sudo nano /etc/nginx/sites-available/db.domain.com.conf.

server {
  listen 80;
  listen [::]:80;
  server_name db.domain.com;
  location ^~ /.well-known/acme-challenge/ {
    default_type "text/plain";
    root /usr/share/nginx/db;
  }
}

Now enable the confiuration and restart the service.

sudo ln -s /etc/nginx/sites-available/db.domain.com.conf /etc/nginx/sites-enabled/
sudo service nginx restart

Expand Let's Encrypt certificate

We have to expand our current Let's Encrypt TLS certificate via Certbot. If you followed along from my prior tutorials, then these steps will work with your system. If you have your own TLS certificate or did not follow my other tutorials, then use your own certificates in the configuration file below. For all the others, follow along.

Create the webroot directory for Certbot to use. Let's give ownership to www-data to the Nginx hosting directory and all subdirectories as well.

sudo mkdir -p /usr/share/nginx/db/.well-known/acme-challenge
sudo chown -R www-data:www-data /usr/share/phppgadmin
sudo chown -R www-data:www-data /usr/share/nginx

The first line in the command calls the certbot program and the \ character signifies the single-line command continues to the next line. I do this to help with readability. The "second line" is important and maintains the current subdomains and domains for the TLS certificate. The third line is how we add the new subdomain (virtual host) to our current certificate. Be sure to replace your Ubuntu hostname for host and your domain name for domain below. If you picked a different SQL database subdomain, then put replace that for db.

sudo certbot certonly --expand --webroot \
  -w /usr/share/nginx/html -d www.domain.com -d domain.com -d host.domain.com \
  -w /usr/share/nginx/db -d db.domain.com

Nginx update config file

Let's update the db subdomain configuration file. Be sure to use your own subdomain and domain names.

sudo nano /etc/nginx/sites-available/db.domain.com.conf

Enter and modify the lines below into the nano editor. This is where we put in the Nginx/Apache htpasswd username/password file created above. Look for the lines auth_basic and auth_basic_user_file below. The lines that begin with add_header X-Robots-Tag keep search bots like Bing and Google from indexing this private site. We don't want this site popping up in their search results.

# Non-encrypted http traffic
server {
  listen 80;
  listen [::]:80;
  server_name db.domain.com;
  root /usr/share/nginx/db;
  # This location directive is for Certbot certificate creation and renewal
  location ^~ /.well-known/acme-challenge/ {
    default_type "text/plain";
    root /usr/share/nginx/db;
  }
  # All non-encrypted traffic should otherwise be redirected to encrypted traffic
  location / {
    return 301 https://db.domain.com$request_uri;
  }
}
# Encrypted https traffic
server {
  listen 443 ssl;
  listen [::]:443 ssl http2;
  server_name db.domain.com;
  root /usr/share/nginx/db;
  index index.html index.php;
  add_header X-Robots-Tag noindex,nofollow,noarchive,nosnippet,notranslate,noimageindex;
 
  # Encryption settings
  ssl_certificate /etc/letsencrypt/live/www.domain.com/fullchain.pem;
  ssl_certificate_key /etc/letsencrypt/live/www.domain.com/privkey.pem;
  ssl_trusted_certificate /etc/letsencrypt/live/www.domain.com/chain.pem;
  include /etc/letsencrypt/options-ssl-nginx.conf;
  ssl_stapling on;
  ssl_stapling_verify on;
 
  # Root location
  location / {
    # Next two lines are the Apache http user/password authentication
    auth_basic "not allowed";
    auth_basic_user_file /usr/share/nginx/.htpasswd;
    try_files $uri $uri/ =404;
  }
 
  # Any calls for php files will be forwarded to PHP-FPM daemon process
  location ~ \.php$ {
    fastcgi_pass unix:/run/php/php7.4-fpm.sock;
    fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
    include fastcgi_params;
    include snippets/fastcgi-php.conf;
  }
}

If you want Nginx to forward calls from web browsers to domain.com/db to this web app, then edit the default Nginx conf file sudo nano /etc/nginx/conf.d/default.conf. Add the following lines inside the server directive that listens for 443 ssl http2.

location /db {
  return 301 https://db.domain.com;
}

Now reload the Nginx server. I use reload rather than restart, because if an error occurs, the service remains in it's old state. If you use restart and the service messes up due to your configuration changes, then it stops entirely.

sudo service nginx restart

Fail2ban and phpPgAdmin

We used Apache's html authentication tool with our phpPgAdmin site. To keep bad people from breaking in, we can put them in jail. Let's create a Fail2ban custom filter definition for this purpose. Run the command sudo nano /etc/fail2ban/filter.d/nginx-htpasswd.conf.

[Definition]
failregex = ^.*user ".*"(?:(?: was not found in.*\.htpasswd")|(?:: password mismatch)), client: <HOST>, server: \S*, request: [\S ]*, host: \S*"$
ignoreregex =
datepattern = {^LN-BEG}

Let's test our regular expression above, and make sure Fail2ban finds appropriate log entries. Go to your phpPgAdmin site but login with incorret htpasswd credentials. Do this a few times, so that there are log entries reflecting this. Here's the command that can test if Fail2ban recognizes our regular expression. The conf file is indeed stated twice in the command below; once each for the location of failregex and ignoreregex.

sudo fail2ban-regex /var/log/nginx/error.log /etc/fail2ban/filter.d/nginx-htpasswd.conf /etc/fail2ban/filter.d/nginx-htpasswd.conf

In the midle of the output, you should see the following line (numbers will be different for you). Where it says 2 matched means that Fail2ban recognized (matched) my two incorrect logins.

Lines: 17 lines, 0 ignored, 2 matched, 15 missed

Now we know our filter definition works. But we still have to activate the jail. We'll create the jail in the Fail2ban jail.d folder. Run the command sudo nano /etc/fail2ban/jail.d/nginx-htpasswd.conf. It is important that what's between the square brakets [ ] matches the filter definition filename above (without trailing .conf).

[nginx-htpasswd]
enabled = true
port = http,https
logpath = /var/log/nginx/error.log

Restart the service.

sudo service fail2ban restart

Feel free to do a few more failed login attemps with the htpasswd credentials. And check the log to see if it was registered correctly.

cat /ar/log/fail2ban.log
sudo fail2ban-client status nginx-htpasswd

If you were put in the jail, because you failed authentication too many times, you can unban yourself. Be sure to replace [your ip] with your own (without the square brackets).

sudo fail2ban-client set nginx-htpasswd unbanip [your ip]

Use your phpPgAdmin web app

Now go to a web browser and type the address http://domain.com/db. You should see that it redirects to the https port and also to the address db.domain.com (note the db as a subdomain rather than a directory).

You will first log in with the htpasswd credentials. After the phpPgAdmin web app loads, then log in with your PostgreSQL credentials. You created both of these earlier on.

That's it. You now have a fully functional LEMP Stack with web-based SQL administration. Congratulations!!

There's so much you can do with this. This includes creating a database for web app user authentication, email server, shared document server, and so many more.