sql_firewall: a SQL Firewall Extension for PostgreSQL

A few days ago, I had released a brand-new PostgreSQL extension, called "sql_firewall".

• https://news.ycombinator.com/item?id=10109566
• https://github.com/uptimejp/sql_firewall

sql_firewall is intended to protect PostgreSQL database from SQL injection attacks by limiting SQL queries to be executed on the database.

In this entry, I would like to introduce how it works and how to use it.

How sql_fiewall works

The sql_firewall module has three modes as following:

• Learning mode
• Permissive mode
• Enforcing mode

Under the Learning mode, sql_firewall would learn SQL queries which should be allowed to be executed on PostgreSQL.

In reality, it's difficult for human to know and control all the queries issued by application. So, the Learning mode allows sql_fiewall to learn queries during application testing period or some short period after the application launch.

Once finished learning queries, sql_firewall can be switched to the Permissive mode or the Enforcing mode. Under the Permissive mode, when a non-learned query is executed, sql_firewall would detect and notice that as WARNING. Under the Enforcing mode, sql_firewall would raise ERROR when an unexpected query is found, and the query execution is prohibited.

So, sql_firewall is able to prevent SQL injection attacks by limiting queries only required by specific application.

How to deploy sql_fiewall

The sql_firewall source code is now available on Github, and you can find 'v0_8' tag for version 0.8 release. So, let's pick this to deploy with PostgreSQL 9.4.

First of all, download the 'v0_8' code in ZIP format from Github, and extract it.

[snaga@devvm04 tmp]$ wget https://codeload.github.com/uptimejp/sql_firewall/zip/v0_8
--2015-08-27 08:31:10--  https://codeload.github.com/uptimejp/sql_firewall/zip/v0_8
(...snip...)

