Home > Back-end >  PostgreSQL - dangerous built-in functions to avoid for dynamic queries?
PostgreSQL - dangerous built-in functions to avoid for dynamic queries?

Time:09-01

In our software product, we want to add a feature that allows advanced users to define their own database views by entering any select statement they want, which will then be used in a "create view" statement.

While this is arguably dangerous in and by itself, are there any PostgreSQL functions that add dangers greater than "seeing everything in the database" and "overstraining the database by joining too many tables"?

I think of functions that would allow the execution of DDL statemtents, accessing the file system, executing commands on OS level etc.

CodePudding user response:

Functions that are publicly available will be your least problem. All functions that can access the operating system are restricted to superusers and members of certain predefined roles like pg_write_server_files or pg_execute_server_programs.

Still, I think that this is a bad idea: anybody who can submit an SQL statement can easily launch a successful denial-of-service attack, either hogging the CPU or filling the disk with temporary files.

  • Related