What is the idiomatic way of having PUBLIC and PRIVATE FUNCTIONS in Postgresql?

Question

Please,

I am working on a PoC in supabase(PG 13.3) in my free time, and although I am breaking the main functions into smaller functions (for reuse purposes), I want the applications/clients to only have access to three of them ("public" functions). So most of the functions are only used by these three main functions.

Considering a managed solution (RDS or Google Cloud SQL), what's the best way to limit the access to applications/clients to only these three functions and prevent them from seeing and executing the other "private" functions.

I googled it but didn't find an answer yet.

Any help would be much appreciated.

Thanks

Answer 1

You can take two approaches:

1. Put the auxiliary functions that you don't want users to use in a different schema that is not on the search_path and don't document them.

   Then they can only be called with a different schema qualification, so they won't be called by accident. However, that does not prevent users from using those functions, it just discourages them.

2. REVOKE permissions to the auxiliary functions from PUBLIC and give them only to the owner of the main functions. Then declare those main functions to be SECURITY DEFINER (and make sure to set search_path on them!).

   The main drawback with this approach is that all functions are executed as the owner, so you cannot use PostgreSQL's normal permission checks to make sure that the caller does not access any data for which it has no permissions.

I personally would prefer the first method.