I am currently adding Postgres support to a deployment tool that currently only supports MySQL. This tool dynamically provisions and configures a database, among other things.
In MySQL, we can restrict users to a particular set of IP addresses, with SQL, as part of the grant command like so:
GRANT ALL PRIVILEGES ON my_database_name.* TO 'my_user_name'@'192.168.101.%' IDENTIFIED BY 'my_secret_password' GRANT ALL PRIVILEGES ON my_database_name.* TO 'my_user_name'@'internal.example.com' IDENTIFIED BY 'my_secret_password'
In Postgres, it seems like the only way to set up a similar arrangement is to first create the user without restriction:
CREATE USER "my_user_name" WITH PASSWORD 'my_secret_password'
I've got it sorted out how to then programmatically restrict this role to a particular database, but it seems like the only way to restrict the Postgres user by host is to create entries in the pg_hba.conf file.
This is problematic, as there are many users/hosts being created programmatically by an agent that does not have file-system access.
It looks like our only three options around this are to:
- Manipulate the pg_hba.conf without using SQL to restrict a role, after it has been created using SQL
- Allow "all", instead of particular users, for an allowed host
- Restrict access to localhost only, and connect using a ssh tunnel
This seems less than ideal, and makes me wonder if I am overlooking an obvious solution. Is it possible to restrict Postgres roles by IP address using only SQL commands?