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

ERROR: plpgsql: permission denied

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

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2004-03-30 03:21:36
Subject: Re: question about libpq
Previous:From: Joe ConwayDate: 2004-03-29 23:06:59
Subject: Re: question about libpq

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