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.
Here is the ouptput to the \dn+ command:
List of schemas
Name | Owner | Access privileges | Description
------------+-----------+-------------------------------+------------------------
operations | schemaB | |
public | postgres | postgres=UC/postgres +| standard public schema
| | =UC/postgres |
schemaA | schemaA | schemaA=UC/schemaA +|
| | schemaB=U/schemaA |
\dn+ schema_A
show you (when logged in withpsql
)? – dezso Oct 6 '14 at 14:14