WebServiceProxyDriver

About

The Zeos WebServiceProxy driver is a driver that doesn't connect to a specific database. It is an internal API proxy for Zeos. Using this driver you can build a bridge across the internet between your Zeos based application and any database that is supported by Zeos. It uses http and https as its transport protocols, so it should be able to get through most firewalls. Also it doesn't rely on a constant connection but rather uses session IDs. So a change of the client IP address or a connection loss don't close the session.

Pro's

• Can connect nearly any Zeos supported database to the internet
• can connect any mobile device to your database without requiring a client library.
• can add 2FA to the database access. 2FA is checked before a connection to the database is created.
• Is a Pascal only implementation, so no client library is used
• Can encrypt the connection by using TLS
• Doesn't loose the connection even if the client is suspended, disconnected for a time or changes its IP address.
• more to come, I am sure...

Con's

• is another layer that might break.
• it uses SOAP and XML - so it suffers from data bloat by XML.
• not all metadata functions are implemented yet
• currently no support for stored procedures, generators and events.
• XML doesn't allow to encode control characters besides TAB, Line Feed and Carriage Return. So other characters cannot be transported in Strings and CLOBs.
• more to come, I am sure...

The server

About

The current implementation is an http server that listens on port 8000. It uses the FPC http server units. Since the server is based on the web service toolkit, other server implementations can be used (indy, Synapse, apache module). This allows a wide variety of choices. The current implementation is designed for Lazarus 2.0.6 and FPC 3.0.4 only. There is no Delphi implementation.

configuration

Upon startup the server reads the zeosproxy.ini in its own directory (windows) or /etc/zeosproxy.ini (linux). The first section that is read is the [general] section. Here the values for database prefix and security module prefix are read. Every section of this ini file starting with the database prefix is treated as a database configuration name, that a client can connect to. The following values can be set in the [general] section:

key	description
Database Prefix	This key allows setting of a database prefix, that distinguishes database configurations from other sections. The default ist db.
Security Prefix	This key allows setting of a security prefix, that distinguishes security configurations from other sections. The default ist sec.
Listening Port	This key allows to set the port that the Proxy server listenes on. The default value is 8000.
IP Address	This key allows to set the IP address that the server binds to. The default value is 127.0.0.1. A value of 0.0.0.0 allows to listen on all IP addresses of the host.
Connection Idle Timeout	From time to time the server cleans up connections. Connections that have not been used for this time will be closed. The timeout is specified in seconds. The default value is 86400 which is one day.
use ssl	If enabled, SSL is used.
host name	If SSL is enabled, this sets the host name to use for SSL.
Certificate File	Sets the certificate file to use for SSL.
Key File	Specifies the key file to use for SSL.
Key Password	if the key fiule is encrypted, the key password can be set here.

Connections

Sections whose name start with the database prefix will be read as describing a database that users can connect to. The following keys can be used inside these sections:

key	description
ClientCodepage	This key allows setting the client that the server uses to connect to the database. Only UTF8 is officially supported currently.
Database	This is the database name to connect to on the server. The meaning depends on the driver.
LibraryLocation	This value specifies the client library to use.
Port	This property specifies the destination port of the server database, if supported at all.
Protocol	This property specifies the Zeos driver to use for accessing the database. I.e. firebird or ASA or mysql or ...
Properties	this is a list of properties that usually goes into the TZConnection.Properties property. The delimiter between the properties is the ; character.

Security Modules

Sections starting with the security prefix describe additional security modules that can be used to add additional protection layers to the username / password authentication of a database, like one time passwords. Without a security module the server will simply pass the user name and password that were supplied by the client and pass them on to the server. If a security module is specified, username and password are given to that security module for additional checks and possible modifications before they are passed on to the server.

Each security module section has a key named type that tells the server what kind of security module to use. The following values are valid for the type key:

Value	Description
yubiotp	This module allows to check one time passwords that are generated by the Yubikeys, which are myade by Yubico.
totp	This module allows to check time based one time passwords as described in RFC 6238 and used by Google Authenticator and others.
integrated	This module allows to check passwords that were generated using the MD5Crypt algorithm and are stored in the database that will be used.
chained	This security module allows to combine several security modules into a chain. A username / password combination must be authenticated by each one of these modules.
alternate	This security module allows to authenticate a user either by one security module or another security module.
ldap	This security module allows to verify a users password against an LDAP server.

The yubiotp security module

This module allows to check one time passwords that are generated by the Yubikeys, which are myade by Yubico. The Yubikey password can be concatenated with a user password. In that case the module expects the Yubikey OTP to be at the end of the password. The Yubikey OTP will be used to authenticate the user and the remaining password (without the Yubikey OTP) will be returned to be used for server authentication or for the use of other security modules.
The following parameters can be set in sections that configure a yubiotop security module:

