title: postgres: access to schema objects within a function
body: <p>Here's my problem:</p>
  
  <p>I have Schema A, owned by User 1, that owns all objects.</p>
  
  <p>I've created schema B, owned by User 2.</p>
  
  <p>I have granted User 2 all privileges to the objects owned by User 1.</p>
  
  <p>When logged in as User2, I have no problem Selecting, Updating, etc... tables owned by User 1.
  Makes sense.</p>
  
  <p>Now, when logged in as User 2, I want to create a Function that is Selecting some data from tables owned by User 1.</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION -- &lt;-- function owned by User2 (in schema B)
    RETURNS TABLE(
    table columns...)
   AS
  $func$ 
  BEGIN
  
          RETURN QUERY
          SELECT * 
          FROM table;  -- &lt;-- table owned by User 1 (in schema A)
  
  END;
  $func$
  LANGUAGE plpgsql
  ;
  </code></pre>
  
  <p>The Selects all by themselves work fine.
  But when I put them in a Function, I get the error:</p>
  
  <pre><code>ERROR: permission denied for schema schema A
  SQL state: 42501
  </code></pre>
  
  <p>It seems to me that User 2 has the access to User 1's tables...because individual Selects, Inserts, etc... work fine.</p>
  
  <p>It's only when I put these statements within the Function, that i get a permission error.</p>
  
  <p>Obviously I'm missing a permission, or my general design is wrong.</p>
  
  <p>Does anyone have any insight?</p>
  
  <p>thanks so much.</p>
  
  <p>EDIT:</p>
  
  <p>These are the grants i originally issued:</p>
  
  <pre><code>GRANT usage on schema schemaA to user2;
  GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schemaA TO user2;
  GRANT USAGE ON ALL SEQUENCES IN SCHEMA schemaA TO user2;
  GRANT SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA schemaA TO user2;
  GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA schemaA TO user2;
  </code></pre>
  
  <p>After Issuing this grant, my permission error went away:</p>
  
  <pre><code>Grant ALL on schema schemaA to user2;
  </code></pre>
  
  <p>So, what is the difference between the the grants I issued versus the Grant ALL statement?
  Is there a single permission I could have granted individually to make this work?</p>
  
  <p>Thanks.</p>