Lists: | pgsql-sql |
---|
From: | "Luca Giandoso" <luca(dot)giandoso(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | information_schema for all users |
Date: | 2006-07-09 07:52:35 |
Message-ID: | 3ac7d4e0607090052v4e401a85k927b81b002149e88@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
I wold like to make a plpgsql function that return column names and
their data types of a specific table.
I have done this using:
CREATE OR REPLACE FUNCTION _get_table_definition(refcursor) RETURNS
refcursor SECURITY DEFINER AS
$$
DECLARE
BEGIN
OPEN $1 FOR SELECT column_name as field_name, data_type as
field_type FROM information_schema.columns WHERE
table_name='_table_name';
RETURN $1;
END;
$$ LANGUAGE plpgsql;
but it works only with the database owner although i have used
"SECURITY DEFINER".
How can I make it works for everyone that calls the function?
I use PostgreSQL 8.1.4
From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Luca Giandoso <luca(dot)giandoso(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: information_schema for all users |
Date: | 2006-07-09 18:10:10 |
Message-ID: | 20060709181010.GA63805@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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