SQLite for Secrecy Management - Tools and Methods
Introduction
Secrets pervade enterprise systems. Access to critical corporate resources will always require credentials of some type, and this sensitive data is often inadequately protected. It is rife both for erroneous exposure and malicious exploitation. Best practices are few, and often fail.
SQLite is a natural storage platform, approved by the Library of the U.S. Congress as a long-term archival medium. “SQLite is likely used more than all other database engines combined.” The software undergoes extensive testing as it has acquired DO-178B certification for reliability due to the needs of the avionics industry, and is currently used on the Airbus A350's flight systems. The need for SQLite emerged from a damage control application tasked for the U.S. battleship DDG-79 Oscar Austin. An Informix database was running under HP-UX on this vessel, and during ship power losses, the database would not always restart without maintenance, presenting physical risks for the crew. SQLite is an answer to that danger; when used properly, it will transparently recover from such crashes. Despite a small number of CVEs patched in CentOS 7 (CVE-2015-3414, CVE-2015-3415, CVE-2015-3416, CVE-2019-13734), few databases can match SQLite's reliability record, and none that are commercially prevalent.
SQLite specifically avoids any question of access control. It does not implement GRANT and REVOKE as found in other databases, and delegates permissions to the OS. Adapting it for sensitive data always requires strong security to be implemented upon it.
The free releases of CyberArk Conjur and Summon build a basic platform for secrecy management. These tools are somewhat awkward, as conjur requires a running instance of PostgreSQL, which brings an attack surface that is far larger than hoped. Slaving an enterprise to a free, centralized instance of conjur and PostgreSQL is a large risk, as CyberArk's documentation attests.
CyberArk summon, however, can be configured with custom backend providers, which have simple interfacing requirements. SQLite is a fit both for summon and as a standalone secrecy provider.
Surpassing summon, a TLS-wrapped network service requiring a client certificate is also presented. It will further be configured for fault tolerance, ameliorating critical access concerns.
SQLite is a storage platform of the highest caliber. When used correctly, it provides availability in the context of the recovery of an OS instance. The manipulation of permissions it leaves to the user, and best practices for its use in secrecy management are outlined here.
Developer Danger
All secrets originate when a developer (who may work for your vendor) introduces the need for them into running systems. If these secrets are not adequately protected, then they are vulnerable to reaping and exploitation by an adversary. This risk may be historical or immediate, implemented by a developer who has departed or remains available. In any case, the risk must be discerned and remediated to preserve security.
A recent white paper from EMA/Cycode has explicitly named a major objective for breach exploitation:
Developers are the new target. Developers are often considered some of the most difficult and demanding technology resources to corral because they have little desire to conform or comply with security protocols and controls. Instead, they are concentrated on delivering applications through a rapid development pace. The attackers know all about the processes and procedures that developers are likely (or NOT likely) to follow and exploit these weaknesses to achieve their ends. They also know that developers tend to be over-credentialed and generally have access to more valuable systems and deeper levels of authorization than a typical end-user, which makes them a high-value target.
An eventual realization that secrets have leaked into revision control (where visibility is difficult to contain) is rarely coupled with burning motivation to remediate credential breaches. Password changes involving production outages and attempts at repository cleansing may well fail (“we discovered that we could recover the full contents of deleted commits from GitHub with only the commit’s SHA-1 ID ...we found that these hidden commit hashes could be recovered with trivial effort via the Events API”) despite vendor recommended methods to expunge secrecy. Such torpor breeds hostile exploitation.
Threat assessment ideally includes exhaustive searches of revision control systems, checking for secrets in the entire breadth of their history with appropriate tooling (or the best otherwise available substitutes). A deeper scan than just the most recent “head” revision is required. Overlooking a source code repository defines an incomplete assessment of threat.
Rotation of secrets is also a mandatory goal for enterprise security. Too often, a password is exposed that cannot be changed due to production process impacts. Gaining a rotation procedure renders secrets worthless to those that intend exploitation.
Security is a cost center until the very moment that ransomware takes the data center. Protecting an enterprise from these risks is the aim of this effort.
Design Considerations
A secrecy management platform must implement two design imperatives: it must be reliable, and it must be resistant to abuse, so an understanding of integrity risks to a SQLite database is required.
While SQLite asserts itself to be a “zero-administration database,” it is not difficult to corrupt, which can be catastrophic for critical systems. Great care must be taken in SQLite systems design in this scenario.
A principle aspect of SQLite is that it allows only one active writer in the database at any time, and that a writer will always block readers to some extent (attempts by a second writer result in “Error: database is locked” in the command line utility). At the time of publication, an experimental concurrent write feature is planned, but is not yet production code. Data Manipulation Language (DML - i.e. INSERT, UPDATE, DELETE) and any Data Definition Language causing writes (DDL - i.e. CREATE INDEX) will block access to all readers.
A common reaction by developers is to enable Write Ahead Logging (“WAL”) mode, which alters the database behavior to allow increased simultaneous access by a writer and multiple readers. The dangers of WAL mode are made explicit in various sources of SQLite documentation:
“To accelerate searching the WAL, SQLite creates a WAL index in shared memory. This improves the performance of read transactions, but the use of shared memory requires that all readers must be on the same machine [and OS instance]. Thus, WAL mode does not work on a network filesystem.” 1
“It is not possible to change the page size after entering WAL mode.” 1
“In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index.” 1
SQLite does not guarantee ACID consistency with
ATTACH DATABASEin WAL mode. “Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.” 2
Write Ahead Logging should only be enabled after a thorough understanding of its limitations. It will not be enabled in the examples outlined here.
Beyond WAL considerations, there are a few other perils of database corruption and sundry failed operations:
-
Soft and hard links can corrupt a SQLite database. For critical systems, checks should be made in advance that abort access if either a soft link or multiple hard links on the database are found on the filesystem. “If a single database file has multiple links (either hard or soft links) then that is just another way of saying that the file has multiple names. If two or more processes open the database using different names, then they will use different rollback journals and WAL files. That means that if one process crashes, the other process will be unable to recover the transaction in progress because it will be looking in the wrong place for the appropriate journal.”
-
Writers must have write permission on a working directory in order to create the various temporary files used for crash recovery.
-
SQLite uses a “Cost Based Optimizer,” and for lengthy credential stores, an explicit ANALYZE is preferable to the ad-hoc statistics collection that is performed by successive write-enabled connections. The use of ANALYZE comes with the proviso that a lengthy write may be triggered due to full table scans. This exercise is left to the reader.
-
After heavy modification, databases should be VACUUMed. This will likely throw all readers off the database.
-
SQL bind variables prevent “SQL Injection.” To avoid a “Bobby Tables” attack in a critical security component, they will be used in these examples.
-
Since only a single writer is allowed in a (production) SQLite database, regardless of its logging mode, a secondary write attempt will fail with SQLITE_BUSY. One common solution is to extend the wait period from zero with a command of the form
PRAGMA busy_timeout=5000. In a credential store, it is expected that writes will be rare, so the issue is not otherwise addressed here.
These relevant design parameters are addressed below.
Source Code
CyberArk provides both Conjur and Summon in a client-server model of secrecy agents. As the Conjur Ruby agent requires a running PostgreSQL instance, we give it short space here. However, Conjur has value in its capability to interface directly with Java and .NET, which do have merit (especially in handling the notorious web.config in .NET). For users with systems appropriate to Conjur, analysis should be undertaken. It is unfortunate that the high availability of SQLite does not appear to be implemented in Conjur.
CyberArk Summon has a very simple interface for a secrecy provider: “They take one argument, the identifier of a secret (a string). If retrieval is successful, they return the value on stdout with exit code 0. If errors occur, they return an error message on stderr and a non-0 exit code.”
This interface is implemented below in both the C compiled language, and the PHP scripting language.
First, the version in C:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
#define PERMCHECK 1
#ifdef PERMCHECK
#include <sys/types.h>
#include <sys/stat.h>
#include <unistd.h>
#endif
/* cc -g -Wall -O2 -D_FORTIFY_SOURCE=2 -fstack-protector-strong -fpic -pie \
-Wl,-z,relro,-z,now -Wl,-z,now \
-o summon-sqlite summon-sqlite.c -lsqlite3
sqlite3 summon-sqlite.db \
'CREATE TABLE secret (user TEXT UNIQUE NOT NULL, pw TEXT NOT NULL);'
https://cyberark.github.io/summon/
Providers are easy to write.
Given the identifier of a secret, they either return its value or an error.
This is their contract:
They take one argument, the identifier of a secret (a string).
If retrieval is successful, they return the value on stdout with exit code 0.
If errors occur, they return an error message on stderr and a non-0 exit code.
*/
static sqlite3 *db = NULL;
void dberr(int n)
{
fprintf(stderr, "%s (%d)\n", sqlite3_errstr(n), n);
sqlite3_close(db);
exit(1);
}
int main(int argc, char **argv)
{
int index, rc;
char *dbfile = getenv("DBFILE"), *Z = "/usr/local/lib/summon-sqlite/summon-sqlite.db";
sqlite3_stmt *res = NULL;
if(!dbfile) dbfile = Z;
#ifdef PERMCHECK
{
struct stat dbs;
if(-1 == lstat(dbfile, &dbs)) { fprintf(stderr, "stat error\n"); exit(1); }
else
{
if(S_ISLNK(dbs.st_mode) || 1 < dbs.st_nlink)
{ fprintf(stderr, "link error-%d\n", (int) dbs.st_nlink); exit(1); }
if(dbs.st_mode & (S_IXUSR|S_IRWXG|S_IRWXO)) { fprintf(stderr, "perm error - chmod 600 %s\n", dbfile); exit(1); }
}
}
#endif
switch(argc)
{
case 2: /* read the pw */
if(!strcmp("--version", argv[1])) { printf("1.0\n"); exit(0); } /* summon -V */
if(SQLITE_OK != (rc = sqlite3_open_v2(dbfile, &db, SQLITE_OPEN_READONLY, NULL))) dberr(rc);
if(SQLITE_OK != (rc = sqlite3_prepare_v2(db, "SELECT pw FROM secret WHERE user=?", -1, &res, NULL))) dberr(rc);
if(SQLITE_OK != (rc = sqlite3_bind_text(res, 1, argv[1], -1, NULL))) dberr(rc);
if(SQLITE_ROW == sqlite3_step(res)) printf("%s\n", sqlite3_column_text(res, 0)); /* cut by Go's Strings.TrimSpace */
else { fprintf(stderr, "no secret for %s\n", argv[1]); sqlite3_close(db); exit(1); }
break;
case 3: /* set the pw SQLITE_OPEN_NOFOLLOW */
if(SQLITE_OK != (rc = sqlite3_open_v2(dbfile, &db, SQLITE_OPEN_READWRITE, NULL))) dberr(rc);
if(SQLITE_OK != (rc = sqlite3_prepare_v2(db, "INSERT INTO secret (user,pw) VALUES (?,?)", -1, &res, NULL))) dberr(rc);
for(index = 1; index <= 2; index++)
if(SQLITE_OK != (rc = sqlite3_bind_text(res, index, argv[index], -1, NULL))) dberr(rc);
if(SQLITE_CONSTRAINT == (rc = sqlite3_step(res)))
{
sqlite3_finalize(res);
if(SQLITE_OK != (rc = sqlite3_prepare_v2(db, "UPDATE secret SET pw=?2 WHERE user=?1", -1, &res, NULL))) dberr(rc);
for(index = 1; index <= 2; index++)
if(SQLITE_OK != (rc = sqlite3_bind_text(res, index, argv[index], -1, NULL))) dberr(rc);
rc = sqlite3_step(res);
}
if(SQLITE_DONE != rc) dberr(rc);
break;
default:
fprintf(stderr, "%s (user)\n", argv[0]); exit(1);
}
sqlite3_finalize(res); sqlite3_close(db); exit(0);
}
A few important points regarding aspects of this source code:
-
Above, compiler directives and database creation SQL is provided as commentary. The compiler is assumed to be present with devel packages, and the CLI version of SQLite is likewise assumed to be present.
-
POSIX system calls, macros, and stat() constants are used in a conditional compilation block to ensure that the target database is appropriately linked and accessible only to the owner (Windows implementations will need to customize for ACLs, and other exotic filesystems should alter to taste). Expansion to group permissions is left to an exercise for the reader, if required.
-
The test for hard and soft links is a best practice for all users of SQLite databases. The SQLITE_OPEN_NOFOLLOW option may also be available on your platform (it is not present in the CentOS 7 package). Note that S_ISLNK is "Not in POSIX.1-1996" according to
man 2 stat. -
An invocation of
summon -Vwill query this backend with a--versionargument, and is caught with a successful exit (this cannot be used as a named secret). As the permissions test occurs prior to this check, the database file must be present and correct before this check will be successful. -
Otherwise, the password request is sent to the database as a bind variable with a SQL template without dynamic memory allocation, and returned if present. Note the UPDATE reorders the bind variables with an explicit syntax.
-
A secondary mode is provided that will update a stored secret, and is useful for a password change, perhaps invoked over SSH. This can be used to implement the rotation of a secret, and is adaptable for batch rotation. There is an UPSERT SQL syntax available in newer versions of SQLite that can alternately insert or update, depending upon the presence of a stored secret, but it is avoided here in interests of compatibility with the older versions of SQLite, such as is present on CentOS 7, implemented by the detection of a constraint violation.
-
The column used above is explicit. In my own personal use, a TNS column is also present for the specification of an Oracle database connection, or otherwise subverted for other platforms. The table structure presented here is quite simple, and could be implemented by key-value engines such as Berkeley DB, but could be easily extended with additional columns and constraints (these might include an expiration date for a secret, the secret owner's name and email, and notes for operators). It is never appropriate to use a
SELECT *in production applications (or their DML equivalent), thus theINSERTis also specific on the target columns and will not fail if nullable columns are added.
There will be some who argue that C is an unsafe language, and should be avoided in the context of security. This is not easy to do with a straight face, when SQLite is implemented in C and trusted for critical avionics, and the code presented here is sufficiently small for thorough review. While there is an obvious preference in the CyberArk tooling for Ruby and Go, below is an equivalent PHP backend. CentOS 7 variants can load this interpreter with yum install php-cli which appears to bundle SQLite connectivity.
#!/usr/bin/php -f
<?php
$dbfile = getenv("DBFILE");
$stderr = fopen('php://stderr', 'w');
if(!$dbfile) $dbfile = '/usr/local/lib/summon-sqlite/summon-sqlite.db';
if($dbs = lstat($dbfile))
{
define('S_IRWXO', 7); define('S_IRWXG', 56); define('S_IXUSR', 64);
if(is_link($dbfile) || $dbs['nlink'] > 1)
{ fprintf($stderr, "link error-%d\n", $dbs['nlink']); exit(1); }
if($dbs['mode'] & (S_IXUSR|S_IRWXG|S_IRWXO))
{ fprintf($stderr, "perm error - chmod 600 %s\n", $dbfile); exit(1); }
}
else { fprintf($stderr, "stat error\n"); exit(1); }
switch($argc)
{
case 2: /* read the pw */
if('--version' == $argv[1]) { printf("1.0\n"); exit(0); } /* summon -V */
$db = new SQLite3($dbfile, SQLITE3_OPEN_READONLY);
$st = $db->prepare('SELECT pw FROM secret WHERE user=?');
$st->bindParam(1, $argv[1], SQLITE3_TEXT);
$rs = $st->execute();
$id = $rs->fetchArray(SQLITE3_ASSOC)['pw'];
if(strlen($id)) printf("%s\n", $id);
else { fprintf($stderr, "no secret for %s\n", $argv[1]); exit(1); }
break;
case 3: /* set the pw */
$db = new SQLite3($dbfile, SQLITE3_OPEN_READWRITE);
$db->enableExceptions(TRUE);
try
{
$st = $db->prepare('INSERT INTO secret (user,pw) VALUES (?,?)');
$st->bindParam(1, $argv[1], SQLITE3_TEXT);
$st->bindParam(2, $argv[2], SQLITE3_TEXT);
$rs = $st->execute();
}
catch (Exception $error)
{
$st = $db->prepare('UPDATE secret SET pw=?2 WHERE user=?1');
$st->bindParam(1, $argv[1], SQLITE3_TEXT);
$st->bindParam(2, $argv[2], SQLITE3_TEXT);
if($rs = $st->execute());
else { fprintf($stderr, "update error\n"); exit(1); }
}
break;
default:
fprintf($stderr, "%s (user)\n", $argv[0]); exit(1);
}
exit(0); ?>
The only limitation with the PHP version is the assumption that the POSIX permission definitions above are valid for the platform; adjustment for ACLs or other filesystem eccentricities are left as an exercise for the reader.
The PHP provider to summon is perhaps of interest to many users, as a vast API is available with much less tedium than C.
Integration with summon
The summon utility is distributed as a statically-compiled Go application, without any supporting libraries or documentation. There is a docs area of the code repository, but it appears to be somewhat sparse.
An RPM package for summon is available, which is easily installed (show all the assets on the web page if the RPM is not immediately visible):
# rpm -Uvh summon_0.9.4_amd64.rpm Preparing... ################################# [100%] Updating / installing... 1:summon-0:0.9.4-1 ################################# [100%] # rpm -ql summon /usr/local/bin/summon # ls -l /usr/local/bin/summon -rwxr-xr-x. 1 root root 3503272 Aug 18 12:24 /usr/local/bin/summon # file /usr/local/bin/summon /usr/local/bin/summon: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), statically linked, stripped
I have no idea why an open-source application is distributed without debugging symbols (stripped, above).
The summon utility will examine the /usr/local/lib/summon directory for backend providers; the compiled C must be placed in this location.
The database file must be placed elsewhere, as summon -V will attempt to execute all files in this directory, even if they lack execute permissions (summon really should make use of stat()).
# mkdir /usr/local/lib/summon /usr/local/lib/summon-sqlite # cd /usr/local/lib/summon-sqlite/
Place the source and create the database in this directory. Assuming that you have chosen the C version, compile the binary, and place it in /usr/local/lib/summon.
# sqlite3 summon-sqlite.db \
'CREATE TABLE secret (user TEXT UNIQUE NOT NULL, pw TEXT NOT NULL);'
# cc -g -Wall -O2 -D_FORTIFY_SOURCE=2 -fstack-protector-strong -fpic -pie \
> -Wl,-z,relro,-z,now -Wl,-z,now \
> -o summon-sqlite summon-sqlite.c -lsqlite3
# mv summon-sqlite /usr/local/lib/summon/
# cd /usr/local/lib/summon
At this point, add a well-known credential to your new database (correcting any stat() errors):
# ./summon-sqlite scott tiger perm error - chmod 600 /usr/local/lib/summon-sqlite/summon-sqlite.db # chmod 600 /usr/local/lib/summon-sqlite/summon-sqlite.db # ./summon-sqlite scott tiger # ./summon-sqlite scott tiger
After a working database is in place, the summon utility is able to probe this provider:
# summon -V summon-sqlite version 1.0
The utility does have some usage information:
# summon --help NAME: summon - Parse secrets.yml and export environment variables USAGE: summon [global options] command [command options] [arguments...] VERSION: 0.9.4 COMMANDS: help, h Shows a list of commands or help for one command GLOBAL OPTIONS: -p value, --provider value Path to provider for fetching secrets -e value, --environment value Specify section/environment to parse from secrets.yaml -f value Path to secrets.yml (default: "secrets.yml") --up Go up in the directory hierarchy until the secrets file is found -D value var=value causes substitution of value to $var --yaml value secrets.yml as a literal string --ignore value, -i value Ignore the specified key if is isn't accessible or doesn't exist --ignore-all, -I Ignore inaccessible or missing keys --all-provider-versions, -V List of all of the providers in the default path and their versions(if they have the --version tag) --help, -h show help --version, -v print the version
In order to trigger the exchange of a secret between summon and the backend, a configuration file must be created:
# cd /usr/local/lib # echo 'common:' > secrets.yml # echo ' ORACLE_SCOTT: !var scott' >> /usr/local/lib/secrets.yml
Above, the “common” block header might be replaced with “production,” “development,“ or some other marker for a set of credentials. Within such sections, an environment variable is associated with a key that will be passed to the database.
With this in place, we are ready to invoke summon:
# summon -e common bash bash# echo $ORACLE_SCOTT tiger
The summon utility must launch a child process, and it is to the child that the environment definition is passed. A simple shell process was launched above, but the intention of the utility is to launch a major subsystem such as docker/podman or trigger batch processing.
One somewhat inconvenient aspect of summon is that the utility does not exit after the credentials are passed, but remains idle until the child exits:
bash# ps -ef | tail -4 root 7936 5219 0 13:35 pts/0 00:00:00 summon -f secrets.yml -e common bash root 7943 7936 0 13:35 pts/0 00:00:00 /bin/bash root 8111 7943 0 13:37 pts/0 00:00:00 ps -ef root 8112 7943 0 13:37 pts/0 00:00:00 tail -4
It is surprising that summon did not use an execve() system call to simply update the environment, then replace itself with the child, rather than a fork() to create it. Extensive use of summon with many applications may entail multiple copies of this idle parent in the process table. Perhaps compatibility with Windows or other exotic platforms led summon to avoid execve().
Note that if any of the child processes call unsetenv(), clearenv(), or otherwise rewrite the environment, then the credentials intended for a potential grandchild process could be destroyed, and would require a repeat run of summon.
Beware also that environment variables for all processes are exposed in the /proc/*/environ files, and can be scavenged by the process owner or root user. Likewise, the command line arguments (as argc and argv[]) are equally visible to all system users in the /proc/*/cmdline files, which means that the database keys are also exposed by design. For these reasons, both argument and environment passage of secrets is suboptimal.
The password change functionality of the backend should be demonstrated, so the credential is altered here:
# /usr/local/lib/summon/summon-sqlite scott panther # summon -e common bash bash# echo $ORACLE_SCOTT panther
It should be noted that the main driver of summon security in this configuration is the permission on the database file, which is enforced by the backend. Non-root users attempting to invoke summon on this backend will fail. Changing the ownership of the database file will convey this permission to a specific named user.
This was all quite a lot of work to pass an environment variable, and some might question the architecture on several points. This prompts reflection on what might be implemented beyond these constraints.
Standalone Use
The C code could be adapted as a standalone network service running under an inetd interface that redirects standard input/output onto the remote socket. While the PHP could also be used this way, it presents an attack surface that is larger than preferred.
The stunnel utility is also able to implement the inetd interface, but can impose extremely strict access control over TLS that requires a client certificate. Notably, the CentOS 7 stunnel RPM changelog has no mentions of published CVEs. While stunnel is based upon OpenSSL which must be vigilantly patched, the lack of CVEs for the stunnel package is reassuring. While the stunnel website releases new versions at a rapid pace with severe warnings, we must trust RedHat's backporting efforts that assure legacy security.
Below is the adapted code, that removes password change functionality and safely reads the database key from standard input, instead of argv[1] (which avoids exposure in /proc):
# cat SQLsecret.c
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
/* cc -g -Wall -O2 -D_FORTIFY_SOURCE=2 -fstack-protector-strong -fpic -pie \
-Wl,-z,relro,-z,now -Wl,-z,now -o SQLsecret SQLsecret.c -lsqlite3 */
#define BUF 1024
#define PERMCHECK 1
#ifdef PERMCHECK
#include <sys/types.h>
#include <sys/stat.h>
#include <unistd.h>
#endif
static sqlite3 *db = NULL;
void dberr(int n)
{
printf("**%s (%d)\n", sqlite3_errstr(n), n);
sqlite3_close(db);
exit(0);
}
int main(int argc, char **argv)
{
int rc;
char key[BUF], *dbfile = getenv("DBFILE"), *Z = "/etc/stunnel/SQLsecret.db";
sqlite3_stmt *res = NULL;
if(!dbfile) dbfile = Z;
#ifdef PERMCHECK
{
struct stat dbs;
if(-1 == lstat(dbfile, &dbs)) { printf("**stat error\n"); exit(0); }
else
{
if(S_ISLNK(dbs.st_mode) || 1 < dbs.st_nlink)
{ fprintf(stderr, "**link error-%d\n", (int) dbs.st_nlink); exit(1); }
if(dbs.st_mode & (S_IXUSR|S_IRWXG|S_IRWXO)) { printf("perm error - chmod 600 %s\n", dbfile); exit(0); }
}
}
#endif
if(fgets(key, BUF, stdin) == key) { char *p = strrchr(key, '\n'); if(p) *p = 0; }
else { printf("**read error\n"); exit(0); }
if(SQLITE_OK != (rc = sqlite3_open_v2(dbfile, &db, SQLITE_OPEN_READONLY, NULL))) dberr(rc);
if(SQLITE_OK != (rc = sqlite3_prepare_v2(db, "SELECT pw FROM secret WHERE user=?", -1, &res, NULL))) dberr(rc);
if(SQLITE_OK != (rc = sqlite3_bind_text(res, 1, key, -1, NULL))) dberr(rc);
if(SQLITE_ROW == sqlite3_step(res)) printf("%s\n", sqlite3_column_text(res, 0));
else printf("**no secret for %s\n", key);
sqlite3_close(db); exit(0);
}
Assuming the source code is in the stunnel configuration directory, compile the code, and adjust the SELinux security context if you are on CentOS or a derivative. Note that an SELinux relabel event will wipe this permission, so generate a permanent rule if desired.
# cd /etc/stunnel
# cc -g -Wall -O2 -D_FORTIFY_SOURCE=2 -fstack-protector-strong -fpic -pie \
-Wl,-z,relro,-z,now -Wl,-z,now -o SQLsecret SQLsecret.c -lsqlite3
# chcon -t stunnel_exec_t SQLsecret
Place the database and populate it with the well-known user, confirm that it is operational with the correct permissions as root, then test access as a non-root user:
$ /etc/stunnel/SQLsecret scott **unable to open database file (14)
At this point, an RSA key/certificate pair is necessary for the TLS wrapper. Create one and lock down the permissions with these commands:
# cd /etc/stunnel # openssl req -newkey rsa:4096 -x509 -days 3650 -nodes \ -out SQLsecret.pem -keyout SQLsecret.pem Generating a 4096 bit RSA private key .....................................................................................................................................................................................++ ...++ writing new private key to 'SQLsecret.pem' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:US State or Province Name (full name) []:IL Locality Name (eg, city) [Default City]:Chicago Organization Name (eg, company) [Default Company Ltd]:SQLsecret Organizational Unit Name (eg, section) []:stunnel Common Name (eg, your name or your server's hostname) []:netcat Email Address []:SQLsecret@foobar # chmod 400 SQLsecret.pem
Configure an stunnel control file:
# cat SQLsecret.conf #GLOBAL################################################################## sslVersion = TLSv1.2 TIMEOUTidle = 300 renegotiation = no FIPS = no options = NO_SSLv2 options = NO_SSLv3 options = SINGLE_DH_USE options = SINGLE_ECDH_USE options = CIPHER_SERVER_PREFERENCE syslog = yes debug = debug # setuid = adm # setgid = adm # chroot = /var/empty libwrap = no service = SQLsecret ; cd /var/empty; mkdir -p stunnel/etc; cd stunnel/etc; ; echo 'SQLsecret: ALL EXCEPT localhost' >> hosts.deny; ; chcon -t stunnel_etc_t hosts.deny ; https://hynek.me/articles/hardening-your-web-servers-ssl-ciphers/ ciphers=ECDH+AESGCM:ECDH+CHACHA20:DH+AESGCM:RSA+AESGCM:!aNULL:!MD5:!DSS curve = secp521r1 #CREDENTIALS############################################################# verify = 4 CAfile = /etc/stunnel/SQLsecret.pem cert = /etc/stunnel/SQLsecret.pem #ROLE#################################################################### exec = /etc/stunnel/SQLsecret
The following files should now be present in /etc/stunnel (double check the permissions on the key):
$ ll /etc/stunnel/SQLsecret* -rwxr-xr-x. 1 root root 20472 Sep 23 11:11 /etc/stunnel/SQLsecret -rw-r--r--. 1 root root 1588 Sep 23 11:11 /etc/stunnel/SQLsecret.c -rw-r--r--. 1 root root 967 Sep 22 14:59 /etc/stunnel/SQLsecret.conf -rw-------. 1 root root 12288 Sep 23 12:03 /etc/stunnel/SQLsecret.db -r--------. 1 root root 6221 Sep 22 14:10 /etc/stunnel/SQLsecret.pem
To conceal the presence of this code, it is safe to chmod 700 /etc/stunnel with the current configuration (there are scenarios where this could be a problem).
Assuming that systemd will be used with socket activation, choose a port (other init systems can resort to classic inetd):
# cat /etc/systemd/system/SQLsecret.socket [Unit] Description=SQLsecret [Socket] ListenStream=34251 Accept=yes [Install] WantedBy=sockets.target
Configure stunnel to be launched when connections are accepted:
# cat /etc/systemd/system/SQLsecret@.service [Unit] Description=SQLsecret [Service] ExecStart=-/usr/bin/stunnel /etc/stunnel/SQLsecret.conf StandardInput=socket
Start the listener. To start it at every system boot, enable it.
# systemctl enable SQLsecret.socket Created symlink from /etc/systemd/system/sockets.target.wants/SQLsecret.socket to /etc/systemd/system/SQLsecret.socket. # systemctl start SQLsecret.socket
Use an SSL client that is capable of both providing a client certificate and the TLS 1.2 protocol to open the tunnel:
# openssl s_client -quiet -cert SQLsecret.pem -connect localhost:34251 depth=0 C = US, ST = IL, L = Chicago, O = SQLsecret, OU = stunnel, CN = netcat, emailAddress = SQLsecret@foobar verify error:num=18:self signed certificate verify return:1 depth=0 C = US, ST = IL, L = Chicago, O = SQLsecret, OU = stunnel, CN = netcat, emailAddress = SQLsecret@foobar verify return:1 scott tiger # openssl s_client -quiet -connect localhost:34251 depth=0 C = US, ST = IL, L = Chicago, O = SQLsecret, OU = stunnel, CN = netcat, emailAddress = SQLsecret@foobar verify error:num=18:self signed certificate verify return:1 depth=0 C = US, ST = IL, L = Chicago, O = SQLsecret, OU = stunnel, CN = netcat, emailAddress = SQLsecret@foobar verify return:1 140139609302928:error:14094410:SSL routines:ssl3_read_bytes:sslv3 alert handshake failure:s3_pkt.c:1493:SSL alert number 40 140139609302928:error:140790E5:SSL routines:ssl23_write:ssl handshake failure:s23_lib.c:177: # openssl s_client -quiet -cert SQLsecret.pem -connect localhost:34251 2>/dev/null scott tiger # nc --ssl --ssl-key=/etc/stunnel/SQLsecret.pem --ssl-cert=/etc/stunnel/SQLsecret.pem localhost 34251 scott tiger Ncat: Input/output error. # nc --ssl localhost 34251 Ncat: Input/output error.
Above, we have used both netcat and the OpenSSL s_client to interact with our service. Both fail if they do not provide the certificate (also containing the key). It is assumed that administrators are familiar with the vigilant protection of TLS certificates. The s_client status messages can be silenced by redirecting standard error to null. Netcat is able to obtain the tiger password interactively, but always reports an error and fails when used in batch. Within the context of the POSIX shell, s_client is the option pursued below:
# echo scott | openssl s_client -quiet -cert SQLsecret.pem -key SQLsecret.pem -connect localhost:34251 2>/dev/null
tiger
# var=$(echo scott | openssl s_client -quiet -cert SQLsecret.pem -key SQLsecret.pem -connect localhost:34251 2>/dev/null)
# echo $var
tiger
# get_secret () { printf %s\\n "$1" | openssl s_client -quiet -cert SQLsecret.pem -key SQLsecret.pem -connect localhost:34251 2>/dev/null; return $?; }
# get_secret scott
tiger
Note that the shell function above does not expose argv[] to /proc.
The use of echo in the POSIX shell is discouraged; avoid it in production scripting. While the fgets() standard library function should read until EOF or newline, attempts at printf without a newline return null in the POSIX scripting attempted, so include the \\n.
It is important to note that s_client returns its status of success or failure to the shell, and we can act on this information. Examine the shell's return status below with calls to both the listening port and a port that we did not configure:
# printf scott\\n | openssl s_client -quiet -cert SQLsecret.pem -connect localhost:34251 2>/dev/null tiger # echo $? 0 # printf scott\\n | openssl s_client -quiet -cert SQLsecret.pem -connect localhost:34252 2>/dev/null # echo $? 1
Success to the shell means zero, and failure is non-zero. Consider fault tolerance implemented in the POSIX shell, over a set of three stunnel servers:
#!/bin/sh
set -u
r=1
until [ 0 -eq "$r" ]
do for host in secret1.myco.com secret2.myco.com secret3.myco.com
do var=$(printf %s\\n "$1" |
openssl s_client -quiet -cert SQLsecret.pem \
-connect "$host":34251 2>/dev/null)
r=$?
case "$var" in [*][*]*) r=1;; esac
[ 0 -eq "$r" ] && break
done
done
printf %s\\n "$var"
The script will loop through all the stunnel servers until a successful database lookup is returned. Not only are s_client TLS errors retried, but any database error will also force failure for retry on the next server, so SQLITE_BUSY errors due to writers are no longer fatal. Note that a lookup of a nonexistent user will push the script into an infinite loop:
# ./getsecret.sh scott tiger # ./getsecret.sh foo bar # ./getsecret.sh bar (never returns)
No exports of environment data took place above, minimizing visibility in /proc. It might be possible to find credentials in a memory dump (perhaps involving /proc/*/mem), but the bar is considerably raised in attack complexity upon the credential store. Code that overwrites the key, erasing it from memory after use, is a best practice.
Rotation can be implemented by multiple approaches. It is possible to network-mount the databases on all of the redundant servers, issue ATTACH directives, then begin a transaction to update a secret atomically and globally. This is dangerous as most network mounts are cleartext; SMB requires specific directives to encrypt, and NFS is cleartext unless forced over stunnel. Mounts with sshfs might also preserve transaction integrity. This is specifically incompatible with any database that is in WAL mode.
Rotation without ACID guarantees is likely easier, and can be implemented by my previous ssh-run script in batch.
After rotation, an administrator might consider setting all database files to read-only permission, and only enabling write access when ready to push changes.
Security can be extended by configuring stunnel to implement a chroot() and release root privileges. The libwrap feature could also be enabled to allow access from a limited set of IP addresses or DNS names. Commentary above in the stunnel configuration file addresses the options to do so.
Obviously, s_client is not the ideal agent to inject secrets into a runtime environment. It is used here in the context of the POSIX shell, but sophisticated frameworks would likely have TLS 1.2 functionality as a bare minimum for secure communications and redundancy. For POSIX shell applications, the use of s_client does appear sufficient, but other options could easily be found.
Conclusion
The motivation for this article involves credential compromise events, with which every seasoned administrator will be only far too familiar. These experiences are unwanted, inconsistently addressed, highly stressful, and rarely have productive outcomes. They are better avoided, but such avoidance requires both vigilant commitment and ingenuity. Best practices are not mature, and it is an uncommon enterprise that is not plagued by these events.
The use of Ruby and Go is problematic. Our modern kernels are written in C, full stop, and adherence to garbage-collected languages appears to often result in suboptimal solutions of reduced functionality. While we may lament the many footguns of the tooling, we cannot forgo the exposed security controls in an exercise of idealism.
Plaintext in the database is a problem, and other providers of secrecy management tooling encrypt the storage. This could be implemented in the code presented here, but even without such protections this remains a far better solution than many web.config files that proliferate into revision control, or similar exposures. Where required, SQLite storage encryption might be implemented with LibTomCrypt, LibSodium, or, for maximal portability, OpenSSL. SQLite extensions might also present another option.
As I am fully in the realm of US7ASCII, Unicode issues are not addressed here, but nothing is present in this coding that prevents the use of UTF-8 on capable platforms. Where problems arise, solutions in C certainly exist.
What is certain is the insufficient availability and coverage of existing tooling. Perhaps resolution of this problem is attainable.
Charles Fisher has an electrical engineering degree from the University of Iowa and works as a systems and database administrator for a Fortune 500 mining and manufacturing corporation.
Recent Articles