key	description
Yubikeys File	The file that contains the assignments of yubikeys to users. Each line lists one username and one or many yubikeys that are assigned to these users. A colon is used as the delimiter between the username and the yubikeys.
Add Database To Username	In some configurations it might be viable to not only differentiate users by their user name but also on the database that they want to get access to. If this option is set to true, user names will be a concatenation of the client user name, the Database Separator (usually the @ symbol) and the connection name as listed in the ini file.
Database Separator	The separator that gets used, if the Add Database To Username option is set. The default value is the @ character.
Base URL	The base URL used for authentication. For Yubicos public servers this would be https://api.yubico.com/wsapi/2.0/verify, which also is the default value of this option.
Client ID	The client ID to use when communication with the authentication server. This ID can be used to check the signature of the servers response.
Secret Key	The secret key that gets used by Yubico to sign the response. The default value for this option is an empty string. If a value is provided, the module will verify the signature of the response and fail the authentication if the signature is invalid.
Yubikey SQL	An SQL statment that the module can use to check if the supplied user name and yubikey can be used. The SQL must have two parameters where the first parameter gets filled with the public identity of the yubikey and the second parameter gets filled with the user name.
DB User	The user name to use when checking the public identity against the database.
DB Password	The password to use when checking the public identity against the database.
Replacement User Column	If this column name gets set, the user name that gets used for the authentication against subsequent modules or the database will be replaced by the value in the supplied column upon succesful yubikey lookup.

The TOTP security module

This module allows to check time based one time passwords as described in RFC 6238 and used by Google Authenticator and others. The OTP can be combined with a user password. In that case the module expects the user password to be first in the password string and the OTP to be the last six characters in the password.
The following parameters can be set in sections that configure a TOTP security module:

key	description
Secrets File	This key has to be set to the name of the file containing the secrets for the users to be authenticated. Each line contains the name of a user and a Base32 encoded secret delimited by a :. This secret will get used to check the TOTP.
Add Database To Username	In some configurations it might be viable to not only differentiate users by their user name but also on the database that they want to get access to. If this option is set to true, user names will be a concatenation of the client user name, the Database Separator (usually the @ symbol) and the connection name as listed in the ini file.
Database Separator	The separator that gets used, if the Add Database To Username option is set. The default value is the @ character.

Notes about generating TOTP secrets and distributing them

With TOTP security is dependent on how well protected the secret is. Anybody who gets to know the secret can generate the correct TOTPs.

• Most probably these secrets should be generated with a cryptographically secure pseudorandom number generator (CSPRNG). The only CSPRNG that I found for Pascal is the AES-256 based Cryptographically Secure Pseudo-Random Number Generator (CSPRNG) by Synopse which is part of mORMot.
• When distributing these secrets it makes sense to uses Googles Key Uri Format as described in their (archived) wiki on Github.
• It makes sense to generate QR codes for these URIs. In Delphi and FPC this can be done using the
  Zint Barcode Generator for Delphi .
• A sample implementation for generating secrets and URIs can be found at https://github.com/marsupilami79/TOTPSecretGenerator4Pascal

The integrated security module

This module allows to check passwords that are stored in the database that will be used. The password will be extracted from the database using a custom set SQL query. This query is expected to return the users hashed password in the first column of the first returned row. The pasword can be hashed using either the MD5Crypt algorithm or the PostgreSQL variant of hashing passwords with MD5.

The following parameters can be set in sections that configure an integrated security module:

key	description
DB User	The user name to use when connecting to the database.
DB Password	The password to use when connecting to the database.
Replacement User	If this value is set, the username and password that were supplied to the security module get replaced by Replacement User and Replacement password if the authentication finishes successful. This allows to authenticate users but have all users conenct to the database with the same username and password. This is considered bad style.
Replacement Password	If Replacement User is set, this value will get used to replace the user supplied password before authenticating to the database.
Password SQL	This parameter specifies the SQL command to use for retrieving a users hashed password from the database. The username will be supplied as the one and only parameter. The parameter has to be supplied as a question mark. Example: select password from users where lower(username) = lower(?)
Add Database To Username	PostgreSQL has the option to determine the correct username by appending the database name to the user name (aka the db_user_namespace feature). When migrating passwords from such an instance, this option has to be set to true. If this option is set to true, user names will be a concatenation of the client user name, the @ symbol and the database name as listed in the ini file when calculating the user name for PostgreSQL type password checks. If the user name already contains an @ no concatenation will be done. If the user name ends with an @, the @will be removed for the password calculation. The user name that gets used for the database lookup will still be the user name without the appended database name. If the username contains an @ before the concatenation, nothing will be done and the user name will be used as is for password calculation.

The chained security module

This security module allows to combine several security modules into a chanin. A username / password combination must be authenticated by each one of these security modules. Its configuration consists of only one parameter, which lists the ini file sections to read for the chained security modules.

key	description
Module List	This key lists the security module configurations to use from the very same ini file. Names are to be used without the security prefix. The delimiter for this list is the komma (,).

The alternate security module

This security module allows to have two modules as alternatives to each other.
So if one of two modules authenticates a user this suffices for the login to be allowed.
Its configuration consists of only one parameter, which lists the ini file sections to read for the alternate security modules.

key	description
Module List	This key lists the security module configurations to use from the very same ini file. Names are to be used without the security prefix. The delimiter for this list is the komma (,).

The LDAP security module

