Protecting the function and stored procedure on postgres

Question

We have developed stored procedures and functions in a Postgres database which is performing the ETL process. This is deployed at the client system so we need to protect our code been mis-used/modified by the client place.

Is there any way to protect from viewing the stored procedure / function created. Current we have provided the dedicated user account which don't have the access to the function and has access only to the tables. Since the database is at the client place they will be able to access the system with the administration account and gain access to the function and stored procedure.

We would like to set the password/Encrypt the procedure for modification. But should have the execute permission without the password for procedure and function.

We are using the postgres 11 for our development.

Answer 1

You can achieve this by following way.

1) Keep the superuser credential with you and revoke all the access related to your schema.

REVOKE ALL ON schema public FROM public;

Edit: after the above command, only a superuser may create new objects inside the public schema, which is not practical. Assuming a non-superuser foo_user should be granted this privilege, this should be done with:

GRANT ALL ON schema public TO foo_user;

To know what ALL means for a schema, we must refer to GRANT in the doc. It appears that for a schema it means CREATE and USAGE.

So the solution for your problem is created different type of PostgreSQL DB users with different permission.

Same way creates one PostgreSQL user for the client gives that detail.

Hope this will solve your problem.