This is an assignment h3 - Linux palvelimet ICT4TN021-8 in Haaga-Helia University of Applied Sciences

In this assignment we had to choose five optional exercises regarding LAMP and optionally similar stacks. I decided to choose the following exercises:

  • Installing LAMP stack and testing each component
  • Installing and testing PhpMyAdmin
  • Making Apache Virtual Host so that regular user can edit Apache’s webpage without root privileges
  • Testing new Python Flask’s new features (e.g. templates)
  • Lastly installing Python Flask + PostgreSQL + Apache mod WSGI and testing each component.

During this assignment my local computer (Lenovo Z710) runs the latest Arch Linux x86_64 but along side that I’ll make new personal server in which I’ll do theses exercises. This server runs Ubuntu 16.04.3 x64 and is provided by DigitalOcean. Get $10 free in credits when registering to DigitalOcean via my referral.

Installing LAMP Stack

Before starting to install LAMP stack on my server I need to login to it via ssh. I covered SSH in the last assignment, Logging and SSH. Again I’m not gonna show my server’s username and ip address.

Login via SSH

Since I’ve generated SSH key pair between my local computer and server, the server doesn’t ask for my passphrase and allows me to connect instantly. This was also covered in the last assignment.

Installing Apache

Apache is the most popular web server in the world so it is trusted choice for hosting your websites.

You can install Apache via most Linux’s package managers and since I’m using Ubuntu on my server I’ll use apt:

$ sudo apt-get update

$ sudo apt-get install apache2

Since we are using sudo command here it’ll ask for your password and after that apt tells you which packages its gonna install.

If you’re working locally, for any reason, you should be able to connect to the Apache web server by typing localhost or your IP address to your browser’s URL bar. But since I’m working on my server I cant do that just yet. To be able to connect to my server’s Apache web server I need to allow HTTP and HTTPS traffic to it on my server’s firewall.

I use ufw as my firewall and installing Apache the installation process should make an application profile for Apache to UFW’s application list. You can check these applications with:

$ sudo ufw app list

And for me the output of that is:

Available applications:
  Apache
  Apache Full
  Apache Secure
  OpenSSH

Here we can see that my firewall has made profiles for Apache. Apache profile represents profile for HTTP connections, Apache Secure for HTTPS connection and Apache Full for both. You can check the profile’s info with command:

$ sudo ufw app info "Apache Full"

The output of that command

Profile: Apache Full
Title: Web Server (HTTP,HTTPS)
Description: Apache v2 is the next generation of the omnipresent Apache web
server.

Ports:
  80,443/tcp

From that output you can see that the profile enables traffic to ports 80 (port for HTTP connections) and 443 (port for HTTPS connections).

To allow traffic to these ports:

$ sudo ufw allow in "Apache Full"

Now if you try to connect to your server’s web server by typing your server’s ip address to your browser’s URL bar you should receive the Apache’s default web page. But before that, we could check for syntax errors just in case by command:

$ sudo apache2ctl configtest

For me it returned this:

AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.1.1. Set the 'ServerName' directive globally to suppress this message
Syntax OK

If I’ve understood correctly, this should be a harmless syntax error but we still want to get rid of it. Here you can see that the error code is called AH00558 and it has problems to understand the server’s domain name. It also tells you that to suppress it you need to set ServerName directive. This can be done with:

$ sudo emacs /etc/apache2/apache2.conf

Note: you can use any editor as you want, my preference is just Emacs

Here you can see a big list of different configurations for Apache, but for now we don’t care about them. To add ServerName directive we need to go to the bottom of the file and add the following line to the end of it:

ServerName ip-addr

In which you replace the ip-addr with your own ip address, but if you’re working locally localhost should work fine.

Now if you run configtest:

$ sudo apache2ctl configtest

You should see:

Syntax OK

After all the changes we can now restart Apache2:

$ sudo systemctl restart apache2

Now when we type my server’s ip address to my browser’s URL bar we can see:

Apache default page

And Apache is now working!

Installing MySQL

Again since we are using Ubuntu on my server we can use apt to install MySQL. To install it we run the following command:

$ sudo apt-get install mysql-server mysql-client

