- I wrote a Java web service using Spring that handles REST calls from
the Internet. - The web service uses a Postgres database underneath to store, modify and read data.
- The Postgres database is used exclusively by
this web service, no other program accesses the database.
The web service uses a database user that has all rights in the database schema (dropping tables, modifying tables, etc.).
Would there be any tangible benefit in using a database user for this web service, that only has rights to modify table entries (select, insert, update, etc.), but no rights to execute DDL statements?
Or would this be over engineered?
Best Answer
The security argument against using DDLs is neither completely wrong nor completely right.
If a web service is compromised, and an attacker manages to issue arbitrary
select,insert,updateordeletestatements, the damage they can cause is not really different from the damage they can cause by adrop tableormodify tablestatement. Ok, when you system allows installing of new stored procedures or other forms of executable code by DDLs, an attacker might cause more damage than just by destroying or extracting some data, and it is probably a good idea to forbid such DDLs.But with or without DDL access rights, the countermeasures to make a system robust against such attacks are 100% the same:
be extremely careful with dynamic SQL, and make sure only statements prepared by you can be executed, with properly validated input parameters
have regular backups in place
keep your system up-to-date with regular updates
organizational measures
When the system was successfully attacked, you will usually have to make a full restore of the system, including data and DB schema. If an attacker had no execution rights for DDL statements, I don't think any professional DB admin will try only to restore the data based on that information, they will usually restore the whole database anyway.