Hi all. Trying to rationalise my pg_hba.conf and pg_ident.conf configuration on a Debian/Ubuntu machine where:
* One primary application user (“deploy”) runs web applications * postgres, nginx, et. al run under their own users * Using a Unix socket for connecting to PostgreSQL on the same machine (if I split the machines up at some point in the future, I’ll just run TCP + SSL w/ strict IP filtering) At the moment I’m using the following approach, where each database user (unique per application) only has permissions for its own database. Users are mapped to the “deploy” user so that peer authentication can work. http://pastebin.com/ZAWvnKNW # file: pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD local all deploy peer map=appusers local all postgres peer host all all 127.0.0.1/32 md5 host all all ::1/128 md5 # file: pg_ident.conf # MAPNAME SYSTEM-USERNAME PG-USERNAME appusers deploy baltar # represents one application appusers deploy caprica # second app # etc... # via Ansible - name: create app1 database user postgresql_user: db=app1 name=baltar priv=ALL - name: create app2 database user postgresql_user: db=app2 name=caprica priv=ALL What are the outstanding risks here? The only ‘likely’ scenario (short of the box itself being compromised) is if the app is compromised/flawed (i.e. some uncaught SQLi vuln in a lib) then it can drop its own tables, but not the tables of any other application running under the same OS user. (Heck, can you even have multiple applications talking to the same Unix socket?) Thanks in advance.
