0

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

jfbaro
  • 301
  • 2
  • 10
  • 1
    There are no "public" and "private:" functions, tables or views in a database. Access to specific objects is controlled through permissions. If you don't want users to access some functions, don't give those permissions. Don't create accounts that can see everything – Panagiotis Kanavos Nov 01 '21 at 19:03
  • Thanks, @PanagiotisKanavos, It makes sense to me. I wondered if blocking access to sub_functionA and granting access to FunctionA (that uses sub_functiona) could result in problems. – jfbaro Nov 01 '21 at 19:25
  • 1
    Search on SECURITY DEFINER here [CREATE FUNCTION](https://www.postgresql.org/docs/current/sql-createfunction.html). – Adrian Klaver Nov 01 '21 at 20:52

1 Answers1

3

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263