pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedopportunities of inline funtions
2+ messages / 1 participants
[nested] [flat]
* opportunities of inline funtions
@ 2004-10-28 04:18 Anton Maksimenkov <engineer@hlebprom.ru>
0 siblings, 0 replies; 2+ messages in thread
From: Anton Maksimenkov @ 2004-10-28 04:18 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: opportunities of inline funtions
@ 2004-10-29 08:40 Anton Maksimenkov <engineer@hlebprom.ru>
0 siblings, 0 replies; 2+ messages in thread
From: Anton Maksimenkov @ 2004-10-29 08:40 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-10-29 08:40 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-28 04:18 opportunities of inline funtions Anton Maksimenkov <engineer@hlebprom.ru>
2004-10-29 08:40 Re: opportunities of inline funtions Anton Maksimenkov <engineer@hlebprom.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox