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

Re: opportunities of inline funtions

From: Anton Maksimenkov <engineer(at)hlebprom(dot)ru>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: opportunities of inline funtions
Date: 2004-10-29 08:40:29
Message-ID: 20041029144029.60a415a5.engineer@hlebprom.ru (view raw or flat)
Thread:
Lists: pgsql-admin
Hi.

 Really sorry, previous post was completely inconsistent. Actually, in addition, RULES exists. I use postgresql-7.3.5. Here the dump

---------------------------------------------------------------------
CREATE TABLE t_test (
    id integer,
    message text
);

CREATE VIEW vw_test AS
    SELECT t_test.id, t_test.message FROM t_test;

REVOKE ALL ON TABLE vw_test FROM PUBLIC;
GRANT INSERT,SELECT,UPDATE ON TABLE vw_test TO testo;

CREATE FUNCTION f_test (integer, text) RETURNS integer
    AS '/var/postgresql/f_test', 'f_test'
    LANGUAGE c;

REVOKE ALL ON FUNCTION f_test (integer, text) FROM PUBLIC;
GRANT ALL ON FUNCTION f_test (integer, text) TO testo;

CREATE RULE in_vw_test AS ON INSERT TO vw_test DO INSTEAD SELECT f_test(1, '2'::text) AS f_test;
---------------------------------------------------------------------

The source of f_test function is simple:

Datum
f_test(PG_FUNCTION_ARGS)
{
        int ret, proc;
        SPI_connect();
        ret = SPI_exec("INSERT INTO t_test VALUES ('7', 'hi')", 0);
        proc = SPI_processed;
        SPI_finish();
        return (proc);
}
---------------------------------------------------------------------

After REcheck documentation I found that
http://www.postgresql.org/docs/7.3/static/rules-permissions.html
As far as I can understand that after

CREATE RULE in_vw_test AS ON INSERT TO vw_test DO INSTEAD SELECT f_test(1, '2'::text) AS f_test;

f_test will run with permissions of creator of the RULE (superuser) , and can perform the work ("INSERT INTO t_test VALUES ('7', 'hi')").
It works when started by me (superuser):

engineer=# INSERT INTO vw_test VALUES ('1','2');
 f_test 
--------
      1
(1 row)


But all the same for 'testo' user:

engineer=> INSERT INTO vw_test VALUES ('1','2');
ERROR:  t_test: permission denied


 What's wrong? Why rules permissions (changes to owner) do not work?

-- 
engineer

pgsql-admin by date

Next:From: Ivan DimitrovDate: 2004-10-29 10:04:35
Subject: pg_autovacuum is not working
Previous:From: Johnson, HeatherDate: 2004-10-28 17:24:25
Subject: Re: pg_ctl stop -m fast

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