X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9EEC73A3E7A for ; Fri, 29 Oct 2004 09:40:44 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 19710-09 for ; Fri, 29 Oct 2004 08:40:38 +0000 (GMT) Received: from gw7.hlebprom.ru (unknown [62.148.250.13]) by svr1.postgresql.org (Postfix) with ESMTP id 1B5A23A3E61 for ; Fri, 29 Oct 2004 09:40:37 +0100 (BST) Received: from openbox.hlebprom.ru (openbox.hlebprom.ru [172.16.7.7]) by gw7.hlebprom.ru (Postfix) with ESMTP id 76ABD4B813 for ; Fri, 29 Oct 2004 14:40:53 +0600 (YEKST) Date: Fri, 29 Oct 2004 14:40:29 +0600 From: Anton Maksimenkov To: pgsql-admin@postgresql.org Subject: Re: opportunities of inline funtions Message-Id: <20041029144029.60a415a5.engineer@hlebprom.ru> Organization: =?KOI8-R?Q?=EF=E1=EF_"=E8=CC=C5=C2=D0=D2=CF=CD"?= X-Mailer: Sylpheed version 0.9.0claws (GTK+ 1.2.10; i386-unknown-openbsd3.5) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200410/368 X-Sequence-Number: 15361 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