Обсуждение: opportunities of inline funtions

Hi.

  There was an idea to make access to the data only through the built - in
functions (on C). There is a table 't_test' which the superuser can read and
write down. There are functions of access, for example, for an insert of the
data:

PG_FUNCTION_INFO_V1(f_test);

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

  It is supposed further, that function will check the data and to carry out
something else, unimportantly. And so. It would be desirable, that this
function was carried out by the usual user, and it could work "as" superuser.
That is has executed an insert of the data. Read caution about "trojan horses"
in functions which allow the usual user to get access to the data which are
inaccessible to him. Here is how this idea I wanted to apply time in positive
sense. So I grant execute of this function to 'testo' user

=> GRANT EXECUTE ON FUNCTION f_test(INT, CHAR) TO testo;


However I receive a mistake

=> SELECT f_test('1','2');
ERROR:  t_test: permission denied

How to realize this idea?

--
engineer

Hi.

 Really sorry, previous post was completely inconsistent. Actually, in addition, RULES exists. I use postgresql-7.3.5.
Herethe 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