Introduction

Admin credentials serve as the gateway to ProxySQL’s configuration, granting users unparalleled control over its functionalities. However, the improper handling of these credentials can expose organizations to significant security risks, including unauthorized access, data breaches, and service disruptions. Recognizing this challenge, the ProxySQL development team introduced a robust solution: the admin-admin_credentials variable.

In this blog post, we delve into the intricacies of managing ProxySQL admin credentials, with a particular focus on the admin-admin_credentials variable.

What is admin-admin_credentials

ProxySQL’s admin-admin_credentials feature is a crucial component for securing access to ProxySQL’s administrative functionalities. At its core, this feature involves setting up a set of dedicated username and password pairs specifically for administrative tasks within ProxySQL.

admin-admin_credentials ensures that only users with the designated administrative credentials can access and modify ProxySQL’s configuration, runtime settings, and other administrative functions. This segregation of administrative credentials enhances security by minimizing the risk of unauthorized access or inadvertent modifications to ProxySQL’s critical settings.

Furthermore, admin-admin_credentials operates independently of any existing authentication mechanisms within the underlying database servers or applications. This means that even if the authentication mechanisms of the database servers are compromised, the integrity of ProxySQL’s administrative access remains intact, bolstering overall system security.

Setting up admin-admin_credentials

Configuring admin-admin_credentials in ProxySQL involves several straightforward steps to ensure a secure and robust administrative access mechanism. Below is a step-by-step guide to setting up admin-admin_credentials effectively:

  1. Access ProxySQL Configuration: Begin by accessing the ProxySQL configuration. This can typically be done through the ProxySQL administration interface.
  2. Define Admin Credentials: Within the ProxySQL configuration, specify the desired username and password for the administrative account. These credentials will be used exclusively for administrative tasks in ProxySQL. For example: 
    UPDATE global_variables SET variable_value='admin:password;radmin:radmin' WHERE variable_name='admin-admin_credentials';

    Replace ‘admin’ with your desired username and ‘password’ with a strong, unique password.
    Remember that multiple users can be configured, and they must be semi-colon separated.
    Also remember that admin user can only connect locally through 127.0.0.1 or localhost.

  3. Apply Configuration Changes: After defining the admin credentials, apply the changes to the ProxySQL configuration. This is typically done by executing the following: 
    LOAD ADMIN VARIABLES TO RUNTIME;

    Optionally, save the setting to disk, to have them persistent:

    SAVE ADMIN VARIABLES TO DISK;
  4. Test Administrative Access: Test the administrative access using the configured credentials. Access the ProxySQL administration interface or connect to ProxySQL using the command-line interface (CLI) with the provided credentials. Verify that you can perform administrative tasks such as modifying configurations or monitoring ProxySQL’s status.
  5. Ensure Secure Storage: Lastly, ensure that the admin credentials are securely stored. Avoid storing passwords in plaintext or insecure locations. Consider using a secure password manager or encryption methods to safeguard the credentials.

Security considerations

By segregating administrative credentials and using dedicated username and password pairs for administrative tasks, admin-admin_credentials significantly enhances the security posture of ProxySQL deployments.
admin-admin_credentials operates independently of authentication mechanisms in underlying database servers or applications. This isolation ensures that even if external authentication systems are compromised, the integrity of ProxySQL’s administrative access remains intact, safeguarding against potential security breaches.

Using Hashed Passwords in admin-admin_credentials

In addition to supporting clear text passwords, ProxySQL’s admin-admin_credentials feature offers the flexibility to use hashed passwords, leveraging the hashing algorithms employed in MySQL’s authentication mechanisms. This capability enhances security by providing an additional layer of protection for admin credentials stored within ProxySQL’s configuration.

When using hashed passwords in admin-admin_credentials, administrators have the option to choose between two widely used hashing algorithms:

  1. mysql_native_password: This hashing algorithm is commonly used for authentication in MySQL environments. It employs a simple hashing mechanism that generates a hash value based on the user’s password. While mysql_native_password provides basic security for storing passwords, it is susceptible to brute-force attacks and offers relatively lower security compared to more modern hashing algorithms.
  2. caching_sha2_password: Introduced in MySQL 8.0, caching_sha2_password is a more secure hashing algorithm designed to withstand various cryptographic attacks. It utilizes the SHA-256 hashing function to generate a hashed password, making it significantly more resilient against brute-force and dictionary attacks. Additionally, caching_sha2_password supports stronger password encryption techniques, enhancing overall security.

To use hashed passwords in admin-admin_credentials, administrators can specify the hashed password directly in the ProxySQL configuration, ensuring that sensitive credentials are not exposed in clear text. This approach reduces the risk of credential exposure and unauthorized access, particularly in scenarios where ProxySQL configurations may be accessible to unauthorized users.

