How to make PostreSQL functions private (inaccessible to end users)?

Question

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?

Answer 1

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 ;