Forum OpenACS Q&A: Response to postgres and user passwords not seem to work.

Posted by Janine Ohmer on
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.