Re: ERROR: plpgsql: permission denied

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 uri not like ''%.gif'' and uri not like ''favicon.ico'' and request_time > (now() - when_for) GROUP BY uri order by total_hits desc
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 the user (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