Another datapoint - in the default security mode, it will let you connect to a database you didn't create, but it won't let you actually do anything (ie, an attempt to "select email from users" resulted in "ERROR: users: Permission denied.".)
I've been exploring this tonight because we need to have proper user-level security for our hosting service (which will be ready Real Soon Now tm), but turning on any type of password authentication really messes with pg_dump. For one thing, pg_dumpall doesn't work at all. And even if you're willing to pg_dump individual databases, you still have problems because the only way to get the username and password to pg_dump is to do something gross like this:
pg_dump database < user-pass-file > output-file
because it prompts for them on the command line. Another problem is that the prompts themselves end up in the output file. Altogether yucky. So when I read this in the Postgres docs
"Each user in Postgres is assigned a username and (optionally) a password. By default, users do not have write access to databases they did not create."
I decided to try it, and lo and behold it appears that they don't have read access either. *phew*!!!
Of course, please speak up if I'm wrong about this! And if there's a way to use password authentication and pg_dump peacefuly, I'd love to hear it.
