2.1.1 Processors

SZTPD runs as a single asynchronous I/O event driven process. It is ideal to dedicate a whole core to the sztpd process, thus, in addition to the operating system, the machine should have at least two processors.

2.1.2 Memory

SZTPD uses significant memory resources¹. The following sections consider SZTPD’s constant and transitive memory demands.

2.1.3 Filesystem

Unless SZTPD is configured to use a file-based database, SZTPD has effectively no interaction with the filesystem, all configuration and logs are stored in the database.

That said, a filesystem must exist if the connection to the database is to be encrypted, as the requisit certificates are passed into the SZTPD executable as command line parameters as file paths. Additionally, it is expected that SZTPD’s stdout/stderr will be piped to a file (e.g., /var/log/sztpd.log).

When using a file-based database, the database file grows proportionally to the size of the configuration plus operational state (e.g., audit logs, bootstrapping logs, etc.). Sizing numbers for this haven’t been measured yet.

2.1.4 Network Interfaces

SZTPD’s networking demands vary by interface, as different APIs are presented for northbound administrators and southbound devices.

While the Northbound API is exclusively a programmatic interface, it is expected to be used to drive human-facing interfaces and thus the speed of the network interface may matter.

The Southbound API is used exclusively for machine-to-machine interactions (i.e., SZTPD and the bootstrapping devices), and thus the speed of the network interface need only be fast enough to not cause timeouts. That said, the devices access the southbound interface when they are bootstrapping, which typically occurs immediately when being powered-up for the first time, which typically entails a human (the installer) manually plugging in a power cable. In some use cases, the installer will be instructed to wait for the device to provide some indication that the bootstrapping process has completed (e.g., an LED pattern, audible sound, etc.). From this perspective only, the speed of the network interface may matter².

2.3.1 Listening Ports and the APIs They Present

SZTPD can listen to a multiplicity of ports and, for each such interface, can present a multiplicity of its APIs. Each port may be bound to a source address and/or source port, enabling integration into virtual network interfaces presented by the host operating system.

The protocol running on each port can be configured to be “HTTP” or “HTTPS”.

If a port is configured to use “HTTP”, it is only intended so that the TLS termination can be offloaded to a device fronting SZTPD (e.g., a firewall). That said, SZTPD cannot know or enforce that TLS was performed, so care must be taken to not accidentally expose an unprotected port to a untrusted network.

When “HTTP” is used, SZTPD may be configured with information about the external endpoint so that, e.g., when SZTPD sends callback information to remote peers (e.g., links in account activation emails), it can address the callback to the external endpoint instead of its local endpoint.

The APIs that may be selected for each listening port are:

2.3.2 Variations in Listening Ports

While the above suggests numerous possible combinations, some common options include:

2.3.3 Outbound Connections

SZTPD initiates outbound connections for the reasons discussed in this section.

2.4.1 Data in Motion

All of SZTPD’s APIs are presented as mutually-authenticated HTTPS connections ⁸.

Two factor authentication (client certificate + password) may be configured.

Passwords, when used, may be required to have a minimal length⁹.

Each request sent from clients is tested against access control and a corresponding entry is recorded in the audit log.

2.4.2 Data at Rest

SZTPD hashes passwords used to authenticate clients, but otherwise leaves it to the administrator to implement database-level encryption.

Passwords are hashed when configured for both administrators and devices. For instance, a clear-text input password value, such as $0$<secret> is hashed, using SHA-256 and stored in the database as a value like $5$rounds=<rounds>$<salt>$<hash>.

However, passwords used to authenticate to a remote system (e.g., an SMTP server) are not hashed when, as the cleartext password is needed in such cases. These password may be encrypted in a future release (e.g., as a symmetric-key in the [Keystore]).

For these reasons, database-level encryption is recommended.

2.5.1 Persistence Selection

SZTPD persists all data¹⁰ into the database provided on the command line (i.e., the database URL passed into the sztpd command). Varying the database URL enables use of different databases. Currently supported databases include Postgres, MariaDB, and SQLite.

sqlite:///:memory:

sqlite:///relative/path/to/sztpd.db     (unix)
sqlite:////absolute/path/to/sztpd.db    (unix)
sqlite:///C:\path\to\sztpd.db        (windows)

<engine>://<user>:<passwd>@<host>:<port>/<database-name>

   postgresql://sztpd-admin:secret@db.example.com:5432/sztpd-db 
   mysql+pymysql://sztpd-admin:secret@db.example.com:3306/sztpd-db

