Search code examples
pythonmysqlamazon-web-servicesrdspymysql

Using PyMySQL, I can't connect to RDS

Using PyMySQL, I get pymysql.err.OperationalError: (1045, u"Access denied for user 'my_user'@'<MY_IP_ADDRESS>' (using password: YES)"), however, I can login from the command line or MySQL Workbench using the same credentials on this machine. And, I can connect to localhost using PyMySQL. Here's my sample test code:

import pymysql
prod_conn = pymysql.connect(
  host='correct-host-name.us-west-2.rds.amazonaws.com',
  user='my_user',
  password='correct_password',
  port=3306,
  database='my_db')

However, using mysql -hcorrect-host-name.us-west-2.rds.amazonaws.com -umy_user -pcorrect_password -P3306 -Dmy_db from the command line on the same machine, I get in just fine.

After Googling, I tried checking the grants for the user and I believe that the user is setup correctly. SHOW GRANTS FOR CURRENT_USER() returns

Grants for my_user@%
'GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO \'my_user\'@\'%\' WITH GRANT OPTION'

I tried checking SELECT USER(), CURRENT_USER(); and get back

USER()                      CURRENT_USER()
'my_user@<MY_IP_ADDRESS>'   'my_user@%'

So, it seems that the user is setup properly, which I suspected since I can connect through other options. I also tried both suggestions from https://forums.aws.amazon.com/thread.jspa?messageID=707103 (specifically, using a different parameter group with log_bin_trust_function_creators == 1 and using a password without punctuation) to no avail.

I want to use this in a lambda function, so I really want to use PyMySQL to keep this a python only solution rather than trying MySQLDB or another library.

I don't think there are any anonymous users after reviewing SELECT user,host FROM mysql.user;

user        host
'my_user'   '%'
'mysql.sys' 'localhost'
'rdsadmin'  'localhost'

Appreciate any other thoughts this community might have on how to proceed...

Solution

  • I solved this by creating a new user. I only needed read privileges for this user, so it's a better approach than using the default user with such high permissions. I'm still not sure why the original one didn't work, but here's what ended up working:

    # Grants for my_new_user@%
'GRANT SELECT ON *.* TO \'my_new_user\'@\'%\''