2015-08-27 08:31:11 (176 KB/s) - `v0_8' saved [29537/29537]

[snaga@devvm04 tmp]$ mv v0_8 sql_firewall_v0_8.zip
[snaga@devvm04 tmp]$ unzip sql_firewall_v0_8.zip
Archive:  sql_firewall_v0_8.zip
29b77bd43ee3d234470d3ac28c220087fc72a481
   creating: sql_firewall-0_8/
  inflating: sql_firewall-0_8/Makefile
  inflating: sql_firewall-0_8/README.sql_firewall
  inflating: sql_firewall-0_8/sql_firewall--0.8.sql
  inflating: sql_firewall-0_8/sql_firewall.c
  inflating: sql_firewall-0_8/sql_firewall.control
[snaga@devvm04 tmp]$ cd sql_firewall-0_8/
[snaga@devvm04 sql_firewall-0_8]$ ls
Makefile             sql_firewall--0.8.sql  sql_firewall.control
README.sql_firewall  sql_firewall.c
[snaga@devvm04 sql_firewall-0_8]$ 

Then, build and install it with specifying USE_PGXS=1, same as building an ordinary PostgreSQL extension without the core code.

At this time, make sure that your pg_config command can be found in the PATH environment variable. (Following example is adding $PGHOME/bin explicitly on building.)

[snaga@devvm04 sql_firewall-0_8]$ env USE_PGXS=1 PATH=/usr/pgsql-9.4/bin:$PATH make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -I/usr/include/et -fpic -I. -I./ -I/usr/pgsql-9.4/include/server -I/usr/pgsql-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o sql_firewall.o sql_firewall.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -I/usr/include/et -fpic -shared -o sql_firewall.so sql_firewall.o -L/usr/pgsql-9.4/lib -L/usr/lib64 -Wl,--as-needed
[snaga@devvm04 sql_firewall-0_8]$ sudo env USE_PGXS=1 PATH=/usr/pgsql-9.4/bin:$PATH make install
/bin/mkdir -p '/usr/pgsql-9.4/lib'
/bin/mkdir -p '/usr/pgsql-9.4/share/extension'
/bin/mkdir -p '/usr/pgsql-9.4/share/extension'
/usr/bin/install -c -m 755  sql_firewall.so '/usr/pgsql-9.4/lib/sql_firewall.so'
/usr/bin/install -c -m 644 sql_firewall.control '/usr/pgsql-9.4/share/extension/'
/usr/bin/install -c -m 644 sql_firewall--0.8.sql '/usr/pgsql-9.4/share/extension/'
[snaga@devvm04 sql_firewall-0_8]$

After the installation, modify your postgresql.conf and restart PostgreSQL. (sql_firewall requires restarting to change the configurations becasue of some security reasons.)

[snaga@devvm04 sql_firewall-0_8]$ sudo vi /var/lib/pgsql/9.4/data/postgresql.conf

Add/modify following parameters in postgresql.conf.

shared_preload_libraries = 'sql_firewall'
sql_firewall.firewall = 'disabled'

Then, restart PostgreSQL service.

[snaga@devvm04 sql_firewall-0_8]$ sudo service postgresql-9.4 restart
Stopping postgresql-9.4 service:                           [  OK  ]
Starting postgresql-9.4 service:                           [  OK  ]
[snaga@devvm04 sql_firewall-0_8]$

At last, run CREATE EXTENSION command on your database to install views and SQL functions provided by the sql_firewall extension.

[snaga@devvm04 sql_firewall-0_8]$ psql -U postgres testdb
psql (9.4.4)
Type "help" for help.

testdb=# create extension sql_firewall;
CREATE EXTENSION
testdb=# 

Here, it's ready for enabling the sql_firewall feature.

Let's create a test table here.

testdb=# \! cat t.sql
create table k1 (
  uid integer primary key,
  uname text not null
);

insert into k1 values (1, 'Park Gyu-ri');
insert into k1 values (2, 'Nicole Jung');
insert into k1 values (3, 'Goo Ha-ra');
insert into k1 values (4, 'Han Seung-yeon');
insert into k1 values (5, 'Kang Ji-young');

testdb=# \i t.sql
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
testdb=#

How to learn queries

At first, switch sql_firewall to the Learning mode.

Modify sql_firewall.firewall parameter in postgresql.conf as following, and restart PostgreSQL service.

sql_firewall.firewall = 'learning'

Now, you can teach queries to sql_firewall.

testdb=# select * from k1 where uid = 1;
 uid |    uname
-----+-------------
   1 | Park Gyu-ri
(1 row)

testdb=# select * from sql_firewall.sql_firewall_statements;
 userid |  queryid  |              query              | calls
--------+-----------+---------------------------------+-------
     10 | 227323429 | select * from k1 where uid = ?; |     1
(1 row)

testdb=# \q
[snaga@devvm04 sql_firewall-0_8]$

Here, I would teach a query which gets a single row with a condition on the uid column.

The queries which sql_firewall has learned can be found in the sql_firewall.sql_firewall_statements view. So, let's teach a query to fetch them too.

Then, exit from your psql prompt.

Detect unexpected queries in the Permissive mode

Next, switch sql_firewall to the Permissive mode to detect unexpected queries.

Modify sql_firewall.firewall parameter in postgresql.conf, and restart PostgreSQL.

sql_firewall.firewall = 'permissive'

Then, let's execute the query which is previously learned in the Learning mode.

testdb=# select * from k1 where uid = 1;
 uid |    uname
-----+-------------
   1 | Park Gyu-ri
(1 row)

testdb=#

Now, you can see this query can be executed correctly without any warning or any error.

And different constant in the condition "uid = 1" can be also accepted as following.

testdb=# select * from k1 where uid = 2;
 uid |    uname
-----+-------------
   2 | Nicole Jung
(1 row)

testdb=# select * from k1 where uid = 3;
 uid |   uname
-----+-----------
   3 | Goo Ha-ra
(1 row)

testdb=#

On the other hand, adding different condition would raise WARNING.

For example, let's add "or 1=1" condition, well-known in the SQL injection attacks, to the query.

testdb=# select * from k1 where uid = 3 or 1 = 1;
WARNING:  Prohibited SQL statement
 uid |     uname
-----+----------------
   1 | Park Gyu-ri
   2 | Nicole Jung
   3 | Goo Ha-ra
   4 | Han Seung-yeon
   5 | Kang Ji-young
(5 rows)

testdb=#

This query can be executed here, because it's the "Permissive" mode. Even though, sql_firewall detected it as "unexpected" query and noticed it with a message "WARNING: Prohibited SQL statement".

As you can see, sql_firewall would raise warnings when non-learned/unexpected queries are executed.

Preventing unexpected queries in the Enforcing mode

Next, let's switch sql_firewall to the Enforcing mode to prevent unexpected query execution.

Modify sql_firewall.firewall parameter to "enforcing", and restart PostgreSQL.

sql_firewall.firewall = 'enforcing'

As we did in the Permissive mode, let's execute the same query to get a single row with specifying uid.

testdb=# select * from k1 where uid = 1;
 uid |    uname
-----+-------------
   1 | Park Gyu-ri
(1 row)

testdb=# select * from k1 where uid = 2;
 uid |    uname
-----+-------------
   2 | Nicole Jung
(1 row)

testdb=# select * from k1 where uid = 3;
 uid |   uname
-----+-----------
   3 | Goo Ha-ra
(1 row)

testdb=# 

Here, you can see the query can work same as the Permissive mode. The learned query can be executed correctly without any warning or any error.

Then, let's execute some non-learned query.

testdb=# select * from k1 where uid = 3 or 1 = 1;
ERROR:  Prohibited SQL statement
testdb=#

In the Permissive mode, we just saw a WARNING message. But this time in the Enforcing mode, the query could not be executed and we're noticed that with ERROR because the sql_firewall module prevented the query execution.

This is the differece between the Permissive mode and the Enforcing mode.

And you can see that only two queries are learned (=allowed) in the sql_firewall_statements view at this time.

testdb=# select * from sql_firewall.sql_firewall_statements;
 userid |  queryid  |                        query                        | calls
--------+-----------+-----------------------------------------------------+-------
     10 | 472133445 | select * from sql_firewall.sql_firewall_statements; | 1
     10 | 227323429 | select * from k1 where uid = ?;                     | 4
(2 rows)

testdb=#

Wrap-up

In this entry, I gave a brief introduction to the sql_firewall, how it works and how to use it.

Despite SQL injection attacks are well-known these days, preventing it is not so easy thing, and that's the reason why we still see many security incidents caused by SQL injection attacks.

I think some security solusion at the database layer would be effective to solve that issue because the first-priority target of SQL injection attacks is always "database".

So, if you have to think of preventing SQL injection attacks on PostgreSQL, I think sql_firewall is worth trying.

Happy hacking. :)