Grant Priviliges to cloud sql user with partial_revokes=ON

[SandeepDeb]

Dear Community,

With reference to the following documentation
https://cloud.google.com/sql/docs/mysql/flags#partial-revokes

I was trying to run the below command from SQL studio 

grant select on *.* to 'app'@'%';

 Since root user login is disabled in SQL studio i executed the above query using other built-n database authentication. 
I get the below error
Some of your SQL statements failed to execute (Learn more at https://cloud.google.com/sql/docs/mysql/manage-data-using-studio). Details: Error 1410: You are not allowed to create a user with GRANT

Then i tried connecting using root user from my local with cloud auth proxy.
I still get the same error
SQL Error [1410] [42000]: You are not allowed to create a user with GRANT

NOTE: This user already exists 

[ms4446]

The error "Error 1410: You are not allowed to create a user with GRANT" in Cloud SQL for MySQL, even when the user exists, is not about creating the user but about the restriction on the GRANT statement, particularly when it involves global privileges (*.*). This limitation stems from Cloud SQL’s managed security model and MySQL’s configuration, especially the partial_revokes setting, which is typically enabled by default.

In MySQL 8.0, the partial_revokes flag explicitly prevents granting privileges at the server level (*.*) or on the mysql schema to ensure tighter security and avoid accidental privilege escalation. Cloud SQL enforces this flag, which disallows commands like GRANT SELECT ON *.* TO 'user'@'%'. Even the root user in Cloud SQL is not a true superuser and is subject to these restrictions. Additionally, connecting via the Cloud SQL Auth Proxy does not bypass these limitations, as the proxy only provides a secure connection but does not alter privilege enforcement.

To resolve this, the recommended approach is to use specific grants. Instead of attempting global grants, you should grant privileges on individual databases or tables. For example:

GRANT SELECT ON `your_database_name`.* TO 'user'@'%';

This aligns with Cloud SQL’s granular privilege model and is a best practice for securing resources. Before granting privileges, ensure the user exists by running:

CREATE USER IF NOT EXISTS 'user'@'%' IDENTIFIED BY 'password';

In cases where server-wide privileges are absolutely necessary, you can check if disabling partial_revokes is allowed. If your Cloud SQL instance permits it, this can be done by adding the partial_revokes flag in the instance settings and setting it to OFF. However, this option is often restricted in managed environments.

For administrative tasks that require broader control, you can use a service account with the cloudsql.superuser IAM role. This role enables you to manage users, databases, and configurations through Google Cloud tools and APIs. However, it is important to note that cloudsql.superuser does not override MySQL’s internal restrictions like partial_revokes.

Global grants are disallowed in Cloud SQL due to partial_revokes=ON and managed user restrictions. The solution is to use specific grants on databases or tables, adhering to the principle of least privilege. Only disable partial_revokes if explicitly allowed, and consider using the cloudsql.superuser role for administrative tasks when necessary. By following these best practices, you can manage privileges securely and effectively within Cloud SQL.

[SandeepDeb]

Hello @ms4446 ,

Thanks for taking the effort to provide a detailed response.

I have managed  apply the privileges, all the queries are working fine. But one specific query always throws this error

[ERROR] SQLSTATE[HY000] [2002] Connection refused (Connection: sch, SQL: select table_name as `name`, (data_length + index_length) as `size`, table_comment as `comment`, engine as `engine`, table_collation as `collation` from information_schema.tables where table_schema = ‘some_table’ 

This query works fine from client and mysql studio, just to rule out any permission issues and also evident from the SQL error message.

I have increased the wait_timeout and max_connection but no success. Moreover this query is generated by php/laravel framework and not a custom query. 

Same issue has been reported earlier as well
https://www.googlecloudcommunity.com/gc/Databases/ERROR-SQLSTATE-HY000-2002-Connection-refused/m-p/6...

[ms4446 · Accepted solution]

Hi @SandeepDeb ,

The "Connection refused (2002)" error in Cloud SQL for MySQL typically indicates network instability, connection exhaustion, or resource limitations, rather than a SQL syntax issue. Since it occurs intermittently, the root cause is often connection leaks in Laravel, inefficient database usage, or Cloud SQL constraints. Addressing these factors systematically can resolve the issue.

Laravel does not maintain a traditional persistent connection pool; instead, it establishes a new database connection per request and reuses it within that lifecycle. The most common cause of connection issues is connection leaks, where connections remain open due to improper handling. Laravel’s Query Builder and Eloquent ORM automatically manage connections, but if using DB::connection(), ensure explicit disconnection:

DB::connection()->disconnect();

For high-traffic applications experiencing frequent reconnections, enabling persistent connections may help:

'options' => [PDO::ATTR_PERSISTENT => true],

Instead of blindly increasing max_connections, monitoring connection usage and query performance is key. Using Laravel’s query listener can help identify long-running queries that may be holding connections open longer than necessary:

DB::listen(function ($query) { Log::info($query->sql); });

Cloud SQL enforces limits on connections, CPU, and memory, which can lead to connection refusals if thresholds are exceeded. Monitoring active connections using:

SHOW STATUS WHERE variable_name = 'Threads_connected';

and checking aborted connections with:

SHOW GLOBAL STATUS LIKE 'Aborted_connects';

can provide real-time insights. Additionally, enabling the slow query log helps identify inefficient queries:

SET GLOBAL slow_query_log = 'ON';  
SET GLOBAL long_query_time = 2;

Additionally, TCP keep-alives should be configured at the OS level to prevent premature connection drops:

sysctl -w net.ipv4.tcp_keepalive_time=120

For deeper inspection, checking active network connections with:

netstat -an | grep 3306

or detecting query locks via:

SHOW ENGINE INNODB STATUS;

can reveal performance bottlenecks. If connection churn remains an issue, ProxySQL can be implemented to efficiently manage and pool database connections, reducing the load on Cloud SQL.