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.
# 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.
