Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Matt Silverlock
Date:
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.


  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.

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

From
Adrian Klaver
Date:
On 08/16/2014 07:47 AM, Matt Silverlock wrote:
> 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.

>
> 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?)

Yes. Here is a good description of how:

http://stackoverflow.com/questions/9644251/how-do-unix-domain-sockets-differentiate-between-multiple-clients

>
> Thanks in advance.


--
Adrian Klaver
adrian.klaver@aklaver.com