2.5.2 First-time Initialization

The first time SZTPD runs¹¹ the server will detect that the database is uninitialized and hence prompt for:

• Contract acceptance
• Desired mode selection.

<Non-Production Use Contract>

First time initialization. Please accept the license terms.

By entering "Yes" below, you agree to be bound to the terms and conditions contained on this screen with Watsen Networks.

Please enter "Yes" or "No": <SZTPD waits for input here>

Mode: 
  1 - single-tenant
  x - multi-tenant

Mode: <SZTPD waits for input here>

+--rw devices
   +--rw device* [serial-number]
      +--rw serial-number     string
      +-- ... // additional device parameters here.

+--rw preferences
   +--rw device-ownership-verification!
   |  +-- ... // ownership verification parameters here
   +--rw plugins
      +--rw plugin* [name]
         +-- // plugin parameters here

+--rw device-types
  +--rw device-type* [name]
     +-- // device-type parameters here
    
+--rw tenants
   +--rw tenant* [name]
      +--rw name                    string
      +--rw device
         +--rw device* [serial-number]
            +--rw serial-number     string
            +-- ... // additional device parameters here.

2.5.3 Defaults

The follow sections describe defaults the freshly installed system uses.

[Note: '\' line endings per RFC 8792]

$ export SZTPD_DEFAULT_ADDR="A.B.C.D"; \
  export SZTPD_DEFAULT_PORT="9090"; \
  sztpd --key my-pkcs12.pem --cacert rdbms-cacert.pem postgresql://user:pass@localhost:5432/sztpd

2.5.4 TLS Connection to an RDBMS

$ sztpd --key my-pkcs12.pem --cacert rdbms-cacert.pem postgresql://user:pass@localhost:5432/sztpd

2.5.5 Daemonize

The sztpd executable should be executed as a daemon, gracefully starting and stopping with the host system. Many systems use “rc” scripts located in /etc/rc.d or the like for this purpose.

2.5.6 Signals

The sztpd executable responds the SIGNALS as follows:

• SIGHUP: SZTPD shuts down all ports and restarts.
• any other signal: SZTPD shuts down and exits.

Notably, SZTPD automatically sends itself a SIGHUP signal whenever the system-level [Transport] configuration is updated.

2.5.7 User Privileges

Unless SZTPD is asked to open a listening port below port number 1024, the sztpd does not require any special user privileges (other than be able to read any input file and write any output files), and hence it is recommended to run the sztpd executable using an unprivileged user account. Note that the sztpd executable does not itself drop user privileges.

2.5.8 Dedicated Cores

SZTPD is a single-threaded asynchronous event-driven executable. While the CPU demand has yet to be measured, the demand is not expected to be excessive.

It is ideal if the sztpd process is locked down to a core, thus avoiding delays introduced from process swapping. This means that the machine should have at least two core, at least on other being for the underlying operating system.

2.5.9 Output

The sztpd process does not produce any log files, all audit logs and bootstrapping logs are held within the database layer (see Persistence Selection).

The sztpd process itself may emit output to STDOUT and or STDERR. For instance, if an unexpected condition is encountered, a Python stack trace is generated.

It is recommended to pipe the sztpd process’s output to a file. For instance:

sztpd sqlite:///:memory: >> /var/logs/sztpd.log 2>&1

3.1.1 Data at Rest Encryption

Security features available in MySQL 8 are transparent data encryption (TDE), audit plugin, undo, redo and binlog encryption, caching_sha2_password, roles and password management. In addition, OS and cloud security provided by Amazon to be implemented are VPC, IAM, and security groups. There are numerous resources on how to install MySQL/MariaDB compiled with TLS support and to configure a database instance to use encrypted connections. See:

• https://dev.mysql.com/doc/refman/8.0/en/faqs-security.html
• https://dev.mysql.com/doc/refman/8.0/en/faqs-tablespace-encryption.html
• https://mariadb.com/kb/en/data-at-rest-encryption-overview/
• https://mariadb.com/kb/en/library/innodb-encryption-overview/
• https://mariadb.com/kb/en/library/encryption-key-management/

3.1.2 Data in Transit Encryption

By default, MySQL transmits data between the server and clients without encrypting it. This is acceptable when the server and client run on the same host or in networks where security is guaranteed through other means. However, in cases where the server and client exist on separate networks or they are in a high-risk network, the lack of encryption introduces security concerns. Following details how to create configure an RDBMS instance for TLS.

Here SZTPD server acts like a MySQL client; SZTPD is going to use the client certificate to secure client-side connectivity. You must install the following files on all of your clients including the web server according to these links:

• https://mariadb.com/kb/en/library/securing-connections-for-client-and-server
• https://dev.mysql.com/doc/refman/5.5/en/using-encrypted-connections.html
• https://www.cyberciti.biz/faq/how-to-setup-mariadb-ssl-and-secure-connections-from-clients

For the purpose of this tutorial:

ssl_cert = /etc/my.cnf.d/certificates/server-cert.pem
ssl_key = /etc/my.cnf.d/certificates/server-key.pem
ssl_ca = /etc/my.cnf.d/certificates/ca.pem

See https://dev.mysql.com/doc/refman/8.0/en/faqs-tablespace-encryption.html and A.17.14 for how to change, rotate, and rekey the master encryption key.

Let’s Get Started.

For more information on how to set up and connect to a MySQL database engine, see https://dev.mysql.com/doc/mysql-getting-started/en/#mysql-getting-started-connecting.

3.1.3 Database Server Parameters

MySQL default setup for InnoDB engine contains only one tablespace called the system tablespace whose identifier is 0. More tablespace can be created indirectly using the innodb_file_per_table=ON configuration parameter. A tablespace consists of a chain of files. The size of the files does not have to be divisible by the database block size. Data files are dynamically extended, but redo log files are pre-allocated.

To support highly scalable systems, enabling the innodb_file_per_table option would make available other MySQL features such as table compression and transportable tablespace. Since InnoDB engine caches both data and index pages, initially, set the InnoDB buffer pool value to 70-80% of available memory. You or your DBA will want to change other parameters and resize the buffer based on memory requirement and usage later. For cloud database service, follow your cloud providers recommendation, such as AWS’s recommended best practices for their RDS or Aurora MySQL.

• https://docs.aws.amazon.com/en_pv/AmazonRDS/latest/UserGuide/CHAP_MariaDB.html
• https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/CHAP_AuroraOverview.html

For version MySQL 8.0 and later, the default character set and collation are UTF8MB4 and utf8mb4_0900_ai_ci. If you use an earlier release, please ensure your database character set and collation are set for UTF8MB4 and utf8mb4_0900_ai_ci.

By default, log files are not enabled in MySQL thus all errors will be shown in the syslog.(/var/log/syslog). Therefore to minimize disk I/O and read/write contention, when you enable logging, place log files on different disks from swap files, temporary tablespace files, data files, and index files. Remember to rotate log files.

$ cat /usr/local/etc/my.cnf

# This group is read both both by the client and the server
# use it for options that affect everything
[client-server]

# include all files from the config directory
!includedir /usr/local/etc/my.cnf.d
!includedir /usr/local/bin
                          
port         = 3306       
socket       = /tmp/mysql.sock
#/var/run/mysql.sock

[mysqld_safe]
nice         = 0
socket       = /tmp/mysql.sock
log_error    = /usr/local/var/log/mysql_error.log

[mysqld]
log_error    = /usr/local/var/log/mysql_error.log
log          = /usr/local/var/log/mysql.log
binlog-do-db = zero

# To enable General Query Log
#general_log_file        = /usr/local/var/log/mysql.log
#general_log             = 1

# To enable Slow Query Log
slow_query_log  = /usr/local/var/log/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

default-storage-engine  = innodb
innodb_buffer_pool_size = 2G 
innodb_file_per_table   = 1

ssl_cert = /usr/local/etc/my.cnf.d/server-cert.pem
ssl_key = /usr/local/etc/my.cnf.d/server-key.pem
ssl_ca = /usr/local/etc/my.cnf.d/ca.pem
require_secure_transport=ON


[mariadb]
plugin-load-add=file_key_management
file_key_management_filename=/mnt/usb/secret.txt
file_key_management_encryption_algorithm=AES_CTR
innodb-encrypt-tables
innodb-encrypt-log
innodb-encryption-threads = 4
plugin-load-add=file_key_management
file_key_management_filename=/mnt/usb/secret.txt
file_key_management_encryption_algorithm=AES_CTR

Start MySQL server and note that any MySQL server error log will be output to mysql_error.log file.

$ mysqld

Or

$ mysql.server start

Restart MySQL server after changes have been made.

$ mysql.server restart

Or to stop

$ mysql stop

Once, you have a database instance running, open a MySQL client by typing the following command and enter your MySQL root password when prompted:

$ mysql -u root -p   

If you haven’t set a password for your MySQL root user, you can omit the -p switch.

While in the MySQL shell monitor, you can obtain the status of your connection and various databases as follows:

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

mysql> status;

mysql  Ver 8.0.18 for osx10.14 on x86_64 (Homebrew)
Connection id:          14
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.18 
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /tmp/mysql.sock
Uptime:                 8 days 21 hours 17 min 57 sec

The next step is to create an administrative user login with the minimum privileges required to create other user accounts and define their privileges (CREATE USER and GRANT). For security purposes, we recommend changing MySQL root password and creating an administrative user account, as well a dedicated user account for SZTPD with minimal privileges. The next few steps entail creating a dedicated schema owner/user account and password. Here in this demonstration, we use my_user and my_pass respectively. Note that a user account in MySQL consists of user name and host name or host IP address.

mysql> show grants;
mysql> create user my_user@'%' identified by 'my_pass’ REQUIRE SSL password expire;
mysql> create user my_user@localhost identified by 'my_pass’ REQUIRE SSL password expire;

To grant access from another host, change the host name part (localhost). To create a user that can connect from any host, use the ‘%’ wildcard as a host part.

mysql> grant select on mysql.* to my_user;
mysql> grant select on mysql.* to my_user@localhost;
mysql> grant all privileges on sztpd.* to my_user@'%';
mysql> grant all privileges on sztpd.* to my_user@localhost;

Finally, reload the privileges from the grant tables in the mysql system database.

mysql> flush privileges;

Commonly used privileges are:

• ALL PRIVILEGES – grants all privileges to a user account.
• CREATE – to create databases and tables.
• DROP - to drop databases and tables.
• DELETE - to delete rows from a specific table.
• INSERT - to insert rows into a specific table.
• SELECT – to read a database.
• UPDATE - to update table rows.

Now, let’s try to connect to MySQL with that newly created user:

$ mysql --ssl-mode=REQUIRED -u my_user -p  -h localhost

To reset password:

mysql> set password=’new_password’;

To check for user grants:

mysql> show grants for my_user@'%’;
+---------------------------------------------------+
| Grants for my_user@%                              |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `my_user`@`%`               |
| GRANT SELECT ON `mysql`.* TO `my_user`@`%`        |
| GRANT ALL PRIVILEGES ON `sztpd`.* TO `my_user`@`%`|
+---------------------------------------------------+

Verifying for TLS support:

mysql> status
Current user:   my_user@localhost
SSL:            Cipher in use is DHE-RSA-AES128-GCM-SHA256

mysql> show variables like '%ssl%';

+--------------------+-----------------------------------------+
| Variable_name      | Value                                   |
+--------------------+-----------------------------------------+
| have_openssl       | YES                                     |
| have_ssl           | YES                                     |
| ssl_ca             | /usr/local/etc/my.cnf.d/ca.pem          |
| ssl_capath         |                                         |
| ssl_cert           | /usr/local/etc/my.cnf.d/server-cert.pem |
| ssl_cipher         |                                         |
| ssl_crl            |                                         |
| ssl_crlpath        |                                         |
| ssl_fips_mode      | OFF                                     |
| ssl_key            | /usr/local/etc/my.cnf.d/server-key.pem  |
+--------------------+-----------------------------------------+

mysql> show global variables like 'tls_version'';

+---------------+-------------------------------+
| Variable_name | Value                         |
+---------------+-------------------------------+
| tls_version   | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
+---------------+-------------------------------+

To verify your user account has been created and grants are given successfully, exit and login back in MySQL Shell as the new user. MySQL will prompt for a password.

$ mysql> quit
$ mysql -u my_user -p

Install MySQL connector

$ pip install mysql-connector

In another OS shell, start the SZTPD server for the first time. This will populate the schema/database ‘sztpd’ with tables and indexes. If you run the SZTPD server again, it will skip schema creation because sztpd schema already exists.

Finally, verify that your sztpd schema was installed successfully by switch to sztpd database schema and list the tables:

mysql> show databases; 
mysql> use sztpd;                         
mysql> show tables;
+-----------------+
| Tables_in_sztpd |
+-----------------+
| aa              |
| al              |
| bb              |
| bb2             |
| bbi             |
| cc              |
| cc2             |
| co              |
| cr              |
| dbl             |
| drm             |
| drmmm           |
| kaa             |
| kaacc           |
| kss             |
| obi             |
| oo              |
| pdvv            |
| pdvvmm          |
| pnww            |
| rb              |
| singletons      |
| sp              |
| sp2             |
| tc              |
| tcc             |
| tle             |
| tlehrccc        |
| tlehrccc2       |
+-----------------+

3.2.1 Postgres Encryption Options

• https://www.postgresql.org/docs/current/encryption-options.html

3.2.2 Enabling TLS communication between PostgreSQL database and SZTPD server

To enable TLS 1.2 with server certificate validation, edit Postgres server configuration parameters in the postgresql.conf file, which specifies server behavior with regards to auditing, authentication, encryption, and other behaviors. The postgresql.conf file usually resides in the data directory under your installation:

password_encryption = scram-sha-256     # md5 prior to 10 or scram-sha-256 post version 10

# - SSL -
ssl = on
ssl_ca_file = ''
ssl_cert_file = 'server.crt'
ssl_crl_file = ''
ssl_key_file = 'server.key'
ssl_ciphers = 'TLSv1.2:!aNULL' #'HIGH:MEDIUM:+3DES' or TLSv1.3 
               or a list of ciphers but TLSv1.2 is a safe bet
ssl_prefer_server_ciphers = on
pg_ctl reload

If you have a Postgres release 11.4, set ssl_ciphers to TLSv1.2.

PostgreSQL release 12 contains two new server settings (ssl_min_protocol_version and ssl_max_protocol_version) that are are used to control the oldest (minimum) and newest (maximum) version of the SSL and TLS protocol family that the server will accept. Set these to TLSv1.2 and TLSv1.3 respectively.

Client authentication is controlled by a configuration file, which is named pg_hba.conf and is stored in the database data directory. Make sure tcp localhost connections are enabled in pg_hba.conf and modify your pg_hba.conf file to use scram-sha-256 algorithm. For more information, see

- https://www.postgresql.org/docs/12/auth-pg-hba-conf.html

 TYPE   DATABASE    USER    ADDRESS    METHOD
local   all         all                scram-sha-256

Procure the Certificate Authority (CA) signed certificate for the PostgreSQL database from the system administrator of your organization. Ensure that the certificate is in x509 format. For example, postgres.crt. Save the procured certificate file in the following locations:

SZTPD Server: /secure PostgresSQL Engine Server: /secure

• https://info.crunchydata.com/blog/how-to-upgrade-postgresql-passwords-to-scram
• https://github.com/MagicStack/asyncpg/blob/master/asyncpg/protocol/scram.pyx#L263

To start a PostgresSQL shell client connecting to the default Postgres database, type:

 $ psql postgres 
 psql (12)
 Type "help" for help.

You can start the server from a specific directory. To do this use the command and substitute in for the specified values:

pg_ctl -D [Data Directory] -l [Log file] start

The “Data Directory” refers to the directory that was just initialized. The “Log file” is a file that will record server events for later analysis. Generally log files are formatted to contain the date in the file name (e.g. “2018-05-27.log” or “myData-logfile-2018-05-27.log”) and should be stored outside of the database that they are logging so as to avoid unnecessary risks.

The server will only start if the port is free. If the default server is running it must first be stopped using command:

pg_ctl -D /usr/local/var/postgres stop

Once started, the database instance can be connected using an open source admin and development tool such as pgAdmin or simply a PostgreSQL shell:

$ psql —help
Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "kristen")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)

$ psql postgres

Once inside PostgresSQL shell, create a user account with a password and permission to create a database, as follow:

postgres=> CREATE USER my_sztpd WITH LOGIN ENCRYPTED PASSWORD 'my_pass’ CREATEDB;

For more details, see https://www.postgresql.org/docs/9.1/sql-createrole.html

Exit out and login back in to verify that the user was created successfully

postgres=>exit
$ psql sztpd -U my_sztpd
postgres=conninfo
You are connected to database "sztpd" as user "my_sztpd" via socket in "/tmp" at port "5432".

Then you can start the SZTPD server, which creates a schema and populate seed data for the tables.
While the SZTPD server is running and waiting for client connections, you will want to verify that the schema and tables have been created successfully.

sztpd=> dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | kristen
 sztpd  | my_user