---
# System prepended metadata

title: SA Homework4 Checklist
tags: [SA]

---

# SA Homework4 Checklist
###### tags: `SA`

## Setup
### Installation
```shell
sudo pkg install -y php73 php73-{extensions,zip,mbstring,GD,zlib,curl,openssl,pecl-imagick-im7,intl,fileinfo,pecl-APCu} nginx mysql80-server curl wget
sudo pkg install -y php73-pdo_mysql
```

#### /etc/rc.conf
```shell
php_fpm_enable="YES"
nginx_enable="YES"
mysql_enable="YES"
firewall_enable="YES"
firewall_type="open"
```
You may need to reboot.

### PHP config
```shell
sudo cp /usr/local/etc/php.ini{-production,}
```

#### /usr/local/etc/php.ini
```
cgi.fix_pathinfo=0
expose_php=Off     # hide php version
```

#### /usr/local/etc/php-fpm.d/www.conf
```
listen.owner = www
listen.group = www
listen.mode = 0660
;listen = 127.0.0.1:9000
listen = /var/run/php-fpm.sock
```

### Nginx config
Edit `/usr/local/etc/nginx/nginx.conf`.

```nginx
user  www;
worker_processes  auto;

events {
    worker_connections  1024;
}

http {
    include            mime.types;
    default_type       application/octet-stream;
    sendfile           on;
    keepalive_timeout  65;
    server_tokens      off;

    server {
        listen         80;
        server_name    hyperbola.nctu.me;
        return 301     https://$server_name$request_uri;
    }

    server {
        listen         443 ssl http2 default_server;
        server_name    hyperbola.nctu.me;
        root           /usr/local/www/hyperbola.nctu.me;

        ssl_certificate             /etc/ssl/hyperbola.nctu.me.pem;
        ssl_certificate_key         /etc/ssl/hyperbola.nctu.me.key;
        ssl_protocols               TLSv1 TLSv1.1 TLSv1.2;
        ssl_prefer_server_ciphers   on;
        ssl_ciphers                 ECDH+AESGCM:ECDH+AES256:ECDH+AES128:DH+3DES:!ADH:!AECDH:!MD5;
        ssl_dhparam                 /etc/ssl/dhparam.pem;
        ssl_session_cache           shared:SSL:40m;
        ssl_session_timeout         4h;
        ssl_session_tickets         on;
        add_header                  Strict-Transport-Security "max-age=31536000" always;

        access_log     /var/log/nginx/hyperbola.nctu.me.log;
        error_log      /var/log/nginx/hyperbola.nctu.me.err.log;
        index          index.html index.htm index.php;

        location / {
            try_files $uri $uri/ =404;
        }

        error_page   500 502 503 504  /50x.html;
        location = /50x.html {
            root   /usr/local/www/nginx-dist;
        }

        #
        location ~ \.php$ {
            try_files      $uri =404;
            fastcgi_pass   unix:/var/run/php-fpm.sock;
            fastcgi_index  index.php;
            fastcgi_param  SCRIPT_FILENAME $request_filename;
            include        fastcgi_params;
        }
    }
}

```

## Spec
- [x] Setup name-based virtual host.
- [x] Show different content when connecting using domain name/IP.

### Private page
- [x] Place a webpage on `https://{your-intranet-IP}/private`.
- [x] When accessing from intranet, it must show after passing. Basic Auth authentication with username `admin` and password `your-student-ID`.
- [x] Access from public network should be denied (return 403) .

### Information hidden
- [x] Hide NGINX/Apache version in header (5%).
```nginx
server_tokens off;
```

### HTTPS
https://www.digitalocean.com/community/tutorials/how-to-create-a-self-signed-ssl-certificate-for-nginx-in-ubuntu-16-04

