title: Permission denied when calling a function when using SECURITY DEFINER even after GRANT ALL to role has been run
body: <p>Running the SQL below I get a permissions issue. </p>
  
  <blockquote>
    <p>[42501] ERROR: permission denied for schema myschema Where: PL/pgSQL function >myschema.test_func() line 8 at SQL statement</p>
  </blockquote>
  
  <p>What permission to I need to grant to allow the SECURITY DEFINER to select/execute functions. ALL PERMISSIONS doesn't seem to work.</p>
  
  <p>Looking at <a href="https://www.postgresql.org/docs/current/sql-grant.html" rel="nofollow noreferrer">https://www.postgresql.org/docs/current/sql-grant.html</a> I don't see anything else I can grant to make this work.</p>
  
  <p>I'm using Postgresql 11. Obviously if I make myrole a super user it works fine. Even putting the grants at the end gives the same error.</p>
  
  <hr>
  
  <p>Edit:</p>
  
  <p>I've not explained very well. The issue is not creating the functions I get the issue when the following line is called:
  <code>SELECT myschema.test_func_2('foo', 'bar') INTO l_table_result;</code></p>
  
  <p>The creating of the function works fine, only when I run <code>select myschema.test_func();</code> do I get the SQL error.</p>
  
  <p>I can't see what other permissions myrole needs in order to call another function.</p>
  
  <pre><code>CREATE ROLE myrole WITH NOLOGIN;
  
  CREATE SCHEMA IF NOT EXISTS myschema;
  
  GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschema TO myrole ;
  GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA myschema TO myrole ;
  GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA myschema TO myrole ;
  
  
  CREATE OR REPLACE
  FUNCTION myschema.test_func()
  RETURNS BOOLEAN
      SECURITY DEFINER
      LANGUAGE plpgsql
  AS $$
  DECLARE
      l_table_result BOOLEAN;
  BEGIN
  
      RAISE LOG 'Will Get This Far ... ';
  
      SELECT myschema.test_func_2('foo', 'bar') INTO l_table_result;
  
      RAISE LOG 'But Not This Far';
  
      RETURN TRUE;
  
  END;
  $$;
  
  /* OWNERSHIP AND PERMISSIONS*/
  ALTER FUNCTION myschema.test_func OWNER to myrole;
  
  
  
  CREATE OR REPLACE
  FUNCTION myschema.test_func_2(
                                  p_foo VARCHAR(10),
                                  p_bar VARCHAR(10)
  )
  RETURNS BOOLEAN
      SECURITY DEFINER
      LANGUAGE plpgsql
  AS $$
  BEGIN
  
      RETURN TRUE;
  
  END;
  $$;
  
  /* OWNERSHIP AND PERMISSIONS*/
  ALTER FUNCTION myschema.test_func_2(VARCHAR, VARCHAR) OWNER to myrole;
  
  
  
  
  select myschema.test_func();
  </code></pre>