this is my favorite SSH key usage introduction. worth the free registration (and no spam yet). it's from the IBM developerWorks pages. (and writen by the Gentoo guy)

there are three parts covering setting up your keys and using key-agent.

eventually you pretty much have to trust the filesystem, or you require human-being to type in password and trust the human...

i use Kerberos for my database authn and authz, but still anybody who can manage to read the /etc/krb5cc_0 cache could gain access... but it does keep simple plaintext passwords out of the files.