I think I managed to solve the problem.
1- I deleted the /Library/StartupItems/PostgreSQL folder which was causing my problems (I don't think the startup item was working properly anyway)
2- With the help of this blogpost:
http://www.ketema.net/serendipity/archives/89-Autostarting-PostgreSQL-on-Leopard.html
I rolled my own autostart plist file
Now PostgreSQL autostarts fine and I don't get the security warning. FYI my org.postgresql.plist file looks like this:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>EnvironmentVariables</key>
<dict>
<key>PGDATA</key>
<string>/Library/PostgreSQL8/data</string>
</dict>
<key>GroupName</key>
<string>wheel</string>
<key>Label</key>
<string>postgresql</string>
<key>ProgramArguments</key>
<array>
<string>/Library/PostgreSQL8/bin/postgres</string>
<string>-i</string>
</array>
<key>RunAtload</key>
<true/>
<key>ServiceDescription</key>
<string>PostgreSQL Server</string>
<key>StandardErrorPath</key>
<string>/Library/PostgreSQL8/data/pg_log/logfile</string>
<key>StandardOutPath</key>
<string>/Library/PostgreSQL8/data/pg_log/logfile</string>
<key>UserName</key>
<string>postgres</string>
</dict>
</plist>