Re: ERROR: plpgsql: permission denied
От | joseph speigle |
---|---|
Тема | Re: ERROR: plpgsql: permission denied |
Дата | |
Msg-id | 20040330035203.GA3229@www.sirfsup.com обсуждение исходный текст |
Ответ на | ERROR: plpgsql: permission denied (joseph speigle <joe.speigle@jklh.us>) |
Список | pgsql-novice |
hi, I figured out the cause of my problem, I had to add "TRUSTED PROCEDURAL" to the CREATE LANGUAGE statement. I thought that was for C or PerlPL or soemthing though. instead of CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL' I had to do CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL' ; BTW, I also got the function to work create or replace function mod_log.top_hits(interval) returns setof mod_log.top_hits_type as ' declare when_for alias for $1; top_hits_row mod_log.top_hits_type%ROWTYPE; generic_record record; begin for generic_record in SELECT uri as uri, sum(*) AS total_hits FROM mod_log.apachelog where uri <> ''127.0.0.1'' and urinot like ''%.gif'' and uri not like ''favicon.ico'' and request_time > (now() - when_for) GROUP BY uri order by total_hitsdesc loop top_hits_row.uri = generic_record.uri; top_hits_row.hits = generic_record.total_hits; return next top_hits_row; end loop; return next top_hits_row; return; end; ' LANGUAGE 'plpgsql'; -- create type mod_log.top_hits_type as (uri character varying(1536), hits bigint); -- http://www.varlena.com/varlena/GeneralBits/Tidbits/setofrowtype.sql -- select * from mod_log.top_hits('5 min'); On Mon, Mar 29, 2004 at 07:12:20PM -0600, joseph speigle wrote: > Hi, > > I have installed mod_pgsqllog for apache, and have created the necessary table (apachelog) in schema (mod_log) for theuser (mod_log). I am having a problem, for example, giving the user (mod_log) permissions to create a function. > > I created language plpgsql as superuser connected to mod_log database with > mod_log=# CREATE FUNCTION plpgsql_call_handler () > mod_log-# RETURNS OPAQUE > mod_log-# as '/usr/local/pgsql/lib/plpgsql.so' > mod_log-# LANGUAGE 'C'; > ERROR: function plpgsql_call_handler already exists with same argument types > mod_log=# CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler > mod_log-# LANCOMPILER 'PL/pgSQL'; > CREATE LANGUAGE > mod_log=# \c mod_log mod_log > You are now connected to database mod_log as user mod_log. > mod_log=> \e apachelog.sql > ERROR: plpgsql: permission denied > mod_log=> > > where apachelog.sql is just the following: > > create or replace function mod_log.top_hits(interval) > returns void > as > ' > declare > interval how_long as alias for $1; > begin > SELECT > uri, > sum(*) AS total_hits > FROM mod_log.apachelog > where client_ip <> > ''127.0.0.1'' and uri not like ''%.gif'' > and uri not like ''%.css'' and uri <> ''/favicon.ico'' > and > GROUP BY uri order by total_hits desc, uri asc; > end' language 'plpgsql'; > > > >From the archives, > http://archives.postgresql.org/pgsql-bugs/2002-08/msg00036.php > I think I need to issue some grants, but which ones? > > -- > joe speigle > www.sirfsup.com > -- joe speigle www.sirfsup.com
В списке pgsql-novice по дате отправления: