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