Postgres – Restrict Superusers to Local Connections Only

authenticationpostgresql

I have a database with several users: foo, bar, and super. I was looking at pg_hba.conf to lock down access.

I'd like to allow foo and bar to be able to connect from any IP with password authentication. Or locally with trust authentication.

I'd like to allow super to only be able to login locally with trust authentication. Never from a non-local address.

I started with this.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Allow everything if you're on the same machine.
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust

But, then I got stuck because I'm not sure how to say "all users, except super". I'm looking for something like this.

host    all             not_super       all                     md5

One approach I thought I could take is to list foo and bar explicitly. But, I'm wondering if there is another way.

host    all             foo             all                     md5
host    all             bar             all                     md5

Best Answer

Processing of pg_hba.conf stops at the first matching rule, so put your super user first - local only, trust - then rules for everybody else afterwards. 

host  all  super  127.0.0.1/32  trust   # super user, local only - trust
host  all  super  0.0.0.0/0     reject  # super user, non-local - reject
host  all  all    0.0.0.0/0     md5     # All other users, anywhere - password

These address specifications are far too wide, in my opinion.
You should arrange define network subnets that your users must be within in order to connect to these databases.

Related Question