- [x] Enable HTTPS (5%). Self-signed certificate is allowed.
```shell
sudo openssl req -x509 -nodes -days 365 -newkey rsa:2048 \
-keyout /etc/ssl/hyperbola.nctu.me.key \
-out /etc/ssl/hyperbola.nctu.me.pem \
-subj '/CN=hyperbola.nctu.me'
sudo openssl dhparam -out /etc/ssl/dhparam.pem 2048
```

```nginx
ssl_certificate             /etc/ssl/hyperbola.nctu.me.pem;
ssl_certificate_key         /etc/ssl/hyperbola.nctu.me.key;
ssl_protocols               TLSv1 TLSv1.1 TLSv1.2;
ssl_prefer_server_ciphers   on;
ssl_ciphers                 ECDH+AESGCM:ECDH+AES256:ECDH+AES128:DH+3DES:!ADH:!AECDH:!MD5;
ssl_dhparam                 /etc/ssl/dhparam.pem;
ssl_session_cache           shared:SSL:40m;
ssl_session_timeout         4h;
ssl_session_tickets         on;
```

- [x] Supply `–k` option when testing with `curl`.
- [x] Redirect to HTTPS automatically when attempting to connect to HTTP.
```nginx
server {
    listen         80;
    server_name    hyperbola.nctu.me;
    return 301     https://$server_name$request_uri;
}
```

- [x] Enable HSTS.
```nginx
add_header Strict-Transport-Security "max-age=31536000" always;
```

- [x] Enable HTTP2 on pages connected with HTTPS. Can be tested with `curl --http2`. Ensure that the server only provides ciphers not "blacklisted" by HTTP2.
```nginx
listen 443 ssl http2;
```

## PHP/PHP-FPM
:::warning
Use PHP7 or higher
:::

- [x] Set up PHP such that access to `https://{yourdomain}/info-{your-student-ID}.php` gives response of `phpinfo()` (3%).
- [x] Hide PHP version information in header (2%).
```
expose_php=Off     # edit /usr/local/etc/php.ini
```

## MySQL
### Setup
```shell
sudo sysrc mysql_enable="YES"
sudo service mysql-server start
sudo mysql_secure_installation
```

### Spec
- [x] Set the transaction isolation levels to READ-COMMITTED (3%). Bonus: Explain why this and other isolation levels mean (+5%). 
```sql
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@global.transaction_isolation;
```

