pg_ident.hba on a single-user, multi-app machine

[Matt Silverlock <matt@xxxxxxxxxxxxxxxxxx>]

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

1. # file: pg_hba.conf
2. # TYPE  DATABASE        USER            ADDRESS                 METHOD
3. local   all             deploy                                  peer map=appusers
4. local   all             postgres                                peer
5. host    all             all              127.0.0.1/32           md5
6. host    all             all             ::1/128                 md5
7.  
8. # file: pg_ident.conf
9. # MAPNAME       SYSTEM-USERNAME         PG-USERNAME
10. appusers        deploy                  baltar # represents one application
11. appusers        deploy                  caprica # second app
12. # etc...
13.  
14. # via Ansible
15. - name: create app1 database user
16.   postgresql_user: db=app1 name=baltar priv=ALL
17.  
18. - name: create app2 database user
19.   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.