Обсуждение: about the RULE system
Hi All,
This is something that bugs me for some time now. I have:
(as user postgres I do)
CREATE TABLE debi (id int, name text);
REVOKE ALL ON debi FROM public;
CREATE FUNCTION piti() RETURNS "trigger" AS $$ DECLARE me RECORD; BEGIN
select * into me FROM pg_authid; new.id := me.oid; new.name :=
me.rolname; return new; END $$ LANGUAGE plpgsql;
INSERT INTO debi (id,name) VALUES (22, 'jklsdf');
INSERT 0 1
INSERT INTO debi (id,name) VALUES (22, 'jklsdf');
INSERT 0 1
CREATE VIEW mdebi as SELECT * from debi;
GRANT SELECT, insert on mdebi to public;
(now I become common user)
SELECT * from debi;
ERROR: permission denied for relation debi
SELECT * from mdebi;
id | name
----+----------
10 | postgres
10 | postgres
(2 rows)
So far so good. But the insert fails:
INSERT INTO mdebi (id,name) VALUES (22, 'jklsdf');
ERROR: permission denied for relation pg_authid
CONTEXT: SQL statement "SELECT * from pg_authid"
PL/pgSQL function "piti" line 1 at select into variables
So it looks like the VIEW have elevated my security level thus allowing
me to access the DEBI table (SELECT statement), but inside of the
TRIGGER within DEBI I'm back with my original security level. This is
weird.
I thought trigger functions execute at root/postgres security level?
But definitely I though, once I've passed VIEW access control, I'm prity
mutch root/postgres superuser.
Apparently not so.
Why I can "SELECT * FROM pg_authid" within SELECT, and I cannot do that
within INSERT (to the same table/view) is a real mistery to me.
But, is there a way around it? (meaning: to have a trigger function do
it's security related job on a table *not* publically accesable, but
available for public access only through a specially designed VIEW).
One thing though. I *realy* *really* *need* to do the job using trigger
functions. Functions called from within the RULE-set are not an option
here - although I wouldn't like to elaborate.
Thenx
--
-R
Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
> I thought trigger functions execute at root/postgres security level?
No. You probably want to make that function SECURITY DEFINER so it
executes as the owner, but this isn't default for triggers.
regards, tom lane
On Wed, 2006-12-13 at 14:01 -0500, Tom Lane wrote: > Rafal Pietrak <rafal@zorro.isa-geek.com> writes: > > I thought trigger functions execute at root/postgres security level? > > No. You probably want to make that function SECURITY DEFINER so it > executes as the owner, but this isn't default for triggers. Hmmm. Have checked it, and it does not look promissing. Obviously, when I define function with "SECURITY DEFINER" I need to limit access to that function. But.... "REVOKE ALL ON FUNCTION piti() FROM PUBLIC" Doe not seam to have any effect on functions installed as a trigger. I have just checked it, and my 'common user' is able to "INSERT INTO debi (id,name) VALUES (22, 'jklsdf')" after the above REVOKE. How do I limit access to such function? Even worse, my 'common user' was able to CREATE TEMPORARY TABLE, and install that function on that table, although "PUBLIC.SCHEMA" is available for USAGE only for those users. I generally accept 'common user' to have the ability to create temporary tables, but the takeover trigger function designed to other purposes AND DEFINED with "security definer" is not really acceptable. How can I limit that sort of missuse? -- -R
On Wed, 2006-12-13 at 15:36, Rafal Pietrak wrote: > On Wed, 2006-12-13 at 14:01 -0500, Tom Lane wrote: > > Rafal Pietrak <rafal@zorro.isa-geek.com> writes: > > > I thought trigger functions execute at root/postgres security level? > > > > No. You probably want to make that function SECURITY DEFINER so it > > executes as the owner, but this isn't default for triggers. > > Hmmm. Have checked it, and it does not look promissing. > > Obviously, when I define function with "SECURITY DEFINER" I need to > limit access to that function. But.... > > "REVOKE ALL ON FUNCTION piti() FROM PUBLIC" > > Doe not seam to have any effect on functions installed as a trigger. Does your "common user" have the permission to create users?
On Wed, 2006-12-13 at 15:43 -0600, Scott Marlowe wrote:
> On Wed, 2006-12-13 at 15:36, Rafal Pietrak wrote:
> >
> >
> > "REVOKE ALL ON FUNCTION piti() FROM PUBLIC"
> >
> > Doe not seam to have any effect on functions installed as a trigger.
>
> Does your "common user" have the permission to create users?
No (although the one I've initially tested this scenario on, was in a
group that did have that permission).
But this time I've tested this on user "niby":
\du niby
List of roles
Role name | Superuser | Create role | Create DB | Connections | Member
of
-----------+-----------+-------------+-----------+-------------+-----------
niby | no | no | no | no limit |
(1 row)
CREATE TEMP TABLE mini (id int, name text, fix int, emul text);
CREATE TRIGGER adad BEFORE INSERT ON mini FOR EACH ROW EXECUTE
PROCEDURE piti();
INSERT INTO mini (name,emul,id,fix) VALUES
('cz_'||(random()*1000000)::integer,'jasdklad', 130003, 1012 );
INSERT 0 1
----------------------------------------------
where piti() is the function I've described earlier, and did REVOKE ALL
on that function.
*all* the above statements are executed as user "niby". The SCHEMA has:
REVOKE ALL; GRANT USAGE; It *does*not* have "GRANT CREATE".
I hope you can copy the results.
--
-R
>>> "REVOKE ALL ON FUNCTION piti() FROM PUBLIC"
>>>
>>> Doe not seam to have any effect on functions installed as a trigger.
>>
>> Does your "common user" have the permission to create users?
>
> No (although the one I've initially tested this scenario on, was in a
> group that did have that permission).
[...]
> I hope you can copy the results.
I can indeed recreate something similar here on PostgreSQL 8.1.4.
Permissions on a trigger function seem to not be checked,
and I can execute a function for which I have no privileges.
I consider this a security leak - or am I missing something?
Here is a _complete_ example:
As superuser, create a trigger function that selects from pg_authid
with SECURITY INVOKER, and REVOKE EXECUTE FROM public:
test=# \c test postgres
You are now connected to database "test" as user "postgres".
test=# CREATE OR REPLACE FUNCTION insert_oid() RETURNS trigger AS
test-# $$BEGIN SELECT oid INTO NEW.useroid FROM pg_catalog.pg_authid
WHERE rolname = user; RETURN NEW; END;$$
test-# LANGUAGE plpgsql STABLE STRICT SECURITY DEFINER;
CREATE FUNCTION
test=# REVOKE EXECUTE ON FUNCTION insert_oid() FROM public;
REVOKE
test=# SELECT proacl FROM pg_catalog.pg_proc WHERE proname =
'insert_oid';
proacl
-----------------------
{postgres=X/postgres}
(1 row)
As normal user, try to execute the function or select from
pg_catalog.pg_authid directly, both fail as expected.
test=# \c test laurenz
You are now connected to database "test" as user "laurenz".
test=> SELECT insert_oid();
ERROR: permission denied for function insert_oid
test=> SELECT oid FROM pg_catalog.pg_authid WHERE rolname = user;
ERROR: permission denied for relation pg_authid
Create a temporary table, define a trigger BEFORE INSERT using the
function that we cannot execute:
test=> CREATE TEMP TABLE lautest (id integer PRIMARY KEY, useroid oid
NOT NULL);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"lautest_pkey" for table "lautest"
CREATE TABLE
test=> CREATE TRIGGER insert_oid BEFORE INSERT ON lautest FOR EACH ROW
EXECUTE PROCEDURE insert_oid();
CREATE TRIGGER
Insert a row into the table.
The trigger function is executed, and I have selected a value from
pg_authid!
test=> INSERT INTO lautest (id) VALUES (1);
INSERT 0 1
test=> SELECT * FROM lautest;
id | useroid
----+---------
1 | 10
(1 row)
Yours,
Laurenz Albe
Looks like this thread have died away.
But since this permission check looks like a security issue to me too,
I'd really apreciate someones explanation on why it is not ... if it is
not.
But if it is a security leak I'd like to pass it over as bug report - so
it does not disapear from sight.
-R
On Thu, 2006-12-14 at 12:51 +0100, Albe Laurenz wrote:
> >>> "REVOKE ALL ON FUNCTION piti() FROM PUBLIC"
> >>>
> >>> Doe not seam to have any effect on functions installed as a trigger.
> >>
> >> Does your "common user" have the permission to create users?
> >
> > No (although the one I've initially tested this scenario on, was in a
> > group that did have that permission).
> [...]
> > I hope you can copy the results.
>
> I can indeed recreate something similar here on PostgreSQL 8.1.4.
>
> Permissions on a trigger function seem to not be checked,
> and I can execute a function for which I have no privileges.
>
> I consider this a security leak - or am I missing something?
>
> Here is a _complete_ example:
>
> As superuser, create a trigger function that selects from pg_authid
> with SECURITY INVOKER, and REVOKE EXECUTE FROM public:
>
> test=# \c test postgres
> You are now connected to database "test" as user "postgres".
> test=# CREATE OR REPLACE FUNCTION insert_oid() RETURNS trigger AS
> test-# $$BEGIN SELECT oid INTO NEW.useroid FROM pg_catalog.pg_authid
> WHERE rolname = user; RETURN NEW; END;$$
> test-# LANGUAGE plpgsql STABLE STRICT SECURITY DEFINER;
> CREATE FUNCTION
> test=# REVOKE EXECUTE ON FUNCTION insert_oid() FROM public;
> REVOKE
> test=# SELECT proacl FROM pg_catalog.pg_proc WHERE proname =
> 'insert_oid';
> proacl
> -----------------------
> {postgres=X/postgres}
> (1 row)
>
> As normal user, try to execute the function or select from
> pg_catalog.pg_authid directly, both fail as expected.
>
> test=# \c test laurenz
> You are now connected to database "test" as user "laurenz".
> test=> SELECT insert_oid();
> ERROR: permission denied for function insert_oid
> test=> SELECT oid FROM pg_catalog.pg_authid WHERE rolname = user;
> ERROR: permission denied for relation pg_authid
>
> Create a temporary table, define a trigger BEFORE INSERT using the
> function that we cannot execute:
>
> test=> CREATE TEMP TABLE lautest (id integer PRIMARY KEY, useroid oid
> NOT NULL);
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "lautest_pkey" for table "lautest"
> CREATE TABLE
> test=> CREATE TRIGGER insert_oid BEFORE INSERT ON lautest FOR EACH ROW
> EXECUTE PROCEDURE insert_oid();
> CREATE TRIGGER
>
> Insert a row into the table.
> The trigger function is executed, and I have selected a value from
> pg_authid!
>
> test=> INSERT INTO lautest (id) VALUES (1);
> INSERT 0 1
> test=> SELECT * FROM lautest;
> id | useroid
> ----+---------
> 1 | 10
> (1 row)
>
> Yours,
> Laurenz Albe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
Rafal Pietrak <rafal@zorro.isa-geek.com> writes: > Looks like this thread have died away. No, it moved to the appropriate mailing list: http://archives.postgresql.org/pgsql-hackers/2006-12/msg00564.php regards, tom lane