PostgreSQL Denial Of Service Attack

In this blog post I am going to simulate a potential Denial of Service attack by hammering the postgresql database with temporary table that will exhaust the PostgreSQL PGDATA directory.

Basically by default PostgreSQL doesn’t have a “dedicated” Temporary tablespace.

show temp_tablespaces ;

pg_temp_tbs

So, if you create a “temporary” table this table will be stored under base directory under $PGDATA directory (which is the main PostgreSQL directory).

To simulate, I will create an account called “emad_usr” under a database called “emad” with no really high permissions as you will see:

create user emad_usr with password ’emad_123′;

 GRANT CONNECT ON DATABASE emad TO emad_usr;

 // I will connect using the account and will create a temporary table

 psql -h localhost -U emad_usr  -d emad

 create temporary table tempo as select * from generate_series(1,1000000000000000000000000000000000000);

This will eventually lead to fully filling the PGDATA directory and will cause the service to be down !

And the following error will be thrown:

ERROR: could not write to tuplestore temporary file: No space left on device

Methods to protect against this type of Denial of Service Attack:

1. The first method to avoid such attack you need to define a dedicated temporary tablespacein a directory different than $PGDATA directory:

mkdir /var/lib/postgresql/tmp_tbs

psql

create tablespace temp_tablespc location ‘/var/lib/postgresql/tmp_tbs’;

alter system set temp_tablespaces = ‘temp_tablespc’;

select pg_reload_conf();

 show temp_tablespaces ;

To confirm the setup, a temporary table will be created:

create temporary table tempo as select * from generate_series(1,100);

select pg_relation_filepath(‘tempo’);

p_temp_tbs2

2. Second method to protect against Denial of Service Attack is to set a limitation of the temp file sizethrough the parameter “temp_file_limit” :

show temp_file_limit;

By default -1 means unlimited

pg_temp_tbs3

set temp_file_limit=’4MB’;

If somebody attempts to create a very large temporary table an error will be thrown:

create temporary table tempo as select * from generate_series(1,1000000000000000000000000000000000000);

ERROR:  temporary file size exceeds temp_file_limit (4096kB)

3. A third way is revoking create temporary tables from a database level , please note that this might have an impact if the application requires sorting or temp file operations:

psql

REVOKE TEMPORARY ON DATABASE emad FROM PUBLIC;

psql -h localhost -U emad_usr  -d emad

emad=> create temporary table tempo as select * from generate_series(1,10);

ERROR:  permission denied to create temporary tables in database “emad”

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

One thought on “PostgreSQL Denial Of Service Attack

  1. Pingback: Easter reading material – Oracle Business Intelligence

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s