This security module allows to contact an LDAP server for user authentication.
The module checks wether the user can login to the LDAP server.
If the login is successful, it can do a search with a filter to check wether the user is allowed to use the service.
Currently it doesn't support SSL, which should be trivial to add since synapse already supports it.

This module isn't compiled by default because it requires the synapse framework to be available.
It has to be enabled in the dbcproxy.inc file by enabling the ENABLE_LDAP_SECURITY define.

key	description
Host Name	The name of the LDAP host to query.
User Name Mask	This allows to add striungs to the user name. The default is %s which will just generate a string with the username. For extending a user name with the domain name, set it to yourdomainname\:s.
User Lookup Expression	An expression that can be used to check a users membership in groups. The Server will run an LDAP search using this expression. If one or meore objects are returned, access is granted. Again :s gets replaced by the user name. This parameter defaults to (sAMAccountName=%s). If it is left empty, the check will not be performed an the user will be allowed to log in. A group membership can be tested with an expression like (&(sAMAccountName=%s)(memberOf=CN=somegroup,CN=Users,DC=contonso,DC=com)).
Base DN	The base DN that gets used for the LDAP search. It should be set to something like DC=contonso,DC=com.
Replacement User	If this value is set, the username that was supplied to the security module gets replaced by Replacement User if the authentication finishes successful. This allows to authenticate users but have all users conenct to the database with the same username and password. This is considered bad style.
Replacement Password	If This parameter is set, this value will get used to replace the user supplied password before authenticating to the database.

The client (Web Service Proxy)

About

By default the driver is compiled to use a separate client library. This library is intended to separate all the web service stuff from the Zeos driver. This way we can compile a working driver without relying on web service support by the compiler. The client library contains the web service proxy and all the units that are needed to do the networking stuff. Once the data is transferred, it is delivered to the Zeos driver that does the rest of the work.
The external Web Service Proxy is programmed using Lazarus 2.0.6 and FPC 3.0.4. It should be simple to make a working Delphi version. Currently that is not a goal though.
For Delphi there also is an internal client / web service proxy. So a client library might not be needed. Your mileage my vary when it comes to using it. The external proxy in the client library will always be guaranteed to work. The internal proxy may fail to compile on older Delphi versions or inherit bugs from Delphis SOAP implementation.

Configuration

The client doesn't need any specific configuration. It gets configured by the Zeos driver.

The Zeos driver "WebServiceProxy"

About

The WebServiceProxy driver contains the necessary implementations for Zeos. If necessary, it loads the client library or connects to the server using the internal proxy. It currently has no configuration parameters. Also the ports canot be set.
To connect to to a server, specify its name or IP address in the HostName propert of TZConnection. The Database specifies which connection configuration the server should load from its INI file when the connection is created. UserName and Password are sent to the server for logging into the target database.

Compilation

Currently the driver is disabled by default. It can be enabled by disabling the define {$DEFINE ZEOS_DISABLE_PROXY} in the Zeos.inc file. The driver is only known to compile on Delphi XE 7. It should compile on any newer Delphi version too.
The internal proxy, which allows the driver to work without an external DLL file, can be enabled by enabling the {$DEFINE USE_INTERNAL_PROXY}' conditinal compilation directive in the file src\plain\ZPlainProxyDriver.pas.

Design Goals

• Use SOAP over HTTP for a number of reasons:
  • using the design tool from the WebService Toolkit it allows for easy API generation
  • using the WebService Toolkit, all the Web-stuff is taken care of and doesn't need extra coding
  • Delphi has support for SOAP based web serices in nearly all versions
  • Exceptions are easily transported from the server to the client. This greatly reduces error possibilities. It's kinda a safecall API for the web ;)
• Use XML and simple string lists as ancodings beside the SOAP transport. XML parsers as well as string lists exist in all Delphi versions as well as in FPC. This should make the server as well as the client less error prone because no additional parsers need to be written.
• The basic driver should compile on all versions and editions of Delphi and FPC without the need for additional components. This requires the creation of a client library.
• On Windows: The client library is to use safe mechanisms for communication with the driver. This means: Use safecall and WideString - at least on Windows, even though they have drawbacks.
• There idea is to have only a minimum of API round trips. Result sets are to be transferred in one SOAP API round trip. All metadata regarding the result set are encoded in one go. No additional round trips are to be done between the server and the client.

Running the server as a service / daemon

Linux with systemd (most modern distributions)

• compile the ZeosProxyService.lpi that can be found in src/webservice/server/service
• place the resulting ZeosProxyService executable in /usr/local/bin
• create a configuration file as /etc/zeosproxy.ini
• copy the zeos-proxy-server.service file to /lib/systemd/system
• run systemctl enable zeos-proxy-server to enable the service to be automatically started on startup
• run systemctl start zeos-proxy-serverto start the server

Windows

• compile the ZeosProxyService.lpi that can be found in src\webservice\server\service
• place the resulting ZeosProxyService.exe at any place you like
• create a ZDbcProxy.ini in the same directory
• run ZeosProxyService.exe --install to register it as a service in Windows
• run net start TZeosProxyDaemonto start the service