6

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?

  • you can restrict postgres access by editing this file pg_hba.conf – Ahmad Abuhasna Nov 24 '15 at 10:09
  • how many user will connect to server concurrent? – a_vlad Nov 24 '15 at 12:41
  • @a_vlad Probably only one, usually, but maybe several sometimes. – Brad Werth Nov 24 '15 at 13:54
  • @AhmadAbuhasna yes, thanks. I am seeking an alternate way, using pure SQL. – Brad Werth Nov 24 '15 at 13:54
  • if only - I prefer restrict access by localhost only, and connect using ssh tunnel. may be it not exactly what You ask, but it not give additional loading to the server and work fine and secure. – a_vlad Nov 24 '15 at 14:00
3
+50

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');

  • Interesting approach... I half worried that this might be the case... – Brad Werth Nov 24 '15 at 23:31
  • @BradWerth Another approach, which involves the user response, is to build a plpgsql (that language does not need any additional packages) which creates the user and prompts the line to be added in the pg_hba.conf (some of the queries in the original script can be reused). – 3manuek Nov 24 '15 at 23:36

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.