How to completely hide a function from all other users and roles?

Question

Database : Postgres 9.6

Use case : I have a function that needs to be hidden, only a particular role/user can see it. I don't even want to expose even the name or body of function to other users/roles.

For example if I have a user named test1 then he should not have any knowledge of existence of function called top_secret_function_1 (not via PgAdmin not via any other command.)

Only the the users/roles that I want to see this function should see this function.

Importance : This function executes some important logic which I don't want to expose but still I need to give some other database access rights to some other users.

Update 1

Klin suggested use of pg_temp but its does not seems effective but his suggestion triggered a idea of using different schema and then restrict the users/roles to schema.

when i reconnect the database the secret function is no longer valid in pg_temp ! I cannot use pg_temp as suggested because then I will have to define function in each users pg_temp schema when he connects

Answer 1

You can revoke and grant access privileges from/to specific Postgres roles but the name and source of the function will be still visible.

To keep the function in top secret create it in the session in which it is needed, in the schema pg_temp. It is a schema associated with the session for keeping temporary resources. It is automatically created when needed and automatically dropped at the end of the session.

create or replace function pg_temp.top_secret_function()
returns text language sql as $$
    select 'Top secret!'::text;
$$;

select pg_temp.top_secret_function();

CREATE FUNCTION
 top_secret_function 
---------------------
 Top secret!
(1 row)

There is no need to drop the function, it will be removed when the session terminates. Nobody (even you as the same user in another concurrent session) can see the function.

Answer 2

As of Now there is no inbuilt functionality in the PostgreSQL to to give a particular user/role only execute access . The function source code is always visible. klin pointed it out right "source of the function will be still visible.", but somehow its misleading as he added some scripts that does not exactly satisfy the question condition.

I really hope this feature comes soon in postgres.