How do I limit user access by IP? For example, assuming I have two non-superusers (user1 and user2), how can allow general access (only limited by security groups) to the instance/cluster by user1 but only allow access to a particular subnet for user2?

In standalone PostgreSQL this is handled within pg_hba.conf, which to my knowledge doesn't exist in RDS and Aurora on AWS.

pg_hba.conf equivalent:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    app_db          user1                scram-sha-256
host    app_db          user2             scram-sha-256
  • you can use security groups – Sahap Asci Jan 23 '20 at 19:36
  • How would a security group know the DB user? – Miles Elam Jan 23 '20 at 20:10
  • 1
    for db user you should use connection privileges. GRANT CONNECT ON DATABASE database_name TO role_specification. RDS is not a complete PostgreSQL. I recommend you to follow its own documentation. – Sahap Asci Jan 25 '20 at 13:35
  • @SahapAsci Access to the database is not the issue. Access through users from certain IPs is what the question is about. – Miles Elam Jan 26 '20 at 15:45
  • You can combine security group (ip) and grant (user) to solve your issue. Just don't create a user which has more than one database access. Moreover, you also can use different rds services for each of your databases. That's a workaround for pg_hba.conf and is not a complete solution but that's all you can do. – Sahap Asci Jan 27 '20 at 14:57

here are two ideas that can be tried..

Run a script or other little app in the background that scans through the killing connections that do not match the established rules it fires off every few seconds ..

Or create a system event trigger that fires off checks the user IP address and any other information that needs to be checked when the connection is made, if it fails the checks call

select  pg_terminate_backend(pid) --or
select  pg_cancel_backend(pid)

The information needed is located in

select * from pg_stat_activity

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.