Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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             | 
share|improve this question
    
Which user calls this function? Also, what does \dn+ schema_A show you (when logged in with psql)? –  dezso Oct 6 '14 at 14:14
    
Schema B will own and call the function. Command output added to post. –  iamtheoracle Oct 6 '14 at 14:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.