2

When writing a set of PostgreSQL functions with procedural languages, is it possible to make some of the supporting functions private and not accessible by end users?

I wanted to refactor some common patterns into SQL functions, but are concerned about introducing potential SQL injection in the function parameters. For example, I wanted to implement a copy_table(src regclass, target text) function for copying a table to new table (in a previous question), the second parameter can be subject to SQL-injection if unquoted. (Quoting the 2nd parameter has its own issues as discussed there). On the one hand, I wanted to write this function as a helper function for other public-facing functions; on the other, I don't want to expose it to everyone for security concerns.

Is there a way to hide the dangerous supporting functions so that they can only be called by my functions?

  • 2
    I think you need to state why you think private/public would have anything to do with SQL-injection. SQL-Injection is called by a failure to escape parameters. I don't believe it has anything to do with the question. – Evan Carroll Mar 18 '18 at 1:10
  • Also, when you say "supporting functions" what do you mean? – Evan Carroll Mar 18 '18 at 1:10
  • This has nothing to do with SQL Injection. First, every function that gets any parameter unquoted is a target for SQL Injection. Second, it's not clear what the issues you were having that are not addressed in the answers there. Third your design is pretty flawed, which is exactly what I answered with a few days ago If your public facing functions are dropping and recreating tables in unspecified schemas, you have a large footprint for attack. – Evan Carroll Mar 18 '18 at 7:32
4

Just use REVOKE to control the permissions Postgres assigns to roles.

Documentation:

The REVOKE command revokes previously granted privileges from one or more roles. The key word PUBLIC refers to the implicitly defined group of all roles.

See the description of the GRANT command for the meaning of the privilege types.

CREATE FUNCTION nocando()
RETURNS void AS $$
  SELECT null::void
$$ LANGUAGE sql;

REVOKE ALL
  ON FUNCTION nocando ()
  FROM PUBLIC ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.