Restrict Postgres roles by IP address outside of the pg_hba.conf file

Question

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:

1. Manipulate the pg_hba.conf without using SQL to restrict a role, after it has been created using SQL
2. Allow "all", instead of particular users, for an allowed host
3. 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?

Answer 1

There is no way to do this with native SQL in PostgreSQL. Also, Postgres manages the privileges and access in a very different way than MySQL.

I've built a VERY BASIC function (needs a lot of tweak and work around, specially handlers when the user already exists) that it adds straightly a user into the pg_hba file, create the user and grant the access to the database.

CREATE OR REPLACE FUNCTION addRemoteUser(
  username text,
  iptarget text DEFAULT '0.0.0.0/0',
  dbtarget text DEFAULT 'all',
  passtext text DEFAULT 'CHANGEME',
  methodauth text DEFAULT 'md5')
  RETURNS boolean
AS $$
  import os
  hbaLine = "host " + str(dbtarget) + "\t" + str(username) + "\t" + str(iptarget) + "\t" + str(methodauth)
  createuser = "CREATE USER " + str(username) + " WITH PASSWORD '" + str(passtext) + "'"
  grantuser = "GRANT connect ON DATABASE " + str(dbtarget) + " TO " + str(username)
  hbaFilePath = plpy.execute("select setting from pg_settings where name = 'hba_file'", 1)
  hs = open(hbaFilePath[0]['setting'],"a")
  hs.write(hbaLine + "\n")
  addUserCommand = plpy.execute(createuser, 1)
  grantUserCommand = plpy.execute(grantuser, 1)
  reload = plpy.execute("select pg_reload_conf()", 1)
  hs.close()
  return True
$$ LANGUAGE plpythonu;

(also posted here on GitHub.)

Obviously is not for production yet, but at least you can have the rough idea how to build the function to do so. It needs PL/Python on the database for this particular example.

I know is not the ideal for this particular case, however I think it's a valid example on how to extend your Postgres installation.

Then, once the function is compiled, you can use SQL to call the function:

SELECT addRemoteUser('mynewuser');