$Revision: 6692 $
Copyright 2002-2024 Fred Toussi. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license. Additional permission is granted to the HSQL Development Group to distribute this document with or without alterations under the terms of the HSQLDB license.
2024-10-25
Table of Contents
As described in the Running and Using HyperSQL chapter, network listeners (servers) provide connectivity to catalogs from different JVM processes. The HyperSQL listeners support both ipv4 and ipv6 network addressing.
This is the preferred way of running a database server and the fastest one. This mode uses the proprietary hsql: communications protocol. The following example of the command for starting the server starts the server with one (default) database with files named "mydb.*" and the public name (alias) of "xdb". Note the database property to set the transaction mode to MVCC is appended to the database file path.
java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:mydb;hsqldb.tx=mvcc --dbname.0 xdb
Alternatively, a server.properties
file can be
used for passing the arguments to the server. This file must be located
in the directory where the command is issued.
java -cp ../lib/hsqldb.jar org.hsqldb.server.Server
Alternatively, you can specify the path of the
server.properties
file on the command line. In this
case, the properties file can have any name or extension, but it should
be a valid properties file.
java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --props myserver.props
Use the --help argument to see the list of available arguments.
java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --help
The contents of the server.properties
file is
described in the next section.
This method of access is used when the computer hosting the database server is restricted to the HTTP protocol. The only reason for using this method of access is restrictions imposed by firewalls on the client or server machines and it should not be used where there are no such restrictions. The HyperSQL HTTP Server is a special web server that allows JDBC clients to connect via HTTP. The server can also act as a small general-purpose web server for static pages.
To run an HTTP server, replace the main class for the server in the example command line above with the following:
java -cp ../lib/hsqldb.jar org.hsqldb.server.WebServer
The contents of the server.properties
file is
described in the next section.
This method of access also uses the HTTP protocol. It is used when
a separate servlet engine (or application server) such as Tomcat or
Resin provides access to the database. The Servlet Mode cannot be
started independently from the servlet engine. The
Servlet
class, in the HSQLDB jar, should be
installed on the application server to provide the connection. The
database is specified using an application server property. Refer to the
source file
src/org/hsqldb/server/Servlet.java
to see the details.
Both HTTP Server and Servlet modes can only be accessed using the JDBC driver at the client end. They do not provide a web front end to the database. The Servlet mode can serve only a single database.
Please note that you do not normally use this mode if you are using the database engine in an application server. In this situation, connections to a catalog are usually made in-process, or using an external HSQL Server instance.
Properties files for running the servers are not created
automatically. You should create your own files that contain
server.property=value
pairs for
each property. The server.properties
or
webserver.properties
files must be located in the
directory where the command to run the
org.hsqldb.server.Server
class is issued.
In all properties files, values are case-sensitive. All values apart
from names of files or pages are required in lowercase (e.g.
server.silent=FALSE
will have no
effect, but server.silent=false
will work). Supported properties and their default values (if any) are as
follows:
Table 15.1. common server and webserver properties
Value | Default | Description |
---|---|---|
server.database.0 | file:test | the catalog type, path and file name of the first database file to use |
server.dbname.0 | "" | lowercase server alias for the first database file |
server.database.n | NO DEFAULT | the catalog type, path and file name of the n'th database file in use |
server.dbname.n | NO DEFAULT | lowercase server alias for the n'th database file |
server.silent | true | no extensive messages displayed on console |
server.trace | false | JDBC trace messages displayed on console |
server.address | NO DEFAULT | IP address of server |
server.tls | false | Whether to encrypt network stream. If this is set to
true , then in normal situations you will also
need to set properties
system.javax.net.ssl.keyStore and
system.javax.net.ssl.keyStorePassword , as
documented elsewhere. The value of server.tls
impacts the default value of
server.port . |
server.daemon | false | Whether the server is run as a daemon |
server.remote_open | false | Allows opening a database path remotely when the first connection is made |
In HyperSQL version 2.0, each server can serve an unlimited number of databases simultaneously. The server.database.0 property defines the filename / path whereas the server.dbname.0 defines the lowercase alias used by clients to connect to that database. The digit 0 is incremented for the second database and so on. Values for the server.database.n property can use the mem:, file: or res: prefixes and connection properties as discussed under CONNECTIONS. For example,
database.0=mem:temp;sql.enforce_strict_size=true;
Properties or default values specific to
server.properties
are:
Table 15.2. server properties
Value | Default | Description |
---|---|---|
server.port | 9001 (normal) or 554 (if TLS
encrypted) | TCP/IP port used for talking to clients. All databases are served on the same port. |
server.no_system_exit | true | no System.exit() call when the database
is closed |
Properties or default values specific to
webserver.properties
are:
Table 15.3. webserver properties
Value | Default | Description |
---|---|---|
server.port | 80 (normal) or 443 (if TLS
encrypted) | TCP/IP port used for talking to clients |
server.default_page | index.html | the default web page for server |
server.root | ./ | the location of served pages |
.<extension> | NO DEFAULT | multiple entries such as .html=text/html
define the mime types of the static files served by the web
server. See the source for
src/org/hsqldb/server/WebServer.java for a
list. |
An example of the contents of a
server.properties
file is given below:
server.database.0=file:/opt/db/accounts server.dbname.0=accounts server.database.1=file:/opt/db/mydb server.dbname.1=enrolments server.database.2=mem:adatabase server.dbname.2=quickdb
In the above example, the server.properties
file indicates that the server provides access to 3 different databases.
Two of the databases are file based, while the third is all in memory. The
aliases for the databases that the users connect to are
accounts
, enrolments
and
quickdb
.
All the above properties and their values can be specified on the
command line to start the server by omitting the
server.
prefix. If a property/value pair is specified
on the command line, it overrides the property value specified in the
server.properties
or
webserver.properties
file.
Note | |
---|---|
Upgrading: If you have existing custom properties files, change the values to the new naming convention. Note the use of digits at the end of server.database.n and server.dbname.n properties. |
If you want to start the server from within your application, as
opposed to the command line or batch files, you should create an instance
of Server or Web Server, then assign the properties and start the Server.
An working example of this can be found in the org.hsqldb.test.TestBase
source. The example below sets the same properties as in the
server.properties
file example.
HsqlProperties p = new HsqlProperties(); p.setProperty("server.database.0","file:/opt/db/accounts"); p.setProperty("server.dbname.0","an_alias"); // set up the rest of properties // alternative to the above is Server server = new Server(); server.setProperties(p); server.setLogWriter(null); // can use custom writer server.setErrWriter(null); // can use custom writer server.start();
To shut down the server, you can execute the SQL "SHUTDOWN"
statement on the server databases. When you start the server from your
application and keep a reference to the Java Server object, you can also
shut it down programatically. Calling the shutdownCatalogs(int
shutdownMode)
method of
org.hsqldb.server.Server
closes all the open
databases, which results in server shutdown. The parameter value is
normally 1, which indicates normal shutdown. Other modes of shutdown, such
as SHUTDOWN IMMEDIATELY are also supported. See the javadoc for
org.hsqldb.server.Server
. See the example
below:
server.shutdownCatalogs(1);
The Server object has several alternative methods for setting databases and their public names. The server should be shutdown using the shutdown() method.
If the server.remote_open
property is true, the
Server works differently from the normal mode. In this mode, it is not
necessary to have any databases listed as server.database.0 etc. in the
Server startup properties. If there are databases listed, they are opened
as normal. The server does not shutdown when the last database is
closed.
In this mode, a connection can be established to a database that is not open or does not exist. The server will open the database or create it, then return a connection to the database.
The connection URL must include the path to the database, separated
with a semicolon from the alias. In the example below, the database path
specified as file:C:/files/mydatabase
is opened and the
database alias xdb
is assigned to the database. After
this, the next connection to the specified alias will connect to the same
database. Any database path on the URL is ignored if the alias is serving
a database.
The database path can point to a file: or mem: database.
If you use database properties on the URL, these properties are used
when the new database is created. If no database properties are used on
the URL, you can also specify the path with
filepath=<path>
. Examples below:
Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb;file:C:/files/mydatabase", "SA", ""); Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb;mem:test;sql.enforce_types=true", "SA", ""); Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb;filepath=file:C:/files/mydatabase", "SA", "");
Each database started by a Server has its own URL. When new databases are created by the server, the database properties for each of the new database can be appended to the database URL. Examples below:
// example in server.propertie file server.database.0=file:/opt/db/accounts;hsqldb.default_table_type=cached;sql.enforce_names=true server.dbname.0=accounts // example for setting the property programatically HsqlProperties p = new HsqlProperties(); p.setProperty("server.database.0","file:/opt/db/accounts;hsqldb.default_table_type=cached;sql.enforce_names=true");
The specified properties apply only to a new database. They have no
effect on an existing database apart from a few properties such as
readonly
listed in the Properties
chapter.
This section explains how to encrypt the stream between JDBC network clients and HyperSQL Listeners. If you are running an in-process (non-Listener) setup, this chapter does not apply to you.
Hsqldb TLS Support Requirements
Java 4 and greater versions support JSSE.
A JKS keystore containing a private key, in order to run a Listener.
If you are running the listener side, then you'll need to run a HSQLDB Server or WebServer Listener instance. It doesn't matter if the underlying database catalogs are new, and it doesn't matter if you are making a new Listener configuration or encrypting an existing Listener configuration. (You can turn encryption on and off at will).
You need a HSQLDB jar file that was built with JSSE present. If you obtained your HSQLDB distribution from us, you are all set, because we build with Java 1.4 or later (which contains JSSE).
At this time, only 1-way, server-cert encryption is tested.
Just use one of the following protocol prefixes.
Hsqldb TLS URL Prefixes
jdbc:hsqldb:hsqls://
jdbc:hsqldb:https://
The latter will only work for clients running with Java 1.4 or later.
If the listener you wish to connect to is using a certificate approved by your default trust keystore, then there is nothing else to do. If not, then you need to tell Java to "trust" the server cert. (It's a slight over-simplification to say that if the server certificate was purchased, then you are all set; if somebody "signed their own" certificate by self-signing or using a private ca certificate, then you need to set up trust).
First, you need to obtain the cert (only the "public" part of it). Since this cert is passed to all clients, you could obtain it by writing a Java client that dumps it to file, or perhaps by using openssl s_client. Since in most cases, if you want to trust a non-commercial cert, you probably have access to the server keystore, I'll show an example of how to get what you need from the server-side JKS keystore.
You may already have an X509 cert for your server. If you have a server keystore, then you can generate a X509 cert like this.
Example 15.1. Exporting certificate from the server's keystore
keytool -export -keystore server.store -alias existing_alias -file server.cer
In this example, server.cer
is the
X509 certificate that you need for the next step.
Now, you need to add this cert to one of the system trust
keystores or to a keystore of your own. See
the Customizing Stores section in JSSERefGuide.html to see
where your system trust keystores are. You can put private keystores
anywhere you want to. The following command will add the cert to an
existing keystore, or create a new keystore if
client.store
doesn't exist.
Example 15.2. Adding a certificate to the client keystore
keytool -import -trustcacerts -keystore trust.store -alias new_alias -file server.cer
If you are making a new keystore, you probably want to start
with a copy of your system default keystore which you can find
somewhere under your JAVA_HOME
directory (typically
jre/lib/security/cacerts
for a JDK, but I forget
exactly where it is for a JRE).
Unless your OS can't stop other people from writing to your files, you probably do not want to set a password on the trust keystore.
If you added the cert to a system trust store, then you are
finished. Otherwise you will need to specify your custom trust
keystore to your client program. The generic way to set the trust
keystore is to set the system property
javax.net.ssl.trustStore
every time that you
run your client program. For example
Example 15.3. Specifying your own trust store to a JDBC client
java -Djavax.net.ssl.trustStore=/home/blaine/trust.store -jar /path/to/hsqldb.jar dest-urlid
This example runs the program SqlTool. SqlTool has built-in TLS
support however, so, for SqlTool you can set
truststore
on a per-urlid basis in the SqlTool
configuration file.
Note: The hostname in your database URL must match the
Common Name of the server's certificate exactly.
That means that if a site certificate is admc.com
,
you cannot use jdbc:hsqldb:hsqls://localhost
or
jdbc:hsqldb:hsqls://www.admc.com:1100
to connect to
it.
If you want more details on anything, see JSSERefGuide.html on
Sun's site, or in the subdirectory
docs/guide/security/jsse
of your Java SE
docs.
Get yourself a JKS
keystore containing a private key. Then set properties
server.tls
,
system.javax.net.ssl.keyStore
and
system.javax.net.ssl.keyStorePassword
in your
server.properties
or
webserver.properties
file. Set
server.tls
to true
,
system.javax.net.ssl.keyStore
to the path of the
private key JKS keystore, and
system.javax.net.ssl.keyStorePassword
to the
password (of both the keystore and the private key record-- they must
be the same). If you specify relative file path values, they will be
resolved relative to the ${user.dir}
when the JRE
is started.
Caution | |
---|---|
If you set any password in a .properties (or any other) file, you need to restrict access to the file. On a good operating system, you can do this like so: chmod 600 path/to/server.properties |
The values and behavior of the system.*
settings above match the usage documented for
javax.net.ssl.keyStorePassword
and
javax.net.ssl.keyStore
in the JSSE docs.
Note | |
---|---|
Before version 2.0, HyperSQL depended on directly setting the corresponding JSSE properties. The new idiom is more secure and easier to manage. If you have an old password in a UNIX init script config file, you should remove it. |
There are two main ways to do this. Either you can use a certificate signed by a certificate authority, or you can make your own. One thing that you need to know in both cases is, the Common Name of the cert has to be the exact hostname that JDBC clients will use in their database URL.
I'm not going to tell you how to get a CA-signed SSL certificate. That is well documented at many other places.
Assuming that you have a standard pem-style private key
certificate, here's how you can use openssl and the program
DERImport
to get it into a JKS keystore.
Because I have spent a lot of time on this document already, I am just giving you an example.
Example 15.4. Getting a pem-style private key into a JKS keystore
openssl pkcs8 -topk8 -outform DER -in Xpvk.pem -inform PEM -out Xpvk.pk8 -nocrypt openssl x509 -in Xcert.pem -out Xcert.der -outform DER java DERImport new.keystore NEWALIAS Xpvk.pk8 Xcert.der
Important | |
---|---|
Make sure to set the password of the key exactly the same as the password for the keystore! |
You need the program DERImport.class
of
course. Do some internet searches to find
DERImport.java
or
DERImport.class
and download it.
If DERImport has become difficult to obtain, I can write a program to do the same thing-- just let me know.
Run man keytool
or see
the Creating a Keystore section of JSSERefGuide.html.
If you are on UNIX and want to automatically start and stop a
Server or WebServer running with encryption, set the
system.javax.net.ssl.keyStore
and
system.javax.net.ssl.keyStorePassword
properties as
instructed above, and follow the instructions in the HyperSQL on UNIX chapter, paying close
attention to the TLS-related comments in the template config
file.
If you are using a private server certificate, make sure to also set the trust store filepath for relevant urlids in your RC file, as explained in the sample config file.
JDBC connections will always be denied if the supplied user and password are not found in the target catalog. But an HyperSQL listener can also restrict access at the listener level, even protecting private catalogs which have insecure (or default) passwords. If you have an in-process setup, this section of the Guide doesn't apply to you.
Many (in fact, most) distributed database applications don't have application clients connect directly to the database, but instead encapsulate access in a controlling process. For example, a web app will usually access the data source on behalf of users, with end-user web browsers never accessing the database directly. In these cases and others, the security benefits of restricting listener access to specific source addresses is well worth the effort. ACLs work by restricting access according to the source address of the incoming connection request. This is efficient because the database engine never even gets the request until it is approved by the ACL filter code.
The sample file sample/acl.txt
in your HyperSQL
distribution explains how to write an ACL file.
# $Id: acl.txt 536 2008-12-05 14:55:10Z unsaved $ # Sample HyperSQL Network Listener ACL file. # Specify "allow" and "deny" rules # For address specifications, individual addresses, host names, and # network addresses with /bit suffix are allowed, but read the caveat about # host names below, under the sample "localhost" rule. # Blank lines ignored. # Lines with # as the first non-whitespace character are ignored. allow 2001:db8::/32 # Allow this 32-bit ipv4 subnet allow localhost # You should use numerical addresses in ACL files, unless you are certain that # the name will always be known to your network address resolution system # (assume that you will lose Internet connectivity at some time). # With a default name resolution setup on UNIX, you are safe to use names # defined in your /etc/hosts file. deny 192.168.101.253 # Deny a single IP address. # In our example, 192.168.101.0/24 is our local, organizational network. # 192.168.101.253 is the IP address of our Intern's PC. # The Intern does not have permission to access our databases directly. allow 192.168.101.0/24 # Any ipv4 or ipv6 candidate address not matched above will be denied
You put your file wherever it is convenient for you, and specify that path
with the property server.acl
or
webserver.acl
in your
server.properties
or
webserver.properties
file (depending on whether your
listener instance is a Server
or
WebServer
). You can specify the ACL file path with
an absolute or relative path. If you use a relative path, it must be
relative to the .properties
file. It's often
convenient to name the ACL file acl.txt
, in the same
directory as your .properties
file and specify the
property value as just acl.txt
. This file name is
intuitive, and things will continue to work as expected if you move or
copy the entire directory.
Warning | |
---|---|
If your |
When you edit your ACL file, it is both more convenient and more secure to test it as explained here before activating it. You could, of course, test an ACL file by editing it in-place, then trying to connect to your listener with JDBC clients from various source addresses. Besides being mightily laborious and boring, with this method it is very easy to accidentally open access to all source addresses or to deny access to all users until you fix incorrect ACL entries.
The suggested method of creating or changing ACLs is to work with an
inactive file (for new ACL files, just don't enable the
*.acl
property yet; for changing an existing file, just
copy it to a temporary file and edit the temporary file). Then use the
ServerAcl
class to test it.
Example 15.5. Validating and Testing an ACL file
java -cp path/to/hsqldb.jar org.hsqldb.server.ServerAcl path/to/acl.txt
If the specified ACL file fails validation, you will be given
details about the problem. Otherwise, the validated rules will be
displayed (including the implicit, default deny rules). You then type in
host names and addresses, one-per-line. Each name or address is tested as
if it were a HyperSQL network client address, using the same exact method
that the HyperSQL listener will use. (HyperSQL listeners use this same
ServerAcl
class to test incoming source addresses).
ServerAcl
will report the rule which matches and
whether access is denied or allowed to that address.
If you have edited a copy of an existing ACL file (as suggested above), then overwrite your live ACL file with your new, validated ACL file. I.e., copy your temp file over top of your live ACL file.
ServerAcl
can be run in the same exact way
described above, to troubleshoot runtime access issues. If you use an ACL
file and a user or application can't get a connection to the database, you
can run ServerAcl
to quickly and definitively find
if the client is being prohibited by an ACL rule.
$Revision: 6787 $