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.