6

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?
Improve this question

1 Answer 1

Reset to default
2

The best way, really, is for the web app to have no right to create or modify tables at all. It should only have the rights to do what it needs to do to perform its day to day functions, not administration.

In particular, the web app must not run as a superuser and should preferably not own the database or tables.

If the webapp has built-in administration functions for changing tables when it's upgraded, etc, it should do so via a separate set of credentials and allow you to be prompted to enter those credentials each time you perform an upgrade. Or let you create the file with the admin credentials, run the upgrade, and delete it again.

If you can't fix the webapp to use different credentials for schema changes, then it might as well be the owner of the database and tables. Just don't make it a superuser!

If you can fix the app so it uses different credentials for schema changes, then its regular non-schema-change user should not own the tables or database. It should have only CONNECT and TEMPORARY rights on the database, USAGE rights on the schema(s), and the required rights on each table GRANTed to it. Just don't forget to REVOKE ALL FROM public ON DATABASE mydb; and REVOKE ALL FROM public ON SCHEMA public;.

Improve this answer
2
  • I am in agreement with everything you said, but I may not be able change the web app to use 2 different sets of credentials, at this time. I may have to go with webapp user being an owner...Also can you elaborate on 'not forgetting' to revoke all from public ... ???
    – zam6ak
    Jul 16, 2015 at 13:08
  • @zam6ak It's not relevant if you're stuck with a single set of creds for the webapp. The point is that by default all users (via the virtual role public) have CREATE rights on the schema public, and you usually want to REVOKE that if you're setting up a limited-access user, then GRANT it only to the user who should be able to make schema changes.
    – Craig Ringer
    Jul 16, 2015 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.