DatabasePostGreSQL was chosen as the database back-end for ReAssure, as at the time of design it was the obvious choice for the following reasons (besides being free).
- Advantages of PostGreSQL:
- Mature handling of stored procedures, which allows moving some logic to the database back-end, and enforcement of security restrictions (see below).
- Passes ACID test
- Code solidity. PostGreSQL was found to contain only 20 defects in more than 775,000 lines of code vs 97 bugs in 425,000 lines of code in version 4.1.8 of MySQL. That was a good showing for MySQL, but the PostGreSQL results were impressive. This does not guarantee that there are fewer remaining bugs or serious vulnerabilities in PostGreSQL, but it is a reassuring (no pun intended) indication of the quality of the code produced.
- Higher assurance of database integrity with CHECK constraints and foreign key support
- Support for triggers, which allow moving more of the integrity logic inside the database, which makes it less dependent on the correct coding of all the involved scripts.
- Poor examples in the documentation on how to use stored procedures securely.
How to write and use stored procedures securelyStored procedures have two advantages. First, they diminish code duplication by allowing different front-ends or scripts to access the database using the same code. Second, the database account used by scripts can be restricted to only execute specific stored procedures. We call this kind of account a scripting account. Examples of different database scripts in ReAssure are the Ruby cgi scripts, and a Python script managing the experimental switch.
With PostgreSQL, it is the "EXTERNAL SECURITY DEFINER;" clause that allows a script account to work with only the "EXECUTE" privilege. Without it, the account would need "INSERT", "UPDATE", etc, privileges as well. This clause is like a setuid setting in UNIX; it allows the procedures to be executed with the privileges of their creator. Therefore, three accounts are needed per database; a developer account, to create the tables; a stored procedure writer account with only the capabilities needed to run the operations within the stored procedures, and so without the capabilities to create and drop tables, grant privileges, etc...; and finally a script account that can only execute stored procedures.
Here is an example table, and an SQL stored procedure operating on it:
CREATE TABLE failed ( user_ID VARCHAR(30) NOT NULL, when_failed timestamp NOT NULL, ip_add VARCHAR(20), PRIMARY KEY (user_ID, when_failed) ); CREATE FUNCTION log_failure (text, text) RETURNS void AS $$ INSERT INTO failed (user_ID, when_failed, ip_add) VALUES ($1, NOW(), $2); $$ LANGUAGE SQL EXTERNAL SECURITY DEFINER; GRANT EXECUTE ON FUNCTION log_failure (text, text) TO rea_script;
In ReAssure, all database access is mediated through the rea_script account. Using stored procedures in itself doesn't provide complete protection against SQL injection. It only limits the damage that a successful SQL attack could accomplish. To block SQL injection attacks, prepared statements should be used. SQL injection attacks (and all code injection attacks) occur because the same communication channel is used for both code and data. Prepared statements separate the code from the data, at least from the programmer's perspective. Here is an example prepared statement using the above stored procedure:
sth = dbh.prepare("SELECT * FROM log_failure(?, ?)") sth.execute(user_ID, ip_add) sth.finishwhere dbh refers to an open database connection (this example uses the Ruby DBI).
Database Setup InstructionsThere are two databases in ReAssure: rea_data and rea_logs. Both use the public schema. They are setup similarly. After the initial revokes and grants on the databases and schemas, the setup involves copying and pasting the SQL in all of the .postgres files. It is somewhat tedious but it works. Note how rea_fn_creator is granted limited rights on each table (e.g., only SELECT and INSERT for the logs).
# rea_data database #Database permissions REVOKE ALL ON DATABASE rea_data FROM PUBLIC; #Schema permissions # We use only one schema because PostGreSQL keeps track of object owners # which in effect is like a separate schema for each user REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM rea_fn_creator; REVOKE ALL ON SCHEMA public FROM rea_script; REVOKE ALL ON SCHEMA public FROM rea_devel; GRANT USAGE ON SCHEMA public TO rea_devel; GRANT USAGE ON SCHEMA public TO rea_fn_creator; GRANT USAGE ON SCHEMA public TO rea_script; GRANT CREATE ON SCHEMA public TO rea_devel; # rea_fn_creator is unable to drop or alter tables created by rea_devel GRANT CREATE ON SCHEMA public TO rea_fn_creator; For each table in rea_data: GRANT SELECT, INSERT, UPDATE, DELETE ON rea_data_table to rea_fn_creator For each table in rea_logs: GRANT SELECT, INSERT ON rea_logs_table to rea_fn_creator On each function: GRANT EXECUTE ON ... TO rea_script (in rea_data ) #rea_logs database #Database permissions REVOKE ALL ON DATABASE rea_logs FROM PUBLIC; #Schema permissions # We use only one schema because PostGreSQL keeps track of object owners # which in effect is like a separate schema for each user REVOKE ALL ON SCHEMA public FROM rea_fn_creator; REVOKE ALL ON SCHEMA public FROM rea_script; REVOKE ALL ON SCHEMA public FROM rea_devel; GRANT CREATE ON SCHEMA public TO rea_devel; GRANT USAGE ON SCHEMA public TO rea_devel; # rea_fn_creator is unable to drop or alter tables created by rea_devel GRANT CREATE ON SCHEMA public TO rea_fn_creator; GRANT USAGE ON SCHEMA public TO rea_fn_creator; GRANT USAGE ON SCHEMA public TO rea_script; # For each table in rea_logs: GRANT SELECT, INSERT ON rea_logs_table to rea_fn_creator # For each function: # using a separate database and user for logs shields them from a vulnerability in a regular database call. GRANT EXECUTE ON ... TO rea_log (in rea_logs)