Skip site navigation (1) Skip section navigation (2)

User-Id Tracking when Portal was started

From: Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp>
To: PgHacker <pgsql-hackers(at)postgresql(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Florian Pflug <fgp(at)phlo(dot)org>, Robert Haas <robertmhaas(at)gmail(dot)com>
Subject: User-Id Tracking when Portal was started
Date: 2012-07-02 14:55:39
Message-ID: CADyhKSVXDZY7yy3LXy6kgLaKi4mAPMYqP5w7bc4mXMUpQZTB5w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
The attached patch is delivered from the discussion around row-level
access control feature. A problem Florian pointed out is refcursor
declared in security definer function. Even though all the permission
checks are applied based on privilege of the owner of security-definer
function in case when it tries to define a cursor bound to a particular
query, it shall be executed under the credential of executor.
In the result, "current_user" or "has_table_privilege()" will return
unexpected result, even if it would be used in as a part of security
policy for each row.

This patch enables to switch user-id when user tried to execute
a portal being started under the different user's credential.

postgres=# CREATE FUNCTION f1(refcursor) RETURNS refcursor
postgres-#     SECURITY DEFINER LANGUAGE plpgsql
postgres-#     AS 'BEGIN OPEN $1 FOR SELECT current_user,* FROM t1;
postgres'#         RETURN $1; END';
CREATE FUNCTION

postgres=# SET SESSION AUTHORIZATION alice;
SET
postgres=> BEGIN;
BEGIN
postgres=> SELECT f1('abc');
 f1
-----
 abc
(1 row)

postgres=> FETCH abc;
 current_user | a |  b
--------------+---+-----
 kaigai       | 1 | aaa          <=== 'abc' was started under the
kaigai's privilege
(1 row)

postgres=> SELECT current_user;
 current_user
--------------
 alice
(1 row)

postgres=> DECLARE xyz CURSOR FOR SELECT current_user, * FROM t1;
DECLARE CURSOR
postgres=> FETCH xyz;
 current_user | a |  b
--------------+---+-----
 alice        | 1 | aaa
(1 row)

postgres=> RESET SESSION AUTHORIZATION;
RESET
postgres=# FETCH xyz;          <=== 'xyz' was started under the
kaigai's privilege
 current_user | a |  b
--------------+---+-----
 alice        | 2 | bbb
(1 row)


BTW, same problem will be caused in case when security label of
the client would be switched. Probably, a hook should be injected
around the places where I patched with this patch.

Thanks,
-- 
KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>

Attachment: pgsql-v9.3-track-userid-of-portal.v1.patch
Description: application/octet-stream (10.3 KB)

Responses

pgsql-hackers by date

Next:From: Xiong HeDate: 2012-07-02 14:57:40
Subject: How to extend server side encoding GBK
Previous:From: Robert HaasDate: 2012-07-02 14:53:31
Subject: Re: pgsql: Make walsender more responsive.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group