1

How can I encrypt PostgreSQL functions/procedures like SQL Server’s WITH ENCRYPTION?

In SQL Server, we can define stored procedures and functions using WITH ENCRYPTION so that users cannot view the source code even if they have database access.

I want the same level of protection in PostgreSQL, so that if a customer gets database access (or even the DB password), they cannot read my business logic stored in functions and procedures.

Is there a way to do this in PostgreSQL?

Improve this question

3 Answers 3

Reset to default
4

No, you cannot do it in PostgreSQL.

But also check these:

In short, if you control the server, there are other ways to prevent users from seeing your code; if you don't control the server, there's nothing you can do to prevent users from seeing your code.

Improve this answer
1
  • 1
    Even if you control the server, everybody can read the function body. And if you are considering changing the permissions on catalog tables, that's a great way to break all client tools and sometimes to make an upgrade fail. Commented Jan 7 at 21:42
3

Mustaccio's correct answer is talking about functions whose code is part of the CREATE FUNCTION statement and stored in the system catalog pg_proc.

But there are also user-defined functions written in C. Such functions are distributed as a shared library that needs to be installed on the database server, and it takes a superuser to create such functions (because C code running inside the database can bypass all mechanisms that guarantee the integrity of the database).

So one option for you to put business logic into the database while hiding the source code would be to use C functions. If it sounds like that is not the proper tool for the task, I'd happily concur. But then, perhaps the correct solution is to put the secret business logic into the client application rather than into the database.

Improve this answer
0

  1. Build the function, encrypt the body with pgcrypto and put that in a string fed into a PL/pgSQL EXECUTE:

    EXECUTE pgp_sym_decrypt(encrypted_function_definition, pass);
result := SELECT the_function(args);
DROP FUNCTION the_function(arg_types);

    That goes into a wrapper security definer function with the same signature and uses a password accessible to the definer but not the invoker. The pass can even sit in a table guarded by RLS and basic permission system.
    If a user checks what the wrapper does and crafts a new one without the drop, so that they can inspect it afterwards, it won't work because to them, as the new definer, the pass referenced by the original definer is inaccessible.

    As an alternative to the create-invoke-drop, you can also try and transform some of them into prepared statements that can use prepare-execute-deallocate, or into DO blocks that are one-off by design.

  2. Rather than encrypting, you can relocate the functions entirely. They can live on a separate, locked-down db on the same cluster, available for calls via postgres_fdw or PL/proxy from security definer functions on your main db.

  3. As already pointed out by @Laurenz Albe, you can also move them off the db, onto the filesyste. In a standard build, that only works for C language functions.

  4. With pg_http you can set up your functions as microservices wherever you like. All you leave on your db are functions that just call an API via pg_http. Again, that API can simply tell a background process to come in, define the function, run it with the user-provided params, then remove it.

In each case, it obviously complicates planning, optimisation and maintenance since the db also has a bit harder time reasoning about the logic if it's all encrypted, obfuscated and/or hidden away.

Only the 1st and 3rd options would work with MVCC (transactions) like regular functions. The 2nd option could work like that if you use a PostgreSQL extension/fork that can expose and share the transaction between multiple underlying nodes.

Improve this answer