Forum OpenACS Development: Response to problem with updating acs-kernel
The need for this privilege will go away when running PG 7.2, at some point.
What you need to do is to pg_dump your database, drop the database and user, recreate the user with the superuser privilege, load acs-kernel/sql/postgresql.sql, then reload your data.
I suggest trying this on a test system before dropping your database and data, though.
You'll get some duplicate function definition errors when you reload but these are safely ignored.