By leveraging hashed passwords in admin-admin_credentials, organizations can enhance the security posture of their ProxySQL deployments, mitigating the risk of password-related vulnerabilities and ensuring the confidentiality of administrative credentials.

If you prefer using the mysql_native_password hashing algorithm, you can configure ProxySQL’s admin-admin_credentials accordingly. Let’s walk through an example of how to set it up:

UPDATE global_variables
SET variable_value='admin:admin;radmin:'||MYSQL_NATIVE_PASSWORD('secretpassword')
WHERE variable_name='admin-admin_credentials';

In this example, the hashed password is ‘secretpassword’ hashed using the MYSQL_NATIVE_PASSWORD() function introduced in ProxySQL version 2.6.2 .

Keep in mind that the mysql_native_password algorithm provides basic security for storing passwords. While it’s commonly used, it’s important to weigh its security implications against those of more modern hashing algorithms like caching_sha2_password.
Therefore, now let’s make an example using caching_sha2_password !!!

If you prefer using the caching_sha2_password hashing algorithm, you can configure ProxySQL’s admin-admin_credentials accordingly. Let’s walk through an example of how to set it up:

UPDATE global_variables
SET variable_value='admin:admin;radmin:'||CACHING_SHA2_PASSWORD('secretpassword')
WHERE variable_name='admin-admin_credentials';

In this example, the hashed password is ‘secretpassword’ hashed using the CACHING_SHA2_PASSWORD() function introduced in ProxySQL version 2.6.2 .

As always, remember to load the configuration at runtime using the following command:

LOAD ADMIN VARIABLES TO RUNTIME;

And finally, persist the change on disk using the following command:

SAVE ADMIN VARIABLES TO DISK;

Using Hashed Passwords in admin-admin_credentials in the Configuration File

While configuring admin-admin_credentials using SQL commands is convenient for runtime adjustments, you might prefer setting it up directly in the ProxySQL configuration file for initial configuration or automation purposes. Here’s how you can do it:

Open your ProxySQL configuration file, typically located at /etc/proxysql.cnf or /etc/proxysql/proxysql.cnf, in a text editor.

Locate the admin_credentials parameter within the admin_variables section of the configuration file.
Note: within the admin_variables section, the admin- prefix is omitted, and the variable admin-admin_credentials is referred as admin_credentials (without the admin- prefix).

To set up admin_credentials with hashed passwords using the mysql_native_password algorithm, use the following format:

admin_credentials='admin:admin;radmin:HASHED_PASSWORD_HASH'

Replace radmin with your desired username and HASHED_PASSWORD_HASH with the actual hashed password generated using the MYSQL_NATIVE_PASSWORD() function or another suitable method.

Similarly, to use the caching_sha2_password algorithm, replace HASHED_PASSWORD_HASH with the actual hashed password generated using the CACHING_SHA2_PASSWORD() function or another suitable method.

Remember to maintain the security of your configuration files by restricting access permissions and encrypting sensitive information where necessary.

Also remember that ProxySQL configuration file is read only on the very first start, as described in the documentation.

Handling Non-Printable Characters in Hashed Passwords

When using the caching_sha2_password hashing algorithm for admin-admin_credentials in ProxySQL, it’s important to note that the generated hash may contain non-printable characters, making it challenging to copy and paste directly. This can lead to errors when configuring ProxySQL, potentially causing authentication failures.

To prevent pitfalls and ensure a smooth configuration process, consider:

  • using text editors that support special characters
  • use automation to create the configuration file, instead of manually copy/paste the password
  • testing and validation: before applying the configuration changes, thoroughly test the admin-admin_credentials setup to verify that authentication works as expected

By following these suggestions, you can effectively handle non-printable characters in hashed passwords when configuring admin-admin_credentials with the caching_sha2_password algorithm in ProxySQL.

Conclusion: Empowering Secure Database Management with admin-admin_credentials

In the ever-evolving world of database management, security and efficiency reign supreme.
Throughout this guide, we’ve uncovered the significance of secure admin credential management and how admin-admin_credentials emerges as a beacon of security.

Whether you opt for clear text passwords or delve into the world of hashed passwords with algorithms like mysql_native_password or caching_sha2_password, ProxySQL stands ready to empower your administrative tasks, simplifying the complex and fortifying the vulnerable.

As you embark on your ProxySQL journey, remember to embrace best practices, stay vigilant against emerging threats, and tap into the wealth of expertise offered by ProxySQL LLC. Our consulting services are tailored to your unique needs, providing guidance, optimization, and security enhancements to propel your database infrastructure to new heights.

Additional resources

https://proxysql.com/documentation/global-variables/admin-variables/#admin-admin_credentials
https://proxysql.com/documentation/configuring-proxysql/
https://proxysql.com/documentation/configuration-file/
https://proxysql.com/documentation/password-management/#hashed-passwords-and-authentication
https://proxysql.com/contact-us/