Date: Sun, 9 Jul 2006 12:10:10 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Luca Giandoso <luca.giandoso@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: information_schema for all users
Message-ID: <20060709181010.GA63805@winnie.fuhr.org>
References: <3ac7d4e0607090052v4e401a85k927b81b002149e88@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3ac7d4e0607090052v4e401a85k927b81b002149e88@mail.gmail.com>

On Sun, Jul 09, 2006 at 09:52:35AM +0200, Luca Giandoso wrote:
> I wold like to make a plpgsql function that return column names and
> their data types of a specific table.

Do you have a reason for returning a cursor instead of SETOF some
type?  In versions prior to 8.1 you could create a custom type for
the return columns; in 8.1 you could use OUT parameters and return
SETOF record.

[snip function that returns a cursor over information_schema.columns]

> but it works only with the database owner although i have used
> "SECURITY DEFINER".

The information_schema privilege checks are based on current_user,
which is apparently evaluated when you fetch rows from the cursor,
not when you open the cursor.  Here's a simple example; we'll create
the following function as user alice:

  CREATE FUNCTION testfunc(refcursor) RETURNS refcursor AS $$
  BEGIN
      RAISE INFO 'current_user = %', current_user;
      OPEN $1 FOR SELECT current_user;
      RETURN $1;
  END;
  $$ LANGUAGE plpgsql SECURITY DEFINER;

First we'll call the function as alice; notice that the current_user
displayed by the RAISE statement is the same as the current_user
fetched by the cursor:

  test=> BEGIN; SELECT testfunc('curs'); FETCH curs; COMMIT;
  BEGIN
  INFO:  current_user = alice
   testfunc 
  ----------
   curs
  (1 row)
  
   current_user 
  --------------
   alice
  (1 row)
  
  COMMIT

Now we'll call the same function as user bob; notice that the
current_user displayed by the RAISE statement is "alice" because
of SECURITY DEFINER but that the current_user displayed when fetching
from the cursor is "bob":

  test=> \c - bob
  Password for user bob: 
  You are now connected as new user "bob".
  test=> BEGIN; SELECT testfunc('curs'); FETCH curs; COMMIT;
  BEGIN
  INFO:  current_user = alice
   testfunc 
  ----------
   curs
  (1 row)
  
   current_user 
  --------------
   bob
  (1 row)
  
  COMMIT

I don't know if this behavior is intentional but that's how it
currently works.  You could avoid it by returning SETOF some type
rather than a cursor, or you could query the PostgreSQL system
catalogs directly instead of using information_schema.  If you're
returning the results of a simple query, and if you can make that
query work without SECURITY DEFINER, then you could use a view
instead of a function.

-- 
Michael Fuhr