Postgresql – Attacks on Postgresql listening for requests on ‘localhost’

best practicespostgresqlSecurity

I am using jboss server for my servlets and the postgresql as the database server, listening on port 5432 on localhost. My jboss server is bound to a public IP. I want to know that since my postgesql is bound only to the localhost, is there any chance of an attack at all.
Will any one be able to see the all the table data even if they have the user name and password for the database?

Best Answer

Starting by securing PostgreSQL according to Erwins answer is a good start. However, there are other ways to attack and gain access to data. Even if you are after data as an attacker, one wouldn't assume that the database is directly accessible from the internet.

But data might be accessible indirectly due to flaws in the application server, in this case JBoss, and its configuration or in the application code (sql injection).

JBoss had a seriuos security vulnerability about a year ago that allowed you to gain root access - and as a consequence full access to any PostgreSQL server on localhost. http://www.darknet.org.uk/2011/02/jboss-autopwn-jsp-hacking-tool-for-jboss-as-server/

UPDATE:

Yes, Ashwin, using prepared statements or stored procedures/functions is a good way to minimize the risks of sql injections attacks. OWASP has a check list for prevention of SQL injections. https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_1:_Prepared_Statements_.28Parameterized_Queries.29

One advantage of stored procedures/functions is the granular control of permissions. For example you can grant access for the application user to only be able to run specific procedures/functions, whilst access is denied to the underlying tables and other objects. The procedure/function is instead run by the permission of the owner of the procedure/function (a user also with restrictive permissions, just able to operate on the specific tables). This setting can be controlled with SECURITY DEFINER, as in this example function.

CREATE OR REPLACE FUNCTION myschema.myfunction() RETURNS void AS 
$BODY$
BEGIN
    INSERT INTO myschema.mytable(msg) VALUES('value goes here');
END;
$BODY$  
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
ALTER FUNCTION myschema.myfunction() OWNER TO myinsertupdateuser;    
GRANT EXECUTE ON FUNCTION myschema.myfunction() TO myappuser;

This function can be executed by myappuser, but myappuser can not access myschema.mytable directly. In other words myappuser can only run the code you specify in the function. The function is run as myinsertupdateuser, which is assumed to have insert permission on the myschema.mytable table.

Related Question