1

I want to make a small web app using Postgres as database.

Is there a way to accept database access if and only if the SQL command is coming from my app, blocking any other access to the database?

1

Is there a way to accept database access if and only if the SQL command is coming from my app, blocking any other access to the database?

No.

Postgres doesn't care which application the query came from.

It does, however, care a great deal about the machine from which the query was issued and under which account.

The first is managed by Host-Based Authentication (HBA), by adding entries into pg_hba.conf. As long as your application server is configured in there, it stands a chance of getting in. For example:

#     Database Account   Address         Auth  (Comment)
host  db1      app_user  11.22.33.44/32  md5   # server44 

Then, your application will need a database account with which to connect.

Create a Postgres "User" (a.k.a. "login Role") for your application to use and only use those credentials within your application. Use a separate Role to grant privileges on database objects (e.g. tables) and grant that Role to your application User:

create table table1 ... ; 

# Application permissions 
create app_role ; 
grant select, insert, update, delete on table1 to app_role ; 

# Developer permissions 
create dev_role ; 
grant select on table1 to dev_role ; 

# Application account 
create user app_user with password '...' valid until 'infinity' ; 
grant app_role to app_user ; 

# A Developer account 
create user fred with password '...' ; 
grant dev_role to fred ; 

Of course, fred's Development machine will also have to appear in pg_hba.conf!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.