[資料庫交易的 Isolation](https://medium.com/getamis/database-transaction-isolation-a1e448a7736e)：
* Read Uncommitted: 代表 transaction 可以讀到別的 transaction 尚未 commit 的資料，在這個等級中三個問題都沒有解決。
* Read Committed: 代表 transaction 只能讀到別的 transaction 已經 commit 的資料，沒有 commit 的話就不會讀到，在這個等級解決了 Dirty Read 的問題。
* Repeatable Read: 代表每次 transaction 要讀取特定欄位的資料時，只要 query 條件相同，讀取到的資料就會相同。在這個等級解決了 Non-repeatable reads 的問題。
* Serializable: 代表在多個 transaction 同時執行時，只要 transaction 的順序相同時，得到的結果一定相同。比如說 Transaction A 先執行了接下來再執行 Transaction B，在同樣的條件下，每次執行都會得到一樣的結果。在這個等級下連同 Phantom reads 也會一併被解決。

- [x] Create a MySQL user named `nc`. The password of the user is your student ID (3%). This user can only login from localhost (2%).
```sql
CREATE USER 'nc'@'localhost' identified with mysql_native_password by '0716023';
FLUSH PRIVILEGES;
SELECT host, user, authentication_string FROM mysql.user;
```

- [x] Create a database named `nextcloud`.
```sql
CREATE DATABASE nextcloud;
SHOW DATABASES;
```

- [x] User `nc` only have full privileges on database `nextcloud` (2%).
```sql
GRANT ALL PRIVILEGES ON nextcloud.* TO 'nc'@'localhost';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'nc'@'localhost';
```

## HTTP Applications
You can have only one file index.php in the root at the
path `https://{your-domain}/app`.
 - [x] `https://{your-domain}/app` displays `route: /`.
 - [x] `https://{your-domain}/app/{A}+{B}` displays `result: {A+B}`.
 - [x] `https://{your-domain}/app?name={string}` displays `Hello, {string}`.

```php                                                            
$uri = substr($_SERVER['REQUEST_URI'], 4); // remove prefix /app
if (isset($_GET['name'])) {
    echo "Hello, $_GET[name]";
}
else if (preg_match('/^\\/[0-9]+\\+[0-9]+$/', $uri)) {
    $ploc = strpos($uri, "+");
    $a = substr($uri, 1, $ploc-1);
    $b = substr($uri, $ploc + 1);
    $sum = $a + $b;
    echo "result: $sum";
}
else {
    echo "route: $uri";
}
```

## WebSocket
* [Simple DEMO](https://medium.com/@cn007b/super-simple-php-websocket-example-ea2cd5893575)
* This program crashes at client refresh, so just use it for test or demo.
* You can use other WebSocket program you want.
* You don’t need to support HTTP2 for WebSocket connection
* You can use HTTP for this path if you cannot make it connected on port 443.

### Steps
```nginx
http {
    upstream ws {
        server localhost:12345;
    }
    
    server {
        server_name hyperbola.nctu.me;
        # ......
        
        location /wsdemo/ {
            proxy_pass http://ws;
            proxy_http_version 1.1;
            proxy_set_header Upgrade $http_upgrade;
            proxy_set_header Connection "upgrade";
        }
    }
}
```

### SPEC
- [x] Connect WebSocket on port other than 80, 443 (ws://) (3%)
- [x] Connect WebSocket on port 80 (ws://) (+3%)
- [x] Connect WebSocket on port 443 (wss://) (+4%)

## Nextcloud
* You can either install by pkg (v16) or download the latest version (v17).
* Install on path `https://{your-domain}/nextcloud`.

### Insallation
```shell
sudo pkg install -y php73-{zip,mbstring,GD,zlib,curl,openssl}
cd /path/to/root/of/nextcloud
sudo wget https://download.nextcloud.com/server/releases/nextcloud-17.0.2.zip
sudo tar xf nextcloud-17.0.2.zip
sudo chown -R www:www nextcloud
sudo rm xf nextcloud-17.0.2.zip
```

#### Post-installation
```shell
cd /path/to/root/of/nextcloud
## ensure that mysql-server is running
sudo -u www ./occ -y db:convert-filecache-bigint 
```

### Nginx config
Setup [nginx config](https://docs.nextcloud.com/server/15/admin_manual/installation/nginx.html).
```nginx
user  www;
worker_processes  auto;

events {
    worker_connections  1024;
}

http {

    upstream php-handler {
        server unix:/var/run/php-fpm.sock;
    }
    
    include            mime.types;
    default_type       application/octet-stream;
    sendfile           on;
    keepalive_timeout  65;
    server_tokens      off;

    server {
        listen         80;
        server_name    hyperbola.nctu.me;
        return 301     https://$server_name$request_uri;
    }

    server {
        listen         443 ssl http2 default_server;
        server_name    hyperbola.nctu.me;
        root           /usr/local/www/hyperbola.nctu.me;

        ssl_certificate             /etc/ssl/hyperbola.nctu.me.pem;
        ssl_certificate_key         /etc/ssl/hyperbola.nctu.me.key;
        ssl_protocols               TLSv1 TLSv1.1 TLSv1.2;
        ssl_prefer_server_ciphers   on;
        ssl_ciphers                 ECDH+AESGCM:ECDH+AES256:ECDH+AES128:DH+3DES:!ADH:!AECDH:!MD5;
        ssl_dhparam                 /etc/ssl/dhparam.pem;
        ssl_session_cache           shared:SSL:40m;
        ssl_session_timeout         4h;
        ssl_session_tickets         on;

        access_log     /var/log/nginx/hyperbola.nctu.me.log;
        error_log      /var/log/nginx/hyperbola.nctu.me.err.log;
        index          index.html index.htm index.php;
        
        add_header X-Content-Type-Options nosniff;
        add_header X-XSS-Protection "1; mode=block";
        add_header X-Robots-Tag none;
        add_header X-Download-Options noopen;
        add_header X-Permitted-Cross-Domain-Policies none;
        add_header Referrer-Policy no-referrer;
        add_header X-Frame-Options "SAMEORIGIN";
        add_header Strict-Transport-Security "max-age=31536000" always;
        fastcgi_hide_header X-Powered-By;

        location = /.well-known/carddav {
            return 301 $scheme://$host:$server_port/remote.php/dav;
        }
    
        location = /.well-known/caldav {
            return 301 $scheme://$host:$server_port/remote.php/dav;
        }

        client_max_body_size    512M;
        fastcgi_buffers         64 4K;
        gzip                    on;
        gzip_vary               on;
        gzip_comp_level         4;
        gzip_min_length         256;
        gzip_proxied            expired no-cache no-store private no_last_modified no_etag auth;
        gzip_types              application/atom+xml application/javascript application/json application/ld+json application/manifest+json application/rss+xml application/vnd.geo+json application/vnd.ms-fontobject application/x-font-ttf application/x-web-app-manifest+json application/xhtml+xml application/xml font/opentype image/bmp image/svg+xml image/x-icon text/cache-manifest text/css text/plain text/vcard text/vnd.rim.location.xloc text/vtt text/x-component text/x-cross-domain-policy;
        
        location / {
            rewrite ^ /index.php;
        }

        location ~ ^\/(?:build|tests|config|lib|3rdparty|templates|data)\/ {
            deny all;
        }
        location ~ ^\/(?:\.|autotest|occ|issue|indie|db_|console) {
            deny all;
        }

        location ~ ^\/(?:index|remote|public|cron|core\/ajax\/update|status|ocs\/v[12]|updater\/.+|oc[ms]-provider\/.+)\.php(?:$|\/) {
            fastcgi_split_path_info ^(.+?\.php)(\/.*|)$;
            set $path_info $fastcgi_path_info;
            try_files $fastcgi_script_name =404;
            include fastcgi_params;
            fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
            fastcgi_param PATH_INFO $path_info;
            fastcgi_param HTTPS on;
            fastcgi_param modHeadersAvailable true;
            fastcgi_param front_controller_active true;
            fastcgi_pass php-handler;
            fastcgi_intercept_errors on;
            fastcgi_request_buffering off;
        }

        location ~ ^\/(?:updater|oc[ms]-provider)(?:$|\/) {
            try_files $uri/ =404;
            index index.php;
        }

        # Adding the cache control header for js, css and map files
        # Make sure it is BELOW the PHP block
        location ~ \.(?:css|js|woff2?|svg|gif|map)$ {
            try_files $uri /index.php$request_uri;
            add_header Cache-Control "public, max-age=15778463";
            access_log off;
        }

        location ~ \.(?:png|html|ttf|ico|jpg|jpeg|bcmap)$ {
            try_files $uri /index.php$request_uri;
            access_log off;
        }
    }
}
```

### Spec
- [x] Each user in Nextcloud can put static(PHP is not needed) contents(img, html, css, js, etc.) in the `public_html` directory under their home.
- [x] When accessing `https://{your-domain}/sites/~{username}/` , it should show whatever {username} put in his `public_html`, with index index.html.
#### Fix warnings
- [x] Fix all the warnings in Settings/Overview page.
* Edit `/usr/local/etc/php-fpm.d/www.conf`
```
env[PATH] = /usr/local/bin:/usr/bin:/bin
```

* Edit `/usr/local/etc/php.ini`
```
memory_limit = 1G
```

* Edit `/path/to/nexcloud/config/config.php`
```
'memcache.local' => '\OC\Memcache\APCu'
```