Which install both database server and client for you. After it has installed both server and client for you, it’ll ask for you to set your MySQL’s root password. Before we log in to the MySQL for the first time, MySQL comes with a simple security script that is recommended to run when installing MySQL. You can run this script with the command:

$ mysql_secure_installation

When you run the script it’ll ask for your password you just set:


Securing the MySQL server deployment.

Enter password for user root: 

Next it’ll ask you if you want to configure the VALIDATE PASSWORD PLUGIN:

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: 

It is often recommended to setup this plugin, since it requires you to use strong passwords everywhere across MySQL. But since I made this server solely for this assignment and for exercise’s sake we are not going to setup it. If you want to setup it you write “y” there, otherwise you can press any other key.

Next it’ll ask you if you want to change your MySQL root’s password:

Change the password for root ? ((Press y|Y for Yes, any other key for No) : 

But since I chose strong password I’m going to choose No here.

Next it’ll ask you if you want to remove anonymous users from your MySQL installation. These kind of users are mainly for testing and should be removed if you want to move your database into production:

By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y

Im going to remove them by writing “y”. Next the script asks you if you want to remove remote root connections. This enables that root is only allowed to connect from the localhost:

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y

Again I’m going to choose “y” here. Next the script asks you to remove test databases that anyone can access to:

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

And again I’m going to choose “y” here. Lastly the script ensures that all the changes done here will take effect immediately:

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 

After that your MySQL should be installed securely. Now we can test the MySQL simply by:

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

And we are in MySQL! We can see that the secure installation script has deleted test database as it was intended by:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

We can create our own database and ensuring it has UTF-8 character encoding by:

mysql> CREATE DATABASE bluesdudes CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bluesdudes         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

We can make user for that database by;

mysql> GRANT ALL ON bluesdudes.* TO [email protected] IDENTIFIED by 'password';
Query OK, 0 rows affected, 1 warning (0.00 sec)

And of course change the username for anything you want and password for a stronger one. Now we can test if the user works correctly:

mysql> exit
Bye
[email protected]:~# mysql -u topi -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE jazzdudes CHARACTER SET utf8;
ERROR 1044 (42000): Access denied for user 'topi'@'localhost' to database 'jazzdudes'
mysql> USE bluesdudes;
Database changed
mysql> CREATE TABLE bluesdudes (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(512));
Query OK, 0 rows affected (0.01 sec)

mysql> DESCRIBE bluesdudes;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(512) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO bluesdudes(name) VALUES ("B.B. King");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO bluesdudes(name) VALUES ("Muddy Waters");
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO bluesdudes(name) VALUES ("Albert King");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM bluesdudes;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | B.B. King    |
|  2 | Muddy Waters |
|  3 | Albert King  |
+----+--------------+
3 rows in set (0.00 sec)

mysql> UPDATE bluesdudes SET name = "T-Bone Walker" WHERE id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM bluesdudes;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | B.B. King     |
|  2 | Muddy Waters  |
|  3 | T-Bone Walker |
+----+---------------+
3 rows in set (0.00 sec)

mysql> DELETE FROM bluesdudes WHERE id = 3;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM bluesdudes;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | B.B. King    |
|  2 | Muddy Waters |
+----+--------------+
2 rows in set (0.00 sec)

Here we can see that the user topi only has access to database bluesdudes and can’t create a new one. We also tested that user topi can make all different operations in database bluesdudes that are needed for CRUD (Create, Read, Update, Delete).

Installing PHP

Again we can use Ubuntu’s apt for installing PHP. We also want to install few other packages along side PHP to make it run in Apache web server but also to make it communicate with our MySQL database:

$ sudo apt-get install php libapache2-mod-php php-mysql

Now we have installed PHP and modules for Apache and MySQL (We’ll test the PHP and MySQL communication later in the assignment). Since we are working with LAMP stack, we most likely want our Apache web server to prefer index.php over index.html. To make this possible we need to configure Apache’s dir.conf:

$ sudo emacs /etc/apache2/mods-enabled/dir.conf

Note: you can use any editor as you want, I just prefer the true one.

This opens the dir.conf file which should look something like this:

<IfModule mod_dir.c>
        DirectoryIndex index.html index.cgi index.pl index.php index.xhtml index.htm
</IfModule>

Here we need to change that index.php is higher on the list than index.html so Apache will prefer PHP over HTML. For example:

<IfModule mod_dir.c>
        DirectoryIndex index.php index.html index.pl index.cgi index.xhtml index.htm
</IfModule>

Now we need to check that Apache understands our configuration changes by:

$ sudo systemctl restart apache2

If it restarts without errors we now should’ve configured it correctly.

Next we need to test if our newly installed PHP works correctly with Apache. Easiest way to test this is by making a very basic PHP script which we can place to Apache’s “web root” directory, which in my server is located at /var/www/html/. To make this script run:

$ sudo emacs /var/www/html/test.php

This should open a blank file called test.php in which we write:

<?php
phpInfo();
?>

This is a simple command which calls informations about PHP’s configurations. Now we can see if our PHP works correctly by typing following to our browser’s URL bar:

ip-addr/test.php

And now you should see something like this:

PHP Info

Here you can see the list of different configurations of your PHP and on the top at the “System” box you can see my “linux-palvelimet” hostname, which I just made for this exercise. It is recommended to delete this page since we don’t want give information away if it’s not necessary. This can be done with:

$ sudo rm /var/www/html/test.php

So after this we now that our LAMP stack works. Next we could install phpMyAdmin, which makes it possible to interact with MySQL database from web interface.

Installing phpMyAdmin

phpMyAdmin can be found on most package managers and since we use Ubuntu we again use apt for this:

$ sudo apt-get install phpmyadmin

Installation then asks you for your server selection and since we use Apache2 we need to choose it by pressing SPACE. When the prompt window comes, the Apache2 is only highlighted and not chosen so we need make sure choose it before leaving the prompt. When pressing Space on top of the Apache2 choice it should insert small asterisk (*) next to the Apache2 choice. Then it asks you for database configuration so that phpMyAdmin can communicate with your database. Advanced database managers can configure it manually but I chose “yes” when the prompt asked me to use dbconfig-common which automates the configuration. After that prompt asks for your MySQL root password and after that phpMyAdmin should be installed. You can test it bu typing the following to your browser’s URL bar:

ip-addr/phpmyadmin

And it should show the following login screen:

phpMyAdmin

After that you can login to phpMyAdmin by typing root as the username and the corresponding password that you set during your MySQL installation. From there you should be able to see your databases and tables that you’ve made. For example below is a screenshot of my bluesdudes table that I made above:

phpMyAdmin Table

Now we can see that our phpMyAdmin works and it also correctly communicates with our MySQL database. After that you should also secure your phpMyAdmin instance since it’s a popular target for attackers, but for the sake of this exercise we are not going to it now.

Apache Virtual Host

Next part was to make a new Apache website that is possible to edit without root privileges. This can be done by directing minor tweaking of Apache’s virtual host configurations in /etc/apache2/sites-enabled/000-default.conf. 000-default.conf contains Apache’s default configurations for Apache websites. The file should look something like this:

$ sudo emacs /etc/apache2/sites-enabled/000-default.conf
<VirtualHost *:80>
        # The ServerName directive sets the request scheme, hostname and port that
        # the server uses to identify itself. This is used when creating
        # redirection URLs. In the context of virtual hosts, the ServerName
        # specifies what hostname must appear in the request's Host: header to
        # match this virtual host. For the default virtual host (this file) this
        # value is not decisive as it is used as a last resort host regardless.
        # However, you must set it for any further virtual host explicitly.
        #ServerName www.example.com

        ServerAdmin [email protected]
        DocumentRoot /var/html/www

        # Available loglevels: trace8, ..., trace1, debug, info, notice, warn,
        # error, crit, alert, emerg.
        # It is also possible to configure the loglevel for particular
        # modules, e.g.
        #LogLevel info ssl:warn

        ErrorLog ${APACHE_LOG_DIR}/error.log
        CustomLog ${APACHE_LOG_DIR}/access.log combined

        # For most configuration files from conf-available/, which are
        # enabled or disabled at a global level, it is possible to
        # include a line for only one particular virtual host. For example the
        # following line enables the CGI configuration for this host only
        # after it has been globally disabled with "a2disconf".
        #Include conf-available/serve-cgi-bin.conf
</VirtualHost>

Here we can see that the files DocumentRoot is set to /var/log/www. That means currently all the site editing is done within that folder, which is not accessible by regular user. So we need to redirect that to somewhere regular user can access these files, e.g. /home/user. First I want to make a folder for all the HTML files and we can make example index.html already. See also that I’m working without sudo command:

$ mkdir /home/user/public_html

$ emacs /home/user/public_html/index.html
<!doctype html>
<html>
<head>
        <meta charset="utf-8">
        <title>Topi's Virtual Name Host</title>
</head>
<body>
        <h1>Virtual host working!</h1>
</body>
</html>

Next we change 000-default.conf to look like this:

$ sudo emacs /etc/apache2/sites-enabled/000-default.conf
<VirtualHost *:80>
        # The ServerName directive sets the request scheme, hostname and port that
        # the server uses to identify itself. This is used when creating
        # redirection URLs. In the context of virtual hosts, the ServerName
        # specifies what hostname must appear in the request's Host: header to
        # match this virtual host. For the default virtual host (this file) this
        # value is not decisive as it is used as a last resort host regardless.
        # However, you must set it for any further virtual host explicitly.
        #ServerName www.example.com

        ServerAdmin [email protected]
        DocumentRoot /home/user/public_html

        # Available loglevels: trace8, ..., trace1, debug, info, notice, warn,
        # error, crit, alert, emerg.
        # It is also possible to configure the loglevel for particular
        # modules, e.g.
        #LogLevel info ssl:warn

        ErrorLog ${APACHE_LOG_DIR}/error.log
        CustomLog ${APACHE_LOG_DIR}/access.log combined

        # For most configuration files from conf-available/, which are
        # enabled or disabled at a global level, it is possible to
        # include a line for only one particular virtual host. For example the
        # following line enables the CGI configuration for this host only
        # after it has been globally disabled with "a2disconf".
        #Include conf-available/serve-cgi-bin.conf
</VirtualHost>

Here you can see that I changed the DocumentRoot to /home/user/public_html the folder we just made with the index.html we just made. After that change we can restart Apache2:

$ sudo systemctl restart apache2

Now if we try to connect the Apache website with our ip address the following site comes up:

403 Forbidden

And by looking at Apache’s error logs we can see the following in the end:

$ less /var/log/apache2/error.log
[Thu Feb 01 21:31:52.104694 2018] [authz_core:error] [pid 22772] [client IPADDRESS:PORT] AH01630: client denied by server configuration: /home/user/public_html/

Here we can see that we have received an authz_core:error in process id 22772. I’ve hidden the client’s ip address and port since in that part you can see where the connection came from and in this case it was from my mobile phone (of which I use as hotspot for internet). Lastly we can see the error code AH01630 and the description for that code. So this means that connections to this DocumentRoot we just made are not allowed. To change this we need to allow traffic to this location, which can be done with:

$ sudo emacs /etc/apache2/apache2.conf

There we need to add the following line to it:

.
.
.
<Directory /home/user/public_html>
        Require all granted
</Directory>
.
.
.

Now all traffic coming to that location is allowed. After that let’s restart Apache2 again:

$ sudo systemctl restart apache2

Now if we try to connect to our server we should see the index.html we made earlier:

Virtual host working!

And we can try to insert some text to it as a user:

$ emacs /home/topi/public_html/index.html
<!doctype html>
<html>
<head>
        <meta charset="utf-8">
        <title>Topi's Virtual Name Host</title>
</head>
<body>
        <h1>Virtual host working!</h1>
        <p>This is user added line</p>
</body>
</html>

And we should see that change in our website:

User addition

We can now see that making changes as a regular user is possible!

Python Flask + PostgreSQL + Apache mod WSGI

Last part was to test some new features of Python Flask, e.g. templates, database or forms, and then install Python Flash, PostgreSQL and Apache module WSGI and test each component and lastly whole combination. (This is two different assignments)

Installing Flask

Since we are still using my server here, which uses Ubuntu, we can use the apt to get Flask:

$ sudo apt-get install python3 ipython3 python3-flask

This installs Python 3, Python Flask for Python 3 and lastly IPython for Python 3, command shell for interactive computing. Next lets make a sample Flask project to our user’s home folder:

$ mkdir flask

$ emacs flask/hello.py

Lets add some minimal sample code to see if our Flask installation is working (the sample code is from Deploy Flask & Python3 on Apache2 & Ubuntu by Tero Karvinen):

from flask import Flask
app = Flask(__name__)

@app.route('/')
def hello_world():
    return 'Hello, World!\n\n'
	
if __name__ == '__main__':
 app.run(host='0.0.0.0', port=5000)

Here we run program that returns a simple string “Hello, World!” when the python file is called. After the Hello, World line, last lines in the code tells the program to keep running on port 5000 indefinitely. After that you can just save and exit file.

Now if we open a new terminal within my server and call the program with curl for example we should see the following:

$ curl http://localhost:5000
Hello, World!

Installing mod_wsgi

If you want to run the program on Apache we need to download specific Apache WSGI module for Python3. WSGI stands for Web Server Gateway Interface which is a specification for simple and universal interface between web servers and applications for the Python language. Read more about WSGI.

We can install this module with apt:

$ sudo apt-get install libapache2-mod-wsgi-py3

Next we need to make similar virtual host for our program as we made above, but now let’s make a whole new configuration for this file and make it default (again sample code from Deploy Flask & Python3 on Apache2 & Ubuntu by Tero Karvinen):

$ sudo emacs /etc/apache2/sites-available/hello.conf
<VirtualHost *>

	ServerName ip-addr

	WSGIDaemonProcess user user=user group=user threads=5
	WSGIScriptAlias / /home/user/flask/hello.wsgi

	<Directory /home/usr/flask/>
		WSGIProcessGroup hello
		WSGIApplicationGroup %{GLOBAL}
		WSGIScriptReloading On
		Require all granted
	</Directory>
</VirtualHost>

And make this default configuration by:

$ sudo a2dissite 000-default.conf

$ sudo a2ensite hello.conf

This disables the 000-default.conf and uses hello.conf instead.

Lastly restart Apache:

$ sudo systemctl restart apache2

Now when I try to connect to my server via ip address I get this error:

403 Forbidden

With error log:

$ less /var/log/apache2/error.log
[Fri Feb 02 00:27:13.473199 2018] [authz_core:error] [pid 26819] [client IPADDRESS:PORT] AH01630: client denied by server configuration: /home/usr

Here we can see that error is same what we had above. But here the problem is that I’ve mistyped the /home/usr which should be /home/user (Of course user being my real username). So I wen back to hello.conf and fixed the error:

$ sudo emacs /etc/apache2/sites-available/hello.conf
<VirtualHost *>

	ServerName ip-addr

	WSGIDaemonProcess user user=user group=user threads=5
	WSGIScriptAlias / /home/user/flask/hello.wsgi

	<Directory /home/user/flask/>
		WSGIProcessGroup hello
		WSGIApplicationGroup %{GLOBAL}
		WSGIScriptReloading On
		Require all granted
	</Directory>
</VirtualHost>

Here I changed <Directory /home/usr/flask/> to <Directory /home/user/flask/>. Now when I try to connect to my server with ip address I get this error:

Internal Server Error!

With the error log:

$ less /var/log/apache2/error.log
[Fri Feb 02 07:19:47.501115 2018] [wsgi:error] [pid 26909] [client IPADDRESS:PORT] No WSGI daemon process called 'hello' has been configured: /home/user/flask/hello.wsgi

I came back to this problem next morning. So here we can see [wsgi:error] on process id 26909 from my client with the description of misconfiguration of WSGI daemon process. So I went back to hello.conf:

$ sudo emacs /etc/apache2/sites-available/hello.conf
<VirtualHost *>

	ServerName ip-addr

	WSGIDaemonProcess user user=user group=user threads=5
	WSGIScriptAlias / /home/user/flask/hello.wsgi

	<Directory /home/user/flask/>
		WSGIProcessGroup hello
		WSGIApplicationGroup %{GLOBAL}
		WSGIScriptReloading On
		Require all granted
	</Directory>
</VirtualHost>

Here I saw that I had mistyped the WSGIDaemonProcess since it should WSGIDaemonProcess hello user=user group=user threads=5 instead of WSGIDaemonProcess user user=user group=user threads=5. So I fixed that and tried connection again and now it works:

$ curl http://localhost
Hello, World!

Installing PostgreSQL

Installing PostgreSQL can be done straight from your packages manager and since I’m using Ubuntu 16.04 on my server, I can use apt:

$ sudo apt-get update

$ sudo apt-get install postgresql

This installs all the dependencies needed for PostgreSQL. PostgreSQL can use your Linux username and password for authentication so you should make your database and username with the same name as your Linux user:

$ sudo -u postgres createdb user

$ sudo -u postgres createuser user

Open PostgreSQL by:

$ psql
user=> 

And now you’re in PostgreSQL. We can now test the working of PostgreSQL by doing simple CRUD-operations on it. PostgreSQL’s syntax differs a little bit from e.g. MySQL since in PostgreSQL we use backslash “" and different letters for commands. For example \h brings SQL reference and \?brings a list in less mode of different commands of PostgreSQL. SQL commands are pretty much the same as in MySQL with some things done slightly different:

Create tables

user=> CREATE TABLE bluesdudes (id SERIAL PRIMARY KEY, name VARCHAR(512));
CREATE TABLE

Here we can see that PostgreSQL uses SERIAL instead of INT AUTO_INCREMENT.

Insert data

user=>INSERT INTO bluesdudes(name) VALUES ("Eric Clapton");
ERROR:  column "Eric Clapton" does not exist
LINE 1: INSERT INTO bluesdudes(name) VALUES ("Eric Clapton");

Here we can see that PostgreSQL doesn’t allow use of double quotes around strings and this should be done with single quotes always:

user=> INSERT INTO bluesdudes(name) VALUES ('Eric Clapton');
INSERT 0 1
user=> INSERT INTO bluesdudes(name) VALUES ('Warren Haynes');
INSERT 0 1
user=> INSERT INTO bluesdudes(name) VALUES ('Buddy Guy');
INSERT 0 1

Read data

Reading data works the same way as in MySQL:

user=> SELECT * FROM bluesdudes;
 id |     name      
----+---------------
  1 | Eric Clapton
  2 | Warren Haynes
  3 | Buddy Guy
(3 rows)

Update data

This also works the same way as in MySQL:

user=> UPDATE bluesdudes SET name='John Lee Hooker' WHERE id=2;
UPDATE 1
user=> SELECT * FROM bluesdudes;
 id |      name       
----+-----------------
  1 | Eric Clapton
  3 | Buddy Guy
  2 | John Lee Hooker
(3 rows)

Delete data

Also same as in MySQL:

user=> DELETE FROM bluesdudes WHERE id=2;
DELETE 1
user=> SELECT * FROM bluesdudes;
 id |     name     
----+--------------
  1 | Eric Clapton
  3 | Buddy Guy
(2 rows)

So now our PostgreSQL works!

Testing Python Flask + PostgreSQL + Apache mod_wsgi

Last part was to test all three components together. Also in this part I’ll test the Flask’s new feature of templates. First we need to install some packages to get all three components working and again since I’m using Ubuntu 16.04 I can use apt for this:

$ sudo apt-get install python3-flask-sqlalchemy python3-psycopg2

Flask templates

I’ll change the hello.py file we made above so that it’ll with our PostgreSQL database we made above. So one of the new features of Python Flask are templates. Templates are built-in feature in Flask and should work from the box. First we need to make a folder for our templates:

$ mkdir ~/flask/templates

Flask should look for this folder automatically. Next we’ll make an example template (example code from Database Connection from Python Flask to Postgre, Using Plain SQL by Tero Karvinen):

$ emacs ~/flask/templates/base.html
<!doctype html>
<html>
<head>
 <title>
 { % block title % }
 Hello World
 { % endblock title % }
 </title>
 <meta charset="utf-8" />
</head>
<body>
 { % block contents % }
 <h1>Hello World</h1>
 <p>Let's test UTF-8 with "päivää"</p>
 { % endblock contents % }
</body>
</html>

Note: I inserted extra space between the curly braces ({}) and percent sign (%), since Jekyll interprets them as Ruby code and wouldn’t present them as sample code.

Now we need to change the hello.py file so it uses this template:

$ emacs ~/flask/templates/hello.py
from flask import Flask, render_template
app = Flask(__name__)

@app.route('/')
def hello_world():
    return render_template("base.html")

if __name__ == '__main__':
 app.run(host='0.0.0.0', port=5000)

Here we changed that code uses flask.render_template which then renders our base.html template. Notice that we also need to import flask.render_template for it to work. Since we enabled WSGIScriptReloading above we can refresh our page to run the new command with:

$ touch hello.wsgi

Otherwise it’ll just run the old code. Now we can test and see if our site runs the new code:

$ curl localhost
<!doctype html>
<html>
<head>
 <title>
 
 Hello World
 
 </title>
 <meta charset="utf-8" />
</head>
<body>
 
 <h1>Hello World</h1>
 <p>Let's test UTF-8 with "päivää"</p>
 
</body>

And it seems to be working.

Database Connection from Flask to PostgreSQL:

Next we could make a template for our SQL with loop.

$ emacs ~/templates/bluesdudes.html
{ % extends "base.html" % }

{ % block title % } Blues Dudes { % endblock title % }

{ % block contents % }
 <h1>Blues Dudes</h1>
 { % for bluesdude in bluesdudes % }
 <p>{ { bluesdude.name } }</p>
 { % endfor % }
{ % endblock contents % }

Here we made a template that extends from base.html and loops over our data in bluesdudes table. Next we need to configure our hello.py so it’ll communicate with our PostgreSQL. For this part I also drop our earlier made tables from the database by:

$ psql
psql (9.5.10)
Type "help" for help.

user=> DROP TABLE bluesdudes;
DROP TABLE
user=> \d
No relations found.
user=> \q
$ emacs ~/flask/hello.py

Following code is an example from Database Connection from Python Flask to Postgre, Using Plain SQL by Tero Karvinen:

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
db = SQLAlchemy(app)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql:///user'
app.config['SECRET_KEY'] = 'your-secret-key'

def sql(rawSql, sqlVars={}):
 "Execute raw sql, optionally with prepared query"
 assert type(rawSql)==str
 assert type(sqlVars)==dict
 res=db.session.execute(rawSql, sqlVars)
 db.session.commit()
 return res

@app.before_first_request
def initDBforFlask():
 sql("CREATE TABLE IF NOT EXISTS bluesdudes (id SERIAL PRIMARY KEY, name VARCHAR(512) UNIQUE);")
 sql("INSERT INTO bluesdudes(name) VALUES ('B.B. King') ON CONFLICT (name) DO NOTHING;")
 sql("INSERT INTO bluesdudes(name) VALUES ('Albert King') ON CONFLICT (name) DO NOTHING;")
 sql("INSERT INTO bluesdudes(name) VALUES ('Freddie King') ON CONFLICT (name) DO NOTHING;")
 sql("INSERT INTO bluesdudes(name) VALUES ('Muddy Waters') ON CONFLICT (name) DO NOTHING;")

@app.route('/')
def hello_world():
    return render_template("base.html")

@app.route("/bluesdudes")
def bluesdudes():
 bluesdudes=sql("SELECT * FROM bluesdudes;")
 return render_template("bluesdudes.html", bluesdudes=bluesdudes)

if __name__ == "__main__":
 from flask_sqlalchemy import get_debug_queries
 app.run(debug=True)

Here the code contacts your user’s PostgreSQL with Python’s SQL Toolkit called SQLAlchemy. Next it defines the procedure for preventing SQL injection with prepared query. After that it makes table bluesdudes with four different entries, if there isn’t table with the same name or entries with the same name. Next the code tells that when connecting to http://ip-address it renders the template base.html we made earlier. After that, code tells that when connecting to http://ip-address/bluesdudes it’ll render the template bluesdudes.html we also made earlier. So lets see if it works by connecting to http://ip-address/bluesdudes:

Bluesdudes

And it seems to be working!