4

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

4

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.

  • Thanks i will look into it before accepting it as answer as I doubt it will work . It looks complicated as how will i relate the pg_temp to users/roles ? as name suggest is it temporary do i have to define it on every connection ? As o now I am exploring schema option . Thanks . – Abhijit Gujar Oct 14 '17 at 13:19
  • In fact the answer should be: there is no way to completely hide a function from all other users. You can forbid to execute it but cannot hide it. My answer shows the best you can do to make a function invisible to others. – klin Oct 14 '17 at 21:48
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.