Skip site navigation (1) Skip section navigation (2)

Re: ERROR: plpgsql: permission denied

From: joseph speigle <joe(dot)speigle(at)jklh(dot)us>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: ERROR: plpgsql: permission denied
Date: 2004-03-30 03:52:03
Message-ID: 20040330035203.GA3229@www.sirfsup.com (view raw or flat)
Thread:
Lists: 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 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

In response to

pgsql-novice by date

Next:From: joseph speigleDate: 2004-03-30 03:57:19
Subject: Re: dynamic interval in plpgsql
Previous:From: Tom LaneDate: 2004-03-30 03:21:36
Subject: Re: question about libpq

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group