Postgresql – Limiting user access by subnet to Amazon Aurora PostgreSQL

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

This was the response from AWS - they agree using the TABLE_COMMENT column is the way forward. I can also confirm we have successfully implemented this solution.

Hello,

Thank you for contacting AWS premium support. My name is Vidyadhar and I am glad to assist you on this case.

From the case description, I understand that you noticed only null values in create_time column in information_schema.tables. You were removing old tables based on the values inside this column in MySQL. Now, you would like to know reason for not showing up creation time in Aurora and alternatives to achieve your requirement.

Just to let you know, there is a known bug in MySQL related to the same where create_time will be null for partitioned columns. This has been fixed in MySQL 5.6.25 version. Please look into below link for reference.

https://bugs.mysql.com/bug.php?id=69990

In aurora this column will be NULL for all tables including partitioned tables. This has been already notified to the service team. Also, there is no workaround to this available in Aurora for now.

It is best option to utilize the table comment session as a work around to note the time of table creation and proceed accordingly with table deletion. You can look into TABLE_COMMENT column in information_schema.tables. Please refer to below example which might be helpful.

MySQL [test]> create table S1(id int) comment='2017-09-21 15:53:51';
Query OK, 0 rows affected (0.02 sec)

MySQL [test]>  show create table S1\G
*************************** 1. row ***************************
       Table: S1 Create Table: CREATE TABLE `S1` (   `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1
COMMENT='2017-09-21 15:53:51' 1 row in set (0.00 sec)

MySQL [test]> select TABLE_SCHEMA,TABLE_NAME,TABLE_COMMENT from
information_schema.tables where table_schema='test' and
table_name='S1';
+--------------+------------+---------------------+ 
| TABLE_SCHEMA | TABLE_NAME | TABLE_COMMENT       |
+--------------+------------+---------------------+ 
| test         | S1         | 2017-09-21 15:53:51 |
+--------------+------------+---------------------+ 
1 row in set (0.01 sec)

I truly apologize for the inconvenience caused. Aurora service team is working hard to add features and fixing bugs. As of now, I am unable to provide any ETA but definitely this will be fixed in future versions.

I hope this information is helpful. Please feel free to reach me if you have any further questions or concerns.

Have a great day!