I am trying to think of the best (aka recommended or common professional practice) way to create users/roles in PostgreSQL database for development, test and production environments.
I have to say that I use Ruby on Rails if that matters to the answers.
Often I find information that suggests in general the following steps after the installation of PostgreSQL (on Ubuntu 14.04):
- create a role for development and a role for test with permissions
- use those roles (i.e. users) in database configuration files (for connections strings for example)
I wonder whether that is the secure way to go. The users would be able to create/delete databases/tables at will then, isn't that right?
What if I only want to give them read/write access to only the tables that matter for development and test respectively?
And if I go this way any ideas about administration overhead or automizations?
I work alone on the project at the moment, but other developers might join later and I care to have the databases as secure as possible.
I also need to have an idea how to deal with production later. I intend to use Heroku as service provider, so probably some configuration will be given. Anyone experience with that?