Postgres 9.3 - permission denied for schema error with New role on new function

Question

On exiting DB and existing schema I have created a new function. I am supposed to create a new role so that one can execute the new function that I have created.

Function has selects and inserts on some tables in public schema.(Db and schema are already existing)

--I have created new role with below SQL:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

   DO $BODY$
   BEGIN
   CREATE ROLE new_role LOGIN;
   EXCEPTION
       WHEN duplicate_object THEN

   RAISE DEBUG '% (%)', SQLERRM, SQLSTATE;
   END;
   $BODY$;

   ALTER ROLE new_role ENCRYPTED PASSWORD 'abc123';
   COMMIT;

---

--Below are GRANT queries:

  GRANT CONNECT ON DATABASE "existing_db" TO new_role;
   GRANT ALL PRIVILEGES ON SCHEMA public TO new_role;
   grant all ON FUNCTION new_fun(name text, address text) TO new_role

connected to Database using new_role and tried to select from new_fun prompts me with "permission denied for schema public".

I am not sure if I need to give other grant permission here. Please help.

Thanks, -Div

Answer 1

postgresql 9.1 - access tables through functions

I did not know that had to revoke all on public from Public: foloowed steps from above link.

REVOKE ALL ON SCHEMA public FROM public;

got it now.

Thanks