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 createdb, login and a password
  • 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?

  • Is it possible to use different databases entirely? – Aleksei Zabrodskii Dec 27 '15 at 17:43
  • Yes, possible. I chose PostgreSql because it was db of choice at Heroku. But any open source software should do. – Ely Dec 27 '15 at 20:55
  • No, I mean having separate Postgres instances for testing and production. – Aleksei Zabrodskii Dec 28 '15 at 2:43
  • Actually, yes. One for development and local test. Another test for CI (Travis CI). And a production database. – Ely Dec 28 '15 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.