1

Here's my problem:

I have Schema A, owned by User 1, that owns all objects.

I've created schema B, owned by User 2.

I have granted User 2 all privileges to the objects owned by User 1.

When logged in as User2, I have no problem Selecting, Updating, etc... tables owned by User 1. Makes sense.

Now, when logged in as User 2, I want to create a Function that is Selecting some data from tables owned by User 1.

CREATE OR REPLACE FUNCTION -- <-- function owned by User2 (in schema B)
  RETURNS TABLE(
  table columns...)
 AS
$func$ 
BEGIN

        RETURN QUERY
        SELECT * 
        FROM table;  -- <-- table owned by User 1 (in schema A)

END;
$func$
LANGUAGE plpgsql
;

The Selects all by themselves work fine. But when I put them in a Function, I get the error:

ERROR: permission denied for schema schema A
SQL state: 42501

It seems to me that User 2 has the access to User 1's tables...because individual Selects, Inserts, etc... work fine.

It's only when I put these statements within the Function, that i get a permission error.

Obviously I'm missing a permission, or my general design is wrong.

Does anyone have any insight?

thanks so much.

EDIT:

These are the grants i originally issued:

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;

After Issuing this grant, my permission error went away:

Grant ALL on schema schemaA to user2;

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?

Thanks.

3
  • 1
    Did you use: 'GRANT ALL ON SCHEMA SchemaA TO User2;' ? Commented Oct 7, 2014 at 8:17
  • @MaxGruzin - Thanks. That did ultimately work. Please see my edit. Commented Oct 7, 2014 at 13:04
  • I've tried to recreate your case and everything seems to be working fine. I'd recommend revising grants to the table you use in your query. It must have something like that: GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE yourtable TO user2;. Also verify if user2 has the right to execute functions: create a test function without any table queries, simply returning null Commented Oct 8, 2014 at 6:17

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.