Question Is using the same AWS RDS Database for multiple Plesk instances possible?

[carini]

Is possible to use a single RDS instance for more than one Plesk server, providing a manually-defined account with administrative privilege? I tried and the first Plesk goes smoothly, but the subsequent tries to connect the same databases ended with a

Error: The test connection to the database server has failed because the supplied account does not possess administrative privileges:
SQLSTATE[42000]: Syntax error or access violation: 1044 Access denied for user 'admin'@'%' to database 'xxxxxxxxxxxx'

Needless to say the account "admin" HAVE administrative privileges:

[centos@pleskxx ~]$ mysql --host='mariadb-10-4.xxxxxxxxx.eu-central-1.rds.amazonaws.com' --user='admin' --password='xxxxxxxxxxxxxxxxx'

MariaDB [(none)]> SHOW PRIVILEGES;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege               | Context                               | Comment                                               |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                   | Tables                                | To alter the table                                    |
| Alter routine           | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                  | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine          | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create temporary tables | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view             | Tables                                | To create new views                                   |
| Create user             | Server Admin                          | To create new users                                   |
| Delete                  | Tables                                | To delete existing rows                               |
| Delete history          | Tables                                | To delete versioning table historical rows            |
| Drop                    | Databases,Tables                      | To drop databases, tables, and views                  |
| Event                   | Server Admin                          | To create, alter, drop and execute events             |
| Execute                 | Functions,Procedures                  | To execute stored routines                            |
| File                    | File access on server                 | To read and write files on the server                 |
| Grant option            | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                   | Tables                                | To create or drop indexes                             |
| Insert                  | Tables                                | To insert data into tables                            |
| Lock tables             | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                 | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                   | Server Admin                          | To make proxy user possible                           |
| References              | Databases,Tables                      | To have references on tables                          |
| Reload                  | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client      | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave       | Server Admin                          | To read binary log events from the master             |
| Select                  | Tables                                | To retrieve rows from table                           |
| Show databases          | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view               | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                | Server Admin                          | To shut down the server                               |
| Super                   | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                 | Tables                                | To use triggers                                       |
| Create tablespace       | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                  | Tables                                | To update existing rows                               |
| Usage                   | Server Admin                          | No privileges - allow connect only                    |
+-------------------------+---------------------------------------+-------------------------------------------------------+
32 rows in set (0.01 sec)

What's wrong?

 

[Martin Dias]

It should have permissions like this:

select * from user where user='admin' \G
*************************** 1. row ***************************
Host: localhost
User: admin
Password: ???????????????????
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0

 

[carini]

Some of these privileges are unobtainable under AWS RDS, but the first plesk that link with this database can see the tables with no problem, problem comes with subseguent Plesk servers

[root@plesk ~]# mysql --host='mariadb-10-4.xxxxxxxxxxxxxx.eu-central-1.rds.amazonaws.com' --user='admin' --password='xxxxxxxxxxxxxx'

MariaDB [(none)]> SELECT * FROM mysql.user WHERE user='admin'\G
*************************** 1. row ***************************
                  Host: %
                  User: admin
              Password: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: N
          Process_priv: Y
             File_priv: N
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: N
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: N
   Delete_history_priv: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
      password_expired: N
               is_role: N
          default_role:
    max_statement_time: 0.000000
1 row in set (0.00 sec)