We have a web app that detects if the DB does not exist and creates it. Users like it, but my thought is that such user has too much privileges.
I would optimally propose to change the DB creation screen of the app to supply 2 sets of credentials - one that will be used for creating DB initially and not stored permanently (e.g. 'postgres' role) and the other that will be used by the web app and stored in config file (e.g. 'webapp'). Furthermore, 'webapp' will need to be able to 1) add new and 2) modify existing tables, procedures, and functions (the web app automatically updates the schema as required)
So some of the questions I have are:
- What are some of the best practices in above scenario when using PostgreSQL 9.x?
- Should the DB created be owned by 'webapp' role or should it be owned by 'postgres' and I just grant 'webapp' the necessary privileges. In case of latter, would newly added objects be owned by 'webapp' and should I avoid that?
- What are some of the approaches users in community are currently using?