May
16
2023
--

MySQL Connection Security With Connection Control Plugins

MySQL connection control plugin

As a database administrator, have you ever been in a situation when your database confronted a brute force attack? A brute force attack can be launched against a user account in MySQL. MySQL replies with success or error based on supplied credentials, and the time required for the verification is almost the same in either case. Hence, an attacker can launch a brute force attack against a MySQL user account at a rapid rate and can try many different passwords.

According to cryptography, a brute-force attack consists of an attacker trying many passwords or passphrases with the hope of eventually guessing correctly. The attacker systematically checks all possible passwords and passphrases until the correct one is found.

It’s not just brute force attacks going on; the IT industry has recently seen a steady increase in distributed denial of service (DDoS) attacks. Have you also been targeted in such a type of connection flow on port 3306?

Today we would like to walk you through a special kind of plugin, i.e., the connection_control plugin! It was introduced in MySQL 8.0 and also back-ported to MySQL 5.7 and MySQL 5.6.

What are connection control plugins?

The connection control plugin library allows administrators to introduce an increasing delay in the server response to connections after a designated number of consecutive unsuccessful login attempts.

The idea behind using a connection control plugin is to configure a MySQL server so that the server will delay its response. The unauthorized user or a client does not know whether the password is correct or not unless the server replies. Thus, if an attacker attacks a server by spawning multiple connection requests, such connections have to be active until the time server replies. Introducing a delay makes it harder for attackers because now resources are occupied with ensuring connection requests are active. This technique can slow down brute force attacks against MySQL user accounts.

The plugin library contains two plugins:

  • CONNECTION_CONTROL checks incoming connection attempts and adds a delay to server responses as necessary. This plugin also exposes system variables that enable its operation to be configured and a status variable that provides rudimentary monitoring information.

How to install connection control plugins

To load the plugins at runtime, use these statements, adjusting the .so suffix for your platform as necessary. Here I am going to test it with Percona Server for MySQL 5.7.36:

mysql> INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)

Or, you can install the plugin in my.cnf. Add these options under the [mysqld] option group in the MySQL configuration file (/etc/my.cnf):

[mysqld]

plugin-load-add=connection_control.so
connection-control=FORCE_PLUS_PERMANENT
connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT

Now let’s take a deeper look at each of these configurations options:

  • plugin-load-add=connection_control.so
    – Loads the connection_control.so library each time the server is started.
  • connection_control=FORCE_PLUS_PERMANENT
    – Prevents the server from running without the CONNECTION_CONTROL plugin, and server startup fails if the plugin does not initialize successfully.
  • connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT
    – Prevents the server from running without the CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS plugin, and server startup fails if the plugin does not initialize successfully.

To verify plugin installation, restart the server and examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
      FROM INFORMATION_SCHEMA.PLUGINS
      WHERE PLUGIN_NAME LIKE 'connection%';
+------------------------------------------+---------------+
| PLUGIN_NAME                              | PLUGIN_STATUS |
+------------------------------------------+---------------+
| CONNECTION_CONTROL                       | ACTIVE        |
| CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE        |
+------------------------------------------+---------------+

Configure connection control thresholds

Now, let’s configure the server response delay for failed connection attempts using these server parameters. We will set the threshold of consecutive failed connections tentative to three and add a connection delay of a minimum of one second.

mysql> SET GLOBAL connection_control_failed_connections_threshold = 3;
SET GLOBAL connection_control_min_connection_delay = 1000;  
SET GLOBAL connection_control_min_connection_delay = 90000;

Alternatively, to set and persist the variables at runtime, use these statements:

mysql> SET PERSIST connection_control_failed_connections_threshold = 3;
SET PERSIST connection_control_min_connection_delay = 1000;

Also, you can add these options under the [mysqld] option group in the MySQL configuration file (/etc/my.cnf) to adjust them later as necessary.

[mysqld]

connection_control_failed_connections_threshold=3
connection_control_min_connection_delay=1000 
connection_control_max_connection_delay=2147483647

Let’s talk about each of these variables in more detail:

Testing and monitoring the connections

First terminal:

Note, here we will add a minimum one-second delay and set the failed connection threshold to three. The max connection delay is set to 90 seconds.

mysql> SET GLOBAL connection_control_failed_connections_threshold = 3;
SET GLOBAL connection_control_min_connection_delay = 1000;
set global connection_control_max_connection_delay=90000;

Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%connection_control%';
+-------------------------------------------------+-------+
| Variable_name                                   | Value |
+-------------------------------------------------+-------+
| connection_control_failed_connections_threshold | 3     |
| connection_control_max_connection_delay         | 90000 |
| connection_control_min_connection_delay         | 1000  |
+-------------------------------------------------+-------+
3 rows in set (0.00 sec)

Try to fetch the values of these variables:

mysql> select * from information_schema.connection_control_failed_login_attempts;
Empty set (0.00 sec)

mysql> show global status like 'connection_control_%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 0     |
+------------------------------------+-------+
1 row in set (0.00 sec)

Initially, there are no failed connection attempts and no connection delay generated since we are freshly configuring the parameters here. So, there you can see an empty set for information_schema.connection_control_failed_login_attempts and zero Connection_control_delay_generated.

Second terminal:

With the above settings, I tested brute force for 53 fake connections.

Open another terminal and perform these incorrect connections as a root user, specifying a wrong password each time.

[root@ip-xxx-xx-xx-xx ~]# for i in `seq 1 53`;   do time mysql mysql  -uroot -p”try_an_incorrect_password” 
-h xxx.xx.x.x 2>&1 >/dev/null | grep meh ;   done
0.093
0.092
0.093
1.093
2.093
3.105
4.093
5.093
…
…
45.092
46.093
47.093
48.093
49.092
50.093

What’s happening with these connections?

  • In the MySQL processlist, each connection will be in the state of “Waiting in connection_control plugin.”
  • Each connection will experience small but noticeable delays after the third connection attempt and will keep on increasing until you make the last attempt. With each subsequent failed attempt, the delay is increased by one second until it reaches the maximum limit. Meaning if the 50th connection is established after three unsuccessful logins, the 51st connection will take 51 seconds, and the 52nd connection will again take 52 seconds, and so on. This means the delay keeps on increasing until the connection_control_max_connection_delay is reached. As such, automated brute force attack tools will no longer be as useful since they will face continuous delays.

First terminal

Now switch back to the first terminal and recheck the values of the variables.

The connection_control starts monitoring all failed connection attempts and keeps track of consecutive failed connection attempts for each user.

Until consecutive failed attempts are less than the threshold, i.e., three, in our case, the user does not experience any delay. This should avoid delay in genuine cases where the user incorrectly typed his/her password.

Here you can notice that the status of Connection_control_delay_generated is now 50, and CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS is 53.

mysql> show global status like 'connection_control_%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 50    |
+------------------------------------+-------+
1 row in set (0.00 sec)

mysql> SELECT FAILED_ATTEMPTS FROM INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
+-----------------+
| FAILED_ATTEMPTS |
+-----------------+
|              53 |
+-----------------+
1 row in set (0.00 sec)

What happens when you want to make a successful/genuine login?

Please note the server will continue to introduce such a delay for all subsequent failed connections and the first successful connection. Hence, the delay generated for the first successful login attempt after 53 unsuccessful logins is 53 seconds. Suppose MySQL does not add any delay after the first successful connection, in that case, the attacker will get an indication that delay implies the wrong password and thus free pending connections after waiting for a specific amount of time. So, if you try to make one successful connection after N number of unsuccessful attempts, you will surely experience a delay of N seconds for the first successful login attempt.

[root@ip-xxx-xx-xx-xx ~]# date; mysql -uroot -p’correct_password’ -hxxx.xx.x.x -e "select now();";date
Tue Apr 18 06:27:36 PM UTC 2023
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------+
| now()               |
+---------------------+
| 2023-04-18 18:28:29 |
+---------------------+
Tue Apr 18 06:28:29 PM UTC 2023

Which user caused this brute force attack?

You can also determine from which user or host these failed connection attempts were made.

mysql> select * from information_schema.connection_control_failed_login_attempts;
+-----------------------+-----------------+
| USERHOST              | FAILED_ATTEMPTS |
+-----------------------+-----------------+
| 'root'@'xxx-xx-xx-xx' |              53 |
+-----------------------+-----------------+
1 row in set (0.00 sec)

How to reset the failed connection threshold

In case you want to reset these counters, you just have to again assign a value to the variable connection_control_failed_connections_threshold:

mysql> SET GLOBAL connection_control_failed_connections_threshold = 4;
Query OK, 0 rows affected (0.00 sec)

# Now you can see the values are reset!
mysql> select * from information_schema.connection_control_failed_login_attempts;
Empty set (0.00 sec)

mysql> show global status like 'connection_control_%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 0     |
+------------------------------------+-------+
1 row in set (0.00 sec)

Conclusion

MySQL connection control can be very useful to limit the inconvenience of a brute force attack or unseemly TCP connections. Percona Server for MySQL also supports these plugins. By default, it is not enabled, but you can perform the same steps to enable the plugin and set up a secure connection for your environment.

To summarize, the plugin provides the following:

  • A way to configure a threshold after which an increasing delay is triggered
  • Ability to configure a delay in the server’s reply with minimum and maximum limits
  • An information schema view to see monitoring information related to failed connection attempts

Please check our latest blogs on how you can keep your database secure:

Keep Your Database Secure With Percona Advisors

Improving MySQL Password Security with Validation Plugin

MySQL 8: Account Locking

Brute-Force MySQL Password From a Hash

Version 2 Advisor Checks for PMM 2.28 and Newer

May
02
2023
--

How To Use pt-secure-collect for Capturing Data in a Secure Way From the OS and Database System

How To Use pt-secure-collect

Sometimes crucial data sharing is avoided because of compliance rules, organizational policies, or numerous security concerns. The common use cases involve sharing pt-mysql-summary, pt-stalk, and other OS-related details to assist Support Engineers or any other third-party team troubleshoot database-related issues.

In this context, pt-secure-collect is a very important utility from Percona, which helps capture the required information securely and also provides aid in masking the existing information.

Pt-secure-collect helps in collecting, sanitizing, and encrypting data from various sources. By default, this utility collects the output with the help of pt-stalk, pt-summary, and pt-mysql-summary.

Let’s see how this tool works.

Installation

The tool can be installed via the Percona official repositories:

sudo yum install percona-toolkit

Another option for downloading pt-secure-collect is either via the Percona Toolkit or directly installing the specific tool.

shell> sudo wget https://downloads.percona.com/downloads/percona-toolkit/3.5.2/binary/redhat/7/x86_64/percona-toolkit-3.5.2-2.el7.x86_64.rpm 
shell> sudo yum install percona-toolkit-3.5.2-2.el7.x86_64.rpm

OR

shell> sudo wget percona.com/get/pt-secure-collect 
shell> sudo chmod +x pt-secure-collect

 Now, let’s run our first command to capture the OS/Database-related details from the tool.

shell> ./pt-secure-collect collect --bin-dir=/usr/bin/ --temp-dir=/home/vagrant/pt/ --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=Root@1234
Encryption password

Output:

INFO[2023-04-22 06:54:10] Temp directory is "/home/vagrant/pt/"
INFO[2023-04-22 06:54:10] Creating output file "/home/vagrant/pt/pt-stalk_2023-04-22_06_54_10.out"  
INFO[2023-04-22 06:54:10] Running pt-stalk --no-stalk --iterations=2 --sleep=30 --host=localhost --dest=/home/vagrant/pt/ --port=3306 --user=root --password=********  
INFO[2023-04-22 06:55:42] Creating output file "/home/vagrant/pt/pt-summary_2023-04-22_06_55_42.out"  
INFO[2023-04-22 06:55:42] Running pt-summary                            
INFO[2023-04-22 06:55:48] Creating output file "/home/vagrant/pt/pt-mysql-summary_2023-04-22_06_55_48.out"  
INFO[2023-04-22 06:55:48] Running pt-mysql-summary --host=localhost --port=3306 --user=root --password=********  
INFO[2023-04-22 06:56:01] Sanitizing output collected data              
INFO[2023-04-22 06:56:17] Creating tar file "/home/vagrant/pt/pt.tar.gz"  
INFO[2023-04-22 06:56:17] Encrypting "/home/vagrant/pt/pt.tar.gz" file into "/home/vagrant/pt/pt.tar.gz.aes"  
INFO[2023-04-22 06:56:17] Skipping encrypted file "pt.tar.gz.aes"   

So, here the above command collected the data from the “pt*” tools securely. By default, it encrypts the data and asks for the encryption password as well. However, we can skip that part by mentioning this option “ –no-encrypt”  option. 

Options:-

--bin-dir => Directory having the Percona Toolkit binaries (pt* tools). 
--temp-dir => Temporary directory used for the data collection.

Note – In order to run the command successfully all prerequisites binaries of (pt-stalk, pt-summary, and pt-mysql-summary) must be present and included in the command.

Let’s decrypt the file and observe the captured details:

shell> ./pt-secure-collect decrypt /home/vagrant/pt/pt.tar.gz.aes  --outfile=/home/vagrant/pt/pt.tar.gz
Encryption password:
INFO[2023-04-22 07:01:55] Decrypting file "/home/vagrant/pt/pt.tar.gz.aes" into "/home/vagrant/pt/pt.tar.gz" 

Note – Here, we need to provide the password which we used at the time of encryption.

--outfile => Write the output to this file. If omitted, the output file name will be the same as the input file, adding the .aes extension.

Now, inside the path, we can see the unencrypted file. Followed by this, we can uncompress the file to see the contents.

shell> /home/vagrant/pt 
-rw-------. 1 vagrant vagrant 500K Apr 22 07:01 pt.tar.gz

shell> tar -xzvf pt.tar.gz

Let’s look at a couple of examples where the sensitive data has been altered or masked.

  • With pt-secure-collect:
Hostname | hostname 
log_error | /var/log/hostname 
Config File | /etc/hostname 
pid-file        = /var/run/mysqld/hostname 
log-error     = /var/log/hostname 
socket        = /var/lib/mysql/hostname

  • Without pt-secure-collect:
Hostname | localhost.localdomain 
log_error | /var/log/mysqld.log 
Config File | /etc/my.cnf 
pid-file       = /var/run/mysqld/mysqld.pid 
log-error     = /var/log/mysqld.log 
socket        = /var/lib/mysql/mysql.sock

Note – We can clearly see some differences in the both types of outputs. With pt-secure-collection the above information was just replaced with some random value(“hostname”).

Now, let’s see how we can sanitize an existing file “pt-mysql-summary.out” and mask the critical information that ends with the below output section.

shell> ./pt-secure-collect sanitize --input-file=/home/vagrant/pt-mysql-summary.out > /home/vagrant/pt-mysql-summary_sanitize.out

Output:

Hostname | hostname 
Pidfile | /var/run/mysqld/hostname (exists) 
log_error | /var/log/hostname 
Config File | /etc/hostname 
pid-file        = /var/run/mysqld/hostname 
log-error     = /var/log/hostname 
socket        = /var/lib/mysql/hostname 
log-error     = /var/log/mariadb/hostname
pid-file        = /var/run/mariadb/hostname

You may also control the information which you want to skip from masking with settings with option –no-sanitize-hostnames and  –no-sanitize-queries.

Here, we see one more example where the critical information, such as “hostname” details inside the OS log file (“/var/log/messages”), is masked/replaced by some other value.

shell> sudo ./pt-secure-collect sanitize --input-file=/var/log/messages > /home/vagrant/messages_sanitize.out

 

Output (without pt-secure-collect):

Apr 23 03:37:13 localhost pmm-agent: #033[31mERRO#033[0m[2023-04-23T03:37:13.547+00:00] time="2023-04-23T03:37:13Z" level=error msg="Error opening connection to ProxySQL: dial tcp 127.0.0.1:6032: connect: connection refused" source="exporter.go:169"  #033[31magentID#033[0m=/agent_id/04dd6ad8-5c2e-4c52-a624-eb3bc7357651 #033[31mcomponent#033[0m=agent-process #033[31mtype#033[0m=proxysql_exporter

Output (with pt-secure-collect):

Apr 23 03:37:13 localhost pmm-agent: #033[31mERRO#033[0m[2023-04-23T03:37:13.547+00:00] time="2023-04-23T03:37:13Z" level=error msg="Error opening connection to ProxySQL: dial tcp hostname:6032: connect: connection refused" source="hostname:169"  #033[31magentID#033[0m=/agent_id/04dd6ad8-5c2e-4c52-a624-eb3bc7357651 #033[31mcomponent#033[0m=agent-process #033[31mtype#033[0m=proxysql_exporte

 

Summary

With the help of this tool, both OS and database-level information/logs can be encrypted or masked with some different values to hide the sensitive data. This tool comes in handy while dealing with critical data troubleshooting with any third-party stakeholders and also maintains security/compliance-related practices.

Percona Toolkit is a collection of advanced open source command-line tools, developed and used by the Percona technical staff, that are engineered to perform a variety of MySQL, MariaDB, MongoDB, and PostgreSQL server and system tasks that are too difficult or complex to perform manually.

 

Learn more about Percona Toolkit

Apr
28
2023
--

Add More Security to Your Percona Server for MongoDB With AWS IAM integration!

MongoDB With AWS IAM Integration

Did you notice that Percona Server for MongoDB 6.0.5-4 was released just a few days ago? This time around, it introduced improvements to the way we handle master key rotation for data at rest encryption as well as AWS IAM integration.

One key to rule them all — improvements to master key rotation

With the improvements introduced in Percona Server for MongoDB 6.0.5-4, one key path can be used for all servers in a clustered environment. This allows us to use one vault key namespace for all nodes in a deployment while at the same time preserving key versions and allowing each node to perform key rotation without impact to the other nodes.

Changes introduced with Percona Server for MongoDB 6.0.5-4 now allow using the same key for all the members of a replica set if the user chooses so, without impact on functionality.

Why should you care about AWS IAM integration?

With all the systems users need to access daily, password management becomes a more pressing issue. The introduction of IAM systems to an enterprise has become somewhat of a security standard in large enterprises.

Our users approached us about integration with AWS IAM, commonly used in their organizations. It’s an integration missing from MongoDB Community Edition (CE) that is important for compliance with enterprise security policies of many companies. Integration with AWS IAM allows:

MongoDB AWS IAM integration

To set up this integration, follow the steps outlined in our documentation, and configure either the user or the role authentication. This will allow AWS Security Token Service (STS) to play the part of Identity Provider (IDP) in a SAML 2.0-based federation.

Your feedback matters!

We take pride in being open to feedback in Percona. Please do not hesitate to contact us via the community forums or this contact form.

What’s next

We are looking into the problems affecting large size datastores that are a pain to our users. Please let us know if there are any particular issues you are struggling with in MongoDB; we are always open to suggestions!

Learn more about Percona Server for MongoDB

Apr
26
2023
--

Open Source Software Security: Is Open Source Software Safe?

is open source software safe

Even though open source software is firmly in the mainstream, used widely by businesses, governments, and everyone who owns a cell phone or computer, the question repeatedly arises: “Is open source software safe?” Broadly speaking, the answer is a resounding yes. But it’s worth examining what we mean by “safe,” contrasting open source software with proprietary software, and discussing when you should use caution.

Defining “safe” for software

Let’s start by defining what we mean by “safe” because it’s a non-specific term that might mean different things to different people.

Safe, here, encompasses security, stability, sustainability, and compliance.

Specifically, does open source software meet a reasonable security expectation comparable to or better than proprietary software? Is open source software as stable as other software? Is it sustainable, that is – will the software continue to be developed and maintained long-term, predictably, so you can depend on it? And, finally, does open source software carry any risks around legal compliance?

Finally, let’s clarify what we’re discussing with the phrase “open source software.” Anybody can slap an open source license on some software and put it online. Our bar is higher than that. We’re not addressing hobby projects or those that don’t have an active community.

When discussing open source software, we’re talking about ongoing projects with a healthy community and substantial adoption. We will talk about how to assess that when choosing a project.

Let’s start with the big one, security.

Is open source software secure?

When a project’s source code is available to all, the question of security isn’t far behind. How can something be secure if anyone can examine the code and look for security flaws?

Would-be attackers can comb through source code to find security flaws. Sometimes they do! But it also allows “white hat” types to examine open source projects to try to find and fix vulnerabilities before attackers find them and use them. It allows organizations to identify potential vulnerabilities, report them, and apply fixes without depending on a single vendor.

The relative security of open source software has been examined repeatedly by researchers since the early 2000s. Open source software contains no more flaws on average than proprietary software. In some cases, it may have fewer vulnerabilities.

Security through obscurity – expecting software to be more secure if attackers can’t see the source code – doesn’t work. Attackers find and exploit vulnerabilities in proprietary software all the time. The Log4Shell vulnerability in Apache Log4j’s software made big headlines in 2021, but it wasn’t alone. Consider ProxyShell – a set of vulnerabilities in Microsoft Exchange that could result in Remote Code Execution (RCE).

That’s just one example. You can peruse Microsoft’s acknowledgments of security reports for a long list of vulnerabilities discovered by various researchers who found vulnerabilities in its software without access to source code. 

So, is open source software secure? In absolute terms, no software should be considered free of vulnerabilities. But, in relative terms, we’d say yes. Open source software is secure relative to proprietary software – and in some instances, we’d say more secure than proprietary software.

In all instances, open source software allows anyone to examine the software and attempt to provide fixes if they discover a vulnerability. Open source software does not depend on a single vendor that controls the software entirely.

Is open source software stable?

Users may also wonder if open source software is stable, whether it’s safe to use open source software in production environments, and that sort of thing.

Again, the answer is yes, but with a few caveats worth calling out. Let’s start with some prime examples of open source software being used where stability is crucial.

Watch the “Using Open Source Software to Optimize and Troubleshoot Your MySQL Environment” video sessions

Open source software powers the Internet. Linux, for example, is the most widely used operating system to run services you use daily. All the major cloud providers use Linux, your cell phone company is likely using Linux to deliver phone calls, it’s used by streaming services, social media companies, and so forth. If you’re an Android user, it’s in your phone.

That’s just the most obvious example. Open source databases, like MySQL and PostgreSQL, are among the most widely used and popular databases for workloads large and small.

There’s also WordPress and Drupal, content management systems (CMSes) that power millions of websites worldwide. (WordPress, in fact, powers this blog and uses a MySQL database to store its content.)

On the smaller side, you have tools like curl and SQLite embedded in millions of devices for various uses. Open source has even gone to Mars.

Caveats of open source software

One could write a book on the successful use of open source and how well it stacks up, stability-wise, next to proprietary software. What are the caveats?

You need to evaluate open source software the same way you’d evaluate any software. Look at how it’s produced, and the health of its community or vendor, and put it to the test in a proof-of-concept (POC) or otherwise evaluate it to verify it suits your needs.

The health of the community is a broad topic, I won’t try to explore it here fully. But, in short, check out the history of the project. See how many contributors it has, whether it has vendors who support it if you need support, and make sure it’s still being maintained.

If you examine Linux, MySQL, PostgreSQL, Kubernetes, WordPress, Apache Kafka, and thousands of other projects, you’ll find projects with long histories, widespread adoption, and vendors who will provide support above and beyond just supplying the software.

That brings us to sustainability.

Is open source software sustainable?

Sustainable is a phrase used a lot to describe environmentally friendly concerns. But when we say “sustainable” here, we’re talking about whether the development process that produces the software is sustainable. To put it another way: Can we depend on that software to be here tomorrow, next month, or next year? Even longer?

This question isn’t unique to open source software! The same forces that cause software companies to go out of business or cancel projects can impact open source. 

Proprietary software goes away all the time, particularly in the age of Software-as-a-Service. Consider all the projects in Google’s graveyard, like Google Reader, Stadia, G+, and too many messaging apps to even try to recount.

Maintainers aren’t suppliers

However, open source has an added wrinkle, and we want to discuss it head-on. Open source projects are often powered by maintainers who aren’t paid directly to work on those projects. 

Maintainers are not the same thing as suppliers and vendors. An open source project is not necessarily the same thing as a product. 

For example, many of the Apache Software Foundation (ASF) projects have contributors from many different companies. Some may be paid to work on the project full time, and others may contribute as part of their day job where the software is used in their work, but they have other responsibilities. 

So if you evaluate an open source project to use in your business, you need to do some due diligence about the project’s health to verify that it has the longevity you want. Again, this is similar to doing due diligence on a software vendor. 

How to evaluate open source projects

You can feel confident that Microsoft will be around in 10 years and still support Windows and SQL Server. Likewise, Linux and PostgreSQL will almost certainly be around in 10 years. Apple is unlikely to go out of business and drop iOS anytime soon. WordPress has been chugging along for years and years and powers a huge chunk of the Internet, and it’ll still be used for blogs well into the future. 

Open source data management software survey

On the other hand, you can look at a lot of proprietary software that has hit end of life when its vendor was acquired or management changed. Microsoft killed off VisualBasic while it was still popular, for example. Twitter snapped up Vine and then shuttered. Adobe has (largely) retired Flash, though you’ll find few people to mourn Flash and quite a few who were happy to see it go.

Open source software can reach its end of life too. The ASF, for example, has its “Attic” – a process and home for ASF projects that have become obsolete or failed to maintain a large enough community of maintainers.

How can you know if an open source project will be around for the long haul and receive updates? 

A good rule of thumb? Look for widely adopted open source software with a good track record, and it’s even better if multiple vendors work on and support the software

If it can be picked up and developed by multiple vendors, it’s a much safer bet. MySQL and PostgreSQL, for example, are great examples of projects with product equivalents with support options equivalent to proprietary software without the downsides of being proprietary.

What about open source software compliance?

Finally, the question on many people’s minds is whether open source software is safe from a compliance perspective. That is, does open source software introduce any legal requirements?

I’m not a lawyer, nor do I play one on TV, so this isn’t to be confused with legal advice. If you need a genuine legal opinion, you’ll definitely want to consult a lawyer – the same as if you wanted legal advice on an End User License Agreement (EULA) with proprietary software. 

That said – licenses that meet the Open Source Definition (OSD) from the Open Source Initiative (OSI) have conditions triggered on distribution rather than use. If you install and run the software but don’t distribute it, then you don’t have any requirements to meet. Distribution is when you need to verify compliance.

What is open source distribution?

What is distribution? If your organization conveys software to other entities, that generally counts as distribution. For example, if your organization makes an electronic device with embedded software under open source licenses and sells them to customers, that’s distribution. Depending on the license, you may need to include a notice about the software, or you may need to make the source code available to customers on request. 

At least one open source license, the Affero GNU Public License (AGPL), extends the distribution concept to include interaction over a network. So, if you’re using AGPL’ed software in a SaaS offering, that may require you to distribute the source code or provide a mechanism for distributing the source code to users of that SaaS. 

So, if your organization ships software under open source licenses, then you need to have a plan to comply with the license requirements. If you simply use open source software, maybe you have a bunch of servers running Linux and an open source database like MySQL, but don’t distribute the software? Then you don’t have any special requirements to worry about.

The most popular open source licenses

The OSI has approved quite a few licenses as OSD-compliant, but in practice, you’ll see only a handful of them in use. Most open source software uses one of four or five permissive licenses (Apache 2.0, MIT, BSD 2, or BSD 3 being most likely) or one of the reciprocal GPL variants. 

These licenses are well-understood. You can find ample guidance on working with them. 

The Ultimate Guide to Open Source Databases

EULAs, on the other hand, are non-standard and ever-changing. If you use Apple software, for instance, you’re probably familiar with having to agree to EULA changes every time you update your software. If you use proprietary enterprise software, it probably has restrictions and compliance requirements to keep track of as you deploy it. 

The good news about EULAs is that you don’t have to worry about modification or distribution – because you’re not allowed to do that, you don’t need to ask what to do if you make a modification and want to distribute it. Problem solved! 

So… is it safe?

The real answer is, of course, the disappointing but realistic “it depends.” Open source software is not inherently unsafe or less safe than proprietary software.

 

Percona Database Software Solutions

Apr
24
2023
--

How to Persist a Hashed Format Password Inside ProxySQL

persist a hashed password ProxySQL

In this blog post, we will see how to persist the password inside the ProxySQL mysql_users table in hashed format only. Also, even if someone stored the password in cleartext, we see how to change those into the hashed format easily.

Here we are just highlighting one of the scenarios during work on the client environment where we noticed that the ProxySQL mysql_users table had more than 100 user entries, but some of them were available/inserted into the clear text password, whereas some were inserted properly into hashed entries.

Before just explaining those simple commands that were used to fix those clear text entries into the hashed entry quickly, let’s see some more information about the ProxySQL mysql_users table and the password formats.

Password formats inside ProxySQL

ProxySQL is capable of storing passwords in two different formats within the mysql_users.password field, whether in-memory or on-disk. These formats include plain text and hashed passwords.

Plain text passwords are extremely vulnerable to unauthorized access, as anyone with access to the database or configuration files can easily read them. While storing these files in a secure location can mitigate some security concerns, there is still a risk of data breaches. Hashed passwords, on the other hand, are stored in the same format as passwords in the MySQL server’s “mysql.user.password” (before MySQL 8.0 version) or “mysql.user.authentication_string” column (since MySQL 8.0 version using the mysql_native_password plugin), providing an added layer of security.

In ProxySQL, any password that begins with an asterisk (*) is considered to be a hashed password.

The Admin interface of ProxySQL lacks a PASSWORD() function. Therefore, any passwords stored within ProxySQL are preserved in the format in which they were originally inserted. This format may either be plain text or a hashed value.

Note: In general, ProxySQL doesn’t support the user created using the caching_sha2_password plugin password, once the same mysql.user.authentication_string is stored inside the mysql_users.password column. Still, there is a workaround for using those user accounts that are created inside the database using the caching_sha2_password plugin by inserting the clear text password entries inside the ProxySQL mysql_users.password column, but that is not recommended as per security best practices to keep clear text password entries inside the ProxySQL. Hence, we could say, ProxySQL and MySQL communication better support users that are created with the mysql_native_password plugin inside the database. 

 

For more details, please check this blog post ProxySQL Support for MySQL caching_sha2_password and the official ProxySQL documentation Information about MySQL 8.0 – ProxySQL .

So, to explain this scenario, here we created four different test DB user accounts inside the database with the mysql_native_password plugin.

From a database node: 

Username Password (In Clear Text)
test1 test1
test2 test2
test3 test3
test4 test4
mysql [localhost:8028] {msandbox} ((none)) > select user,host,authentication_string,plugin from mysql.user where user like 'test%'G
*************************** 1. row ***************************
                 user: test1
                 host: localhost
authentication_string: *06C0BF5B64ECE2F648B5F048A71903906BA08E5C
               plugin: mysql_native_password
*************************** 2. row ***************************
                 user: test2
                 host: localhost
authentication_string: *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E
               plugin: mysql_native_password
*************************** 3. row ***************************
                 user: test3
                 host: localhost
authentication_string: *F357E78CABAD76FD3F1018EF85D78499B6ACC431
               plugin: mysql_native_password
*************************** 4. row ***************************
                 user: test4
                 host: localhost
authentication_string: *D159BBDA31273BE3F4F00715B4A439925C6A0F2D
               plugin: mysql_native_password
4 rows in set (0.00 sec)

From ProxySQL: 

Here we will insert the user accounts into the mysql_users tables in mixed clear text format as well as in hash format.

ProxySQL_Admin> INSERT INTO mysql_users(username,password) VALUES ('test1','test1'), ('test2','*7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E'),('test3','test3'), ('test4','*D159BBDA31273BE3F4F00715B4A439925C6A0F2D');
Query OK, 4 rows affected (0.00 sec)


ProxySQL_Admin> select distinct username,password from mysql_users where username like 'test%';
+----------+-------------------------------------------+
| username | password                                  |
+----------+-------------------------------------------+
| test1    | test1                                     |
| test2    | *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E |
| test3    | test3                                     |
| test4    | *D159BBDA31273BE3F4F00715B4A439925C6A0F2D |
+----------+-------------------------------------------+
4 rows in set (0.00 sec)


ProxySQL_Admin> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)


ProxySQL_Admin> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.01 sec)


ProxySQL_Admin> select distinct username,password from mysql_users where username like 'test%';
+----------+-------------------------------------------+
| username | password                                  |
+----------+-------------------------------------------+
| test1    | test1                                     |
| test2    | *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E |
| test3    | test3                                     |
| test4    | *D159BBDA31273BE3F4F00715B4A439925C6A0F2D |
+----------+-------------------------------------------+
4 rows in set (0.00 sec)

Below are some other ProxySQL configurations done to verify the ProxySQL’s mysql_users table password is working fine to establish a connection with the MySQL database.

ProxySQL_Admin> select hostgroup_id,hostname,port,status from mysql_servers;
+--------------+-----------+------+--------+
| hostgroup_id | hostname  | port | status |
+--------------+-----------+------+--------+
| 10           | localhost | 8028 | ONLINE |
+--------------+-----------+------+--------+
1 row in set (0.00 sec)


ProxySQL_Admin> select rule_id,active,proxy_port,match_digest,destination_hostgroup,retries,apply from mysql_query_rulesG
*************************** 1. row ***************************
              rule_id: 1048
               active: 1
           proxy_port: 6033
         match_digest: ^SELECT.*FOR UPDATE
destination_hostgroup: 10
              retries: 3
                apply: 1
*************************** 2. row ***************************
              rule_id: 1050
               active: 1
           proxy_port: 6033
         match_digest: ^SELECT.*$
destination_hostgroup: 10
              retries: 3
                apply: 1
2 rows in set (0.00 sec)

Let’s check the database connectivity via ProxySQL using these DB user accounts.

for i in {1..4}; do mysql -h 127.0.0.1 -utest$i -ptest$i -P6033 -e"select current_user(),version();";done
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+-----------+
| current_user()  | version() |
+-----------------+-----------+
| test1@localhost | 8.0.28    |
+-----------------+-----------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+-----------+
| current_user()  | version() |
+-----------------+-----------+
| test2@localhost | 8.0.28    |
+-----------------+-----------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+-----------+
| current_user()  | version() |
+-----------------+-----------+
| test3@localhost | 8.0.28    |
+-----------------+-----------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+-----------+
| current_user()  | version() |
+-----------------+-----------+
| test4@localhost | 8.0.28    |
+-----------------+-----------+

Here above, the main problem is that our mysql_users tables have some plain text passwords visible for some user accounts (test1 & test3), which we don’t want to keep in clear/plain text password format. Instead, all password entries should be available in the hashed password format.

There is one way to fix this problem: drop those clear text password entries for user accounts, get the password hash for those user passwords generated from the MySQL database prompt using the PASSWORD() function, and later insert those actual hashed entries inside the mysql_users table to fix the issue.

But as earlier mentioned, if our mysql_users table had a lot of (>100) entries, fixing those passwords manually can be a tedious job.

Note: Here it is assumed we are not using the percona-scheduler-admin client, which has the feature to sync your user accounts directly with the database nodes in the ProxySQL mysql_users table.

So for this case, let’s see the next section, where we will understand how the admin-hash_passwords variable will help us to solve this problem and persist only hashed password entries inside the ProxySQL’s mysql_users table.

ProxySQL’s admin-hash_passwords variable

ProxySQL version 1.2.3 has included a new global boolean variable called admin-hash_passwords, which is enabled by default to support hashed passwords. If admin-hash_passwords=true, passwords will be automatically hashed during runtime when executing the LOAD MYSQL USERS TO RUNTIME command. However, passwords stored in the mysql_users tables will not be automatically hashed.

Nevertheless, it is possible to hash these passwords both in-memory and on-disk by copying users from RUNTIME using commands such as SAVE MYSQL USERS FROM RUNTIME” after executing LOAD MYSQL USERS TO RUNTIME and then saving the updated information using SAVE MYSQL USERS TO DISK.

Let’s persist the hashed password inside ProxySQL

ProxySQL_Admin> select @@admin-hash_passwords;
+------------------------+
| @@admin-hash_passwords |
+------------------------+
| true                   |
+------------------------+
1 row in set (0.00 sec)


ProxySQL_Admin> select distinct username,password from mysql_users where username like 'test%';
+----------+-------------------------------------------+
| username | password                                  |
+----------+-------------------------------------------+
| test1    | test1                                     |
| test2    | *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E |
| test3    | test3                                     |
| test4    | *D159BBDA31273BE3F4F00715B4A439925C6A0F2D |
+----------+-------------------------------------------+
4 rows in set (0.00 sec)


ProxySQL_Admin> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)


ProxySQL_Admin> select distinct username,password from mysql_users where username like 'test%';
+----------+-------------------------------------------+
| username | password                                  |
+----------+-------------------------------------------+
| test1    | test1                                     |
| test2    | *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E |
| test3    | test3                                     |
| test4    | *D159BBDA31273BE3F4F00715B4A439925C6A0F2D |
+----------+-------------------------------------------+
4 rows in set (0.00 sec)

Currently, passwords are hashed at RUNTIME, but they are not hashed on the mysql_users table. To hash them inside the mysql_users table as well, we need to run the SAVE MYSQL USERS FROM RUNTIME command.

ProxySQL_Admin> SAVE MYSQL USERS FROM RUNTIME;
Query OK, 0 rows affected (0.00 sec)


ProxySQL_Admin> select distinct username,password from mysql_users where username like 'test%';
+----------+-------------------------------------------+
| username | password                                  |
+----------+-------------------------------------------+
| test1    | *06C0BF5B64ECE2F648B5F048A71903906BA08E5C |
| test2    | *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E |
| test3    | *F357E78CABAD76FD3F1018EF85D78499B6ACC431 |
| test4    | *D159BBDA31273BE3F4F00715B4A439925C6A0F2D |
+----------+-------------------------------------------+
4 rows in set (0.00 sec)

The command SAVE MYSQL USERS TO DISK can now be used to store/persist the hashed passwords on the disk.

ProxySQL_Admin> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.01 sec)


ProxySQL_Admin> select distinct username,password from mysql_users where username like 'test%';
+----------+-------------------------------------------+
| username | password                                  |
+----------+-------------------------------------------+
| test1    | *06C0BF5B64ECE2F648B5F048A71903906BA08E5C |
| test2    | *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E |
| test3    | *F357E78CABAD76FD3F1018EF85D78499B6ACC431 |
| test4    | *D159BBDA31273BE3F4F00715B4A439925C6A0F2D |
+----------+-------------------------------------------+
4 rows in set (0.00 sec)

Let’s verify the database connectivity via ProxySQL using these DB user accounts.

for i in {1..4}; do mysql -h 127.0.0.1 -utest$i -ptest$i -P6033 -e"select current_user(),version();";done
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+-----------+
| current_user()  | version() |
+-----------------+-----------+
| test1@localhost | 8.0.28    |
+-----------------+-----------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+-----------+
| current_user()  | version() |
+-----------------+-----------+
| test2@localhost | 8.0.28    |
+-----------------+-----------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+-----------+
| current_user()  | version() |
+-----------------+-----------+
| test3@localhost | 8.0.28    |
+-----------------+-----------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+-----------+
| current_user()  | version() |
+-----------------+-----------+
| test4@localhost | 8.0.28    |
+-----------------+-----------+

Conclusion

Using the admin-hash_passwords feature can be extremely beneficial when there are mixed-format password entries in the mysql_users table. By saving the RUNTIME entries, which consist of hashed passwords, to disk and persisting only these entries in the mysql_users table of ProxySQL, we can easily simplify the management of hashed password entries. Furthermore, to ensure that only hashed password entries are stored within ProxySQL, it is imperative to create database user accounts using the mysql_native_password plugin.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

 

Try Percona Distribution for MySQL today!

Apr
20
2023
--

Using Encryption-at-Rest for PostgreSQL in Kubernetes

encryption-at-rest in PostgreSQL

Data-at-rest encryption is essential for compliance with regulations that require the protection of sensitive data. Encryption can help organizations comply with regulations and avoid legal consequences and fines. It is also critical for securing sensitive data and avoiding data breaches.

PostgreSQL does not natively support Transparent Data Encryption (TDE). TDE is a database encryption technique that encrypts data at the column or table level, as opposed to full-disk encryption (FDE), which encrypts the entire database.

As for FDE, there are multiple options available for PostgreSQL. In this blog post, you will learn:

  • how to leverage FDE on Kubernetes with Percona Operator for PostgreSQL
  • how to start using encrypted storage for already running cluster

Prepare

In most public clouds, block storage is not encrypted by default. To enable the encryption of the storage in Kubernetes, you need to modify the StorageClass resource. This will instruct Container Storage Interface (CSI) to provision encrypted storage volume on your block storage (AWS EBS, GCP Persistent Disk, Ceph, etc.).

The configuration of the storage class depends on your storage plugin. For example, in Google Kubernetes Engine (GKE), you need to create the key in Cloud Key Management Service (KMS) and set it in the StorageClass:

apiVersion: storage.k8s.io/v1beta1
kind: StorageClass
metadata:
  name: my-enc-sc
provisioner: pd.csi.storage.gke.io
parameters:
  type: pd-standard
  disk-encryption-kms-key: KMS_KEY_ID

Get

KMS_KEY_ID

by following the instructions in this document.

For AWS EBS, you just need to add an encrypted field; the key in AWS KMS will be generated automatically.

apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: my-enc-sc
provisioner: kubernetes.io/aws-ebs
parameters:
  encrypted: 'true'
  fsType: ext4
  type: gp2
volumeBindingMode: WaitForFirstConsumer

Read more about storage encryption in the documentation of your cloud provider or storage project of your choice.

Try it out

Once you have the StorageClass created, it is time to use it. I will use Percona Operator for PostgreSQL v2 (currently in tech preview) in my tests, but such an approach can be used with any Percona Operator.

Deploy the operator by following our installation instructions. I will use the regular kubectl way:

kubectl apply -f deploy/bundle.yaml --server-side

Create a new cluster with encrypted storage

To create the cluster with encrypted storage, you must set the correct storage class in the Custom Resource.

spec:
  ...
  instances:
  - name: instance1
  ...
    dataVolumeClaimSpec:
      storageClassName: my-enc-sc
      accessModes:
      - ReadWriteOnce
      resources:
        requests:
          storage: 1Gi

Apply the custom resource:

kubectl apply -f deploy/cr.yaml

The cluster should be up and running, backed by encrypted storage.

Encrypt storage for existing cluster

This task boils down to switching from one StorageClass to another. With version two of the Operator, we have a notion of instance groups. They are absolutely fantastic for testing new configurations, including compute and storage.

  1. Start with a regular cluster with two nodes – Primary and Replica. Storage is not encrypted. (0-fde-pg.yaml)
  2. Add another instance group with two nodes, but this time with encrypted storage (1-fde-pg.yaml). To do that, we change the spec.instances section:
  1.   - name: instance2
        replicas: 2
        dataVolumeClaimSpec:
          storageClassName: my-enc-sc
          accessModes:
          - ReadWriteOnce
          resources:
            requests:
              storage: 1Gi

    Wait for replication to complete and see the traffic hitting new nodes.

  2. Terminate nodes with unencrypted storage by removing the old instance group from the Custom Resource (2-fde-pg.yaml).

Now your cluster runs using encrypted storage.

Conclusion

It is quite interesting that PostgreSQL does not have built-in data-at-rest encryption. Peter Zaitsev wrote a blog post about it in the past – Why PostgreSQL Needs Transparent Database Encryption (TDE) – and why it is needed.

Storage-level encryption allows you to keep your data safe, but it has its limitations. The top limitations are:

  1. You can’t encrypt database objects granularly, only the whole storage.
  2. Also (1) does not allow you to encrypt different data with different keys, which might be the blocker for compliance and regulations.
  3. Physical backups, when files are copied from the disk, are not encrypted.

Even with these limitations, encrypting the data is highly recommended. Try out our operator and let us know what you think.

  • Please use this forum for general discussions.
  • Submit JIRA issue for bugs, and improvements of feature requests.
  • For commercial support, please use our contact page.

The Percona Kubernetes Operators automate the creation, alteration, or deletion of members in your Percona Distribution for MySQL, MongoDB, or PostgreSQL environment.

 

Learn More About Percona Kubernetes Operators

Mar
21
2023
--

Increase the Ability to Securely Replicate Your Data and Restrict Replication To Row-based Events in MySQL

mysql replication

In this blog, I’ll discuss the use case for replication. We want to improve our ability to replicate your data and limit replication to row-based events securely, wherein we do not have control over the source(s).

The replica doesn’t have checking capabilities when processing replicated transactions as of MySQL 8.0.18. It does this to carry out all instructions from its upstream. The replica must impose data access limitations on the replicated stream because changes may get past the security barrier separating the source and replica in some configurations. In that situation, implementing the upstream changes in a more constrained security context is beneficial to organizations needing privilege-controlled aggregate data from multiple separate databases.

In MySQL 8.0.18, a new feature PRIVILEGE_CHECKS_USER is introduced in replication channels. When a PRIVILEGE CHECKS USER account is used, a replication channel is more protected from unauthorized or unintentional use of privileged or undesirable actions.

By default, the replication threads use the privileges of the MySQL user account configured for replication. This can pose a security risk if the user account has privileges not necessary for replication, such as the SUPER privilege or the ability to execute arbitrary SQL statements. You can reduce the risk of unauthorized access or malicious attacks by setting PRIVILEGE_CHECKS_USER to a separate user account with limited privileges. The replication threads will use the limited privileges of the PRIVILEGE_CHECKS_USER account, which can help prevent the execution of unauthorized SQL statements or the manipulation of sensitive data.

PRIVILEGE CHECKS USER is helpful in multi-source replication to carefully gather data from various sources.

Configure user on replica

mysql> STOP REPLICA;

mysql> SET SQL_LOG_BIN=0;

mysql> CREATE USER repl;

mysql> GRANT REPLICATION_APPLIER, SESSION_VARIABLES_ADMIN, CREATE, INSERT, DELETE, UPDATE ON *.* TO repl;

mysql> SET SQL_LOG_BIN=1;

mysql> CHANGE REPLICATION SOURCE TO PRIVILEGE_CHECKS_USER = repl FOR CHANNEL 'channel1';

mysql> START REPLICA;

1 row in set (0.00 sec)

Limitation

  • The SESSION_VARIABLES_ADMIN privilege is a subset of the SYSTEM_VARIABLES_ADMIN and SUPER privileges. SESSION VARIABLES ADMIN might be a security risk if the replication provider is unreliable.
  • Minimal performance issues may be noticed.
  • Messages are recorded in the error file if the channel terminates due to failed privilege checks.

Another feature, REQUIRE ROW FORMAT, was added in MySQL 8.0.19, limiting replication to row-based replication events. The replication channel will only take row-based replication events if REQUIRE ROW FORMAT is set. If REQUIRE ROW FORMAT is enabled, row-based binary logging (binlog format=ROW) is required on the source server.

Configure REQUIRE_ROW_FORMAT 

mysql> STOP REPLICA;

mysql> CHANGE REPLICATION SOURCE TO REQUIRE_ROW_FORMAT = 1;

mysql> START REPLICA;

1 row in set (0.00 sec)

All transactions received and implemented after this new feature has been enabled in a replication channel are checked, and the following are not permitted:

(Upon encountering any of these events, replication shall fail and stop.)

  • DML that logged for statement-based replication
  • Creation or deletion of temp tables: The reason for disallowing the replication of temporary tables is that they may contain sensitive data that is not intended to be persisted beyond their lifetime. For example, a temporary table may contain sensitive customer data used to calculate a tax or fee, but this data may be deleted as soon as the calculation is complete. If this temporary data is replicated to the replica, it may be vulnerable to exposure or manipulation.
  • LOAD DATA events: The reason for disallowing the replication of LOAD DATA instructions is that the data loaded from the file may be in plaintext and not encrypted. When the data is loaded into the database using SQL statements, it can be encrypted before insertion to protect it from unauthorized access or disclosure. However, when the data is loaded using LOAD DATA, it is not automatically encrypted and may be vulnerable to tampering or interception.

Summary

Using PRIVILEGE_CHECKS_USER and require_row_format can further increase the security of a MySQL replication environment.

As mentioned earlier, PRIVILEGE_CHECKS_USER can limit the privileges required by the replication threads. You can reduce the risk of unauthorized access by setting PRIVILEGE_CHECKS_USER to a separate user account with limited privileges.

require_row_format, on the other hand, can be used to ensure that tables in the InnoDB storage engine use the ROW format, which is the most secure option for replication. When require_row_format is set to ON, MySQL will require that all tables that use the InnoDB storage engine use the ROW format. This can help ensure that replication events are processed correctly and can help prevent data inconsistencies.

By combining PRIVILEGE_CHECKS_USER and require_row_format, you can create a more secure replication environment. The replication threads will use the limited privileges of the PRIVILEGE_CHECKS_USER account, while the tables in the InnoDB storage engine will use the secure ROW format. This can help prevent unauthorized access to sensitive data or operations and reduce the risk of data inconsistencies during replication.

It’s important to note that using PRIVILEGE_CHECKS_USER and require_row_format should be done carefully, as changing these variables can have implications for the performance and functionality of the replication environment. Testing any changes in a development or test environment before implementing them in a production environment is recommended.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

Try Percona Distribution for MySQL today!

Feb
20
2023
--

Feedback Wanted: Making EXPLAIN Require Less Privileges for INSERT/UPDATE/DELETE Statements

Making EXPLAIN Require Less Privileges

Introduction/TLDR:

We are considering changing EXPLAIN in Percona Server for MySQL to require less privileges for providing execution plans for INSERT/UPDATE/DELETE statements (and possibly changing the behavior for EXPLAIN SELECT as well), to make it more convenient and safer to use with monitoring and query analysis tools. We would like to get feedback from the Community about the different approaches for achieving this.

The problem:

Running EXPLAIN is a great way to understand how complex SQL statements are executed. So it is natural that monitoring and query analysis tools utilize EXPLAIN for these purposes.

However, there is a problem for cases when INSERT/UPDATE/DELETE statements need to be explained. Running EXPLAIN for these statements, a read-only operation, requires the same privileges as running the original statements which would change data! This means that to be able to run EXPLAIN for any DML statement which was executed in the system users employed by monitoring and query analysis tools need to be granted global INSERT/UPDATE/DELETE privileges along with global SELECT privilege.

Granting such broad privileges to a monitoring and query analysis tool user account sounds like a bad idea from a security point of view. Many users would be wary of doing this. Also, for example, it is not recommended by the Percona Monitoring and Management manual. But without these privileges it becomes impossible for these tools to analyze problematic data-change statements using EXPLAIN.

In Percona, we are thinking about solving this problem by making EXPLAIN for statements like INSERT/UPDATE/DELETE to require fewer privileges than the original statements being explained. In principle, requiring only SELECT privilege for explaining INSERT/UPDATE/DELETE should be sufficient to solve the problem. But ideally, there should be a separate EXPLAIN_PLAN privilege that will be more restrictive than SELECT.

Possible solutions:

We are considering several approaches to implementing this, each with their own benefits and drawbacks. But before proceeding to describe them, let me mention a couple of complications to keep in mind:

  • By its nature, an EXPLAIN statement might disclose information about the contents of the tables (e.g., how many rows are there for a certain key value). This can be a problem for some sensitive data. Currently, this can be avoided by using partial revokes feature to revoke any privileges on schemas in which sensitive data are stored from Monitoring/QuAn users.
  • The introduction of an EXPLAIN_PLAN privilege which supports levels other than global (e.g., can be granted/revoked or partially revoked on schema level) will break binary compatibility of ACL tables with upstream. This results in a more complicated migration process from upstream Oracle MySQL to Percona Server for MySQL. Moreover, it might cause more issues in the future if upstream decides to change ACL tables in some other direction. So we try to avoid solutions which require incompatible changes like this.

Here are the approaches we are thinking about at the moment:

1. We introduce a new global dynamic EXPLAIN_PLAN privilege to be granted to monitoring and query analysis tool users. The EXPLAIN code is changed to require either this global EXPLAIN_PLAN privilege OR privileges that explained statement   requires.

+) The patch implementing this approach is fairly simple and minimally intrusive. So it is less likely to introduce bugs and cause merge troubles with upstream.

+) Existing user’s code/scenarios continue to work (but see comment about partial revokes below!).

-) Since the new privilege is global, it won’t work with partial revokes (or making it work will significantly increase complexity and intrusiveness of this change). So there won’t be a way to avoid disclosure of data from sensitive tables to monitoring and query analysis tool users through EXPLAIN.

2. We introduce a new global dynamic EXPLAIN_DML privilege to be granted to monitoring and query analysis tool users. Unlike in the previous case, it is interpreted like a flag (there is precedent for this, see NDB_STORED_USER privilege). This flag indicates that EXPLAIN for DML statements run by user require SELECT privilege instead of appropriate INSERT/UPDATE/DELETE privileges.

+) This code change can be fairly straightforward, except the code to handle special cases of performance_schema and information_schema tables.

+) This approach works with partial revokes, so sensitive data can be protected.

+) All scenarios for existing users continue to work as is, as only users with a new EXPLAIN_DML flag are affected.

-) The patch is fairly big and intrusive (as we need to touch places where DML currently checks if users have INSERT/UPDATE/DELETE privileges).

There is a variant of this approach which uses a start-up option to identify users requiring special treatment instead of privilege.

3. We introduce a new global dynamic EXPLAIN_ONLY privilege. This privilege is “negative” – meaning that it blocks execution of any non-EXPLAIN statements while active. EXPLAIN statements still require the same privileges for their execution as before. The idea is that with EXPLAIN_ONLY it becomes possible to create a role, to be used by monitoring and query analysis tools, with all necessary “strong” privileges for INSERT/ UPDATE/DELETE statements, but restricted to the execution of EXPLAIN statements only.

+) The patch implementing this approach is fairly simple and non-intrusive.

+) Existing user’s code/scenarios continue to work.

+) This approach works with partial revokes, so sensitive data can be protected.

-) To be able to execute EXPLAIN statements, without sacrificing access to P_S and I_S tables, monitoring and query analysis tools will have to constantly activate and deactivate this special role to which EXPLAIN_ONLY was granted.

-) We are introducing a new “negative” privilege concept which is not in line with existing privileges, so it will require additional documentation and explanations.

The optimal choice between these approaches is not straightforward and depends on what use-cases are considered to be important.

Our request:

We would like to get feedback from the Community about the suggested approaches. What is your preference and why? Will they work for you or not? Or perhaps you see some problems we have missed? Maybe you have some great alternative idea? Please let us know what you think about them by leaving comments below this post.

We would especially appreciate feedback from people working on and with monitoring and query analysis tools! Would you see value in this change?

It would be also great to hear feedback from the Oracle MySQL Team, as we plan to contribute upstream these changes once they are implemented! We have created feature request #110132 “Allow EXPLAIN for DML to be run with lower/read-only privileges” for the upstream for this.

Thank you in advance!

Feb
02
2023
--

PostgreSQL Database Security: External Server-Based Authentication

PostgreSQL Database Security: External Server-Based Authentication

Database security is crucial to protecting sensitive information stored in a database. A critical aspect is authentication, which refers to verifying the identity of a user trying to access the database. The authentication process is typically accomplished through usernames and passwords and includes other factors such as security tokens and one-time passwords. Database Administrators (DBAs) must implement robust authentication mechanisms to ensure that only authorized users can access the database and its data. Additionally, it is best practice to monitor regularly and audit database authentication logs to detect and prevent unauthorized access attempts.

PostgreSQL server authentication refers to verifying the identity of a user trying to connect to the PostgreSQL server. This can be done using various methods, including password-based authentication, a client certificate, and external authentication methods such as GSSAPI, LDAP, and RADIUS. The pg_hba.conf file is used to configure the authentication methods the PostgreSQL server uses and can specify different methods for different types of connections. By default, PostgreSQL uses trust-based authentication, where any connection from localhost is considered trusted and doesn’t require a password. However, it’s important to note that it’s recommended to use password-based or external authentication methods for remote connections and production environments to ensure secure access to the data.

When talking about security, authentication is the first line of defense. PostgreSQL provides various methods of authentication, which are categorized into three categories.

 

PostgreSQL internal authentication and OS-based authentication have already been discussed in previous blogs. Now it’s time to discuss external authentication methods.

PostgreSQL external authentication

PostgreSQL supports a variety of external authentication methods, including GSSAPI, LDAP, and RADIUS. GSSAPI, or Generic Security Service Application Program Interface, which uses Kerberos to authenticate users. LDAP, or Lightweight Directory Access Protocol, authenticates users against an LDAP server. RADIUS, or Remote Authentication Dial-In User Service, authenticates users against a RADIUS server. These external authentication methods can secure a PostgreSQL database by providing a centralized way of managing user access and eliminating the need to store passwords in the database. However, it’s important to note that GSSAPI and RADIUS are only supported in PostgreSQL server versions 9.1 or later, and LDAP is a built-in feature for all versions.

LDAP (Lightweight Directory Access Protocol)

LDAP is a popular authentication method for enterprise environments, and it can also be used with PostgreSQL. In this section, we will go over how to set up LDAP authentication for PostgreSQL and provide an example of how it can be configured. Before you begin, you will need access to an LDAP server and a basic understanding of how it works. You will also need to have PostgreSQL installed and running on your server.

Step 1: Install the OpenLDAP server and client packages

sudo apt-get update 
sudo apt-get install slapd ldap-utils

Step 2: Configure the OpenLDAP server

sudo dpkg-reconfigure slapd

  • Select “No” when asked to configure the database with dbconfig-common.
  • Set the domain name for your LDAP server, for example, “example.com”.
  • Set the organization name for your LDAP server, for example, “Example Inc”.
  • Set the administrator password for your LDAP server.

Step 3: Create an LDAP directory structure

sudo ldapadd -x -D cn=admin,dc=example,dc=com -w <admin password> -f base.ldif

where “base.ldif” is a file containing the directory structure you want to create.

Step 4: Test the LDAP server

ldapsearch -x -b dc=example,dc=com -D cn=admin,dc=example,dc=com -w <admin password>

This command should return information about your LDAP directory.

Step 5: Add users and groups to the LDAP directory

ldapadd -x -D cn=admin,dc=example,dc=com -w <admin password> -f users.ldif

where “users.ldif” is a file containing the users and groups you want to add to the directory.

Step 6: Test the user authentication

ldapwhoami -x -D cn=<user>,dc=example,dc=com -w <user password>

This command should return the DN (Distinguished Name) of the user, indicating that the user

Step 7: Configure pg_hba.conf

The pg_hba.conf file controls the authentication methods used by PostgreSQL. To enable LDAP authentication, you’ll need to add a line to the pg_hba.conf file that specifies the LDAP server and method to be used.

For example:

host all all ldapserver=ldap.example.com ldapmethod=simple

This line tells PostgreSQL to use the LDAP server at ldap.example.com for authentication using the “simple” method.

Step 8: Restart PostgreSQL

After making changes to the pg_hba.conf file, you will need to restart the PostgreSQL service for the changes to take effect.

Step 9: Test the LDAP connection

You can test the connection to the LDAP server by running the following command:

ldapwhoami -h ldap.example.com -D "cn=admin,dc=example,dc=com" -w "adminpassword"

This command will bind to the LDAP server as the “cn=admin,dc=example,dc=com” user with the password “adminpassword”. If the connection is successful, you should see a message indicating the user you are currently bound as.

Step 10: Test LDAP authentication

To test LDAP authentication with PostgreSQL, you can connect to the database using the psql command-line interface. For example,

psql -U myldapuser -h localhost

If the LDAP authentication is set up correctly, you should be able to connect to the database using the myldapuser’s LDAP credentials.

GSSAPI (Generic Security Service Application Program Interface)

GSSAPI is an authentication method that allows users to authenticate to PostgreSQL using Kerberos. In this section, we will go over how to set up GSSAPI authentication for PostgreSQL and provide an example of how it can be configured.

Before you begin, you will need to have a Kerberos infrastructure and a basic understanding of how it works. You will also need to have PostgreSQL installed and running on your server and the Kerberos libraries (gssapi and gssapi_krb5) installed on the server where PostgreSQL is running.

Step 1: Configure pg_hba.conf

The pg_hba.conf file controls the authentication methods used by PostgreSQL. To enable GSSAPI authentication, you’ll need to add a line to the pg_hba.conf file that specifies the GSSAPI method to be used. For example:

hostgssenc postgres postgres 192.168.0.102/32 gss include_realm=0

This line tells PostgreSQL to use GSSAPI for authentication and not to include the Kerberos realm in the username. 

Step 2: Restart PostgreSQL

After making changes to the pg_hba.conf file, you will need to restart the PostgreSQL service for the changes to take effect. If you get this error, that means your server is not configured with –with-gssapi option.

2023-01-31 19:25:18.585 PKT [42302] LOG: hostgssenc record cannot match because GSSAPI is not supported by this build

Step 3: Kerberos configuration

You will need to configure Kerberos to work with PostgreSQL. You will need to create a service principal for the PostgreSQL server and add it to the Kerberos keytab file.

Step 4: Test the GSSAPI connection

You can test the GSSAPI connection by running the following command:

kinit -k -t /path/to/keytab postgres/your.postgres.host@YOURREALM.COM

This command will acquire a Kerberos ticket for the service principal you created in step 3.

Step 5: Test GSSAPI authentication

To test GSSAPI authentication with PostgreSQL, you can connect to the database using the psql command-line interface. For example:

psql "dbname=mydb host=myhost user=myuser"

If the GSSAPI authentication is set up correctly, you should be able to connect to the database using your Kerberos credentials.

SSPI (Security Support Provider Interface)

SSPI is an authentication method that allows users to authenticate to PostgreSQL using Windows’ built-in security features. It is similar to GSSAPI but specific to the Windows operating system.

The PostgreSQL server uses the SSPI library to negotiate authentication with the client using the Kerberos or NTLM protocols. To set up SSPI authentication in PostgreSQL, you must configure the pg_hba.conf file to use the “sspi” method and restart the PostgreSQL service.

For example, to enable SSPI authentication for all connections from the local host, you would add the following line to the pg_hba.conf file:

host all all 127.0.0.1/32 sspi

This line tells PostgreSQL to use SSPI for authentication for all connections coming from the IP address 127.0.0.1, which corresponds to the localhost. Once the changes are made, you will need to restart the PostgreSQL service for the changes to take effect. To test the SSPI authentication, you can connect to the database using the psql command-line interface. SSPI authentication is only supported on Windows and is available on PostgreSQL server version 9.1 or later.

RADIUS (Remote Authentication Dial-In User Service)

RADIUS is a widely used protocol for authenticating remote users. It can also be used to authenticate users in a PostgreSQL database. In this blog post, we will go over how to set up RADIUS authentication for PostgreSQL and provide an example of how it can be configured.

Before you begin, you will need access to a RADIUS server and a basic understanding of how it works.

Step 1: Install FreeRADIUS  

Here are the steps to install and configure a RADIUS server on Ubuntu:

sudo apt-get update sudo apt-get install freeradius freeradius-utils

Step 2: Configure the RADIUS server

  • The main configuration file for FreeRADIUS is located at /etc/freeradius/radiusd.conf.
  • The users file is located at /etc/freeradius/users.

Step 3: Add users to the RADIUS server

sudo nano /etc/freeradius/users

Add a new entry for each user in the following format:

username Auth-Type := Local, User-Password == "password"

Step 4: Restart the RADIUS server

sudo service freeradius restart

Step 5: Test the RADIUS server

sudo radtest username password 127.0.0.1 0 testing123

If the test is successful, you should receive an “Access-Accept” response.

Step 6: Configure pg_hba.conf

The pg_hba.conf file controls the authentication methods used by PostgreSQL. To enable RADIUS authentication, you’ll need to add a line to the pg_hba.conf file that specifies the RADIUS server and method to be used. For example:

host all all 0.0.0.0/0 radius radiusservers=192.168.1.1 radiussecrets=password radiusports=1812

This line tells PostgreSQL to use RADIUS for authentication.

Step 7: Restart PostgreSQL

After making changes to the pg_hba.conf and radius.conf files, you will need to restart the PostgreSQL service for the changes to take effect.

Step 8: Test RADIUS authentication

To test RADIUS authentication with PostgreSQL, you can try to connect to the database using the psql command-line interface. For example,

psql -U username -h localhost

If the RADIUS authentication is set up correctly, you should be able to connect to the database using your RADIUS credentials.

Conclusion

In conclusion, external authentication methods such as GSSAPI, LDAP, and RADIUS can enhance the security of a PostgreSQL database by providing a centralized way of managing user access and eliminating the need to store passwords in the database. GSSAPI uses Kerberos to authenticate users, LDAP authenticates users against an LDAP server, and RADIUS authenticates users against a RADIUS server.

It’s important to note that GSSAPI and RADIUS are only supported in PostgreSQL server versions 9.1 or later, and LDAP is a built-in feature for all versions. In addition to external authentication methods, the PostgreSQL server also supports password-based and client certificate-based authentication methods. It’s recommended to use password-based or external authentication methods for remote connections and production environments to ensure secure access to the data.

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open source community in a single distribution, designed and tested to work together.

Download Percona Distribution for PostgreSQL Today!

Jan
30
2023
--

Why PostgreSQL Needs Transparent Database Encryption (TDE)

PostgreSQL Needs Transparent Database Encryption

As Ibrar Ahmed noted in his blog post on Transparent Database Encryption (TDE). PostgreSQL is a surprising outlier when it comes to offering Transparent Database Encryption.  Instead, it seems PostgreSQL Developers are of the opinion that encryption is a storage-level problem and is better solved on the filesystem or block device level.  I do not share that opinion, and according to a poll I did late last year, many PostgreSQL community members don’t either.

There are two reasons TDE implemented inside the database is essential—technical reasons and non-technical reasons. In my opinion, engineers tend to focus on the technical side and stop on “technical solutions exist”, while in practice, what really matters, is if an organization is able to implement the solutions offered. This is where organizational issues (especially politics) play a big role, especially in large organizations.

For example, only a fraction of Oracle (and other legacy databases) workloads that could be migrated to PostgreSQL are actually migrated, even if it offers substantial cost savings and additional benefits, all because of organizational resistance.

But first, where does the need for encryption really come from? On a high level, some of it comes from Technologists who understand unencrypted sensitive data is not a good idea. Increasingly, though, the industry is not relying on engineers having common sense but rather on defined standards that companies need to comply with (HIPAA, PCI DSS, SOC 2, ISO 27001), etc.

Compliance requirements are typically written in rather general terms, and it is up to the compliance team (and compliance audit team) to translate these to technical requirements, many of which would prefer (or even require) TDE. 

Technical reasons for Transparent Database Encryption

Compared to storage-level encryption, encryption on the database side offers multiple benefits, including:

  • It offers more flexibility and granularity in the encryption of database objects (what is encrypted, what is not, and with what key).
  • If database files are copied or otherwise exposed in their raw form, exposure does not happen.  For example, physical backups stored in open S3 buckets are relatively common exposure vectors.
  • It does not require encryption capabilities on the storage level to provide data security.
  • It adds defense in depth. Even if the storage level encryption exists, having TDE plus storage level encryption gives an added layer of security.

Organizational (non-technical) reasons for TDE

Even if you found a way to deal with all the technical reasons you prefer encryption inside your database engine (TDE), you may be facing additional friction in your organization choosing this path.  This might be a challenge with the security/compliance team (as I already mentioned above) or require additional coordination with the team responsible for storage to ensure proper encryption of original data and all copies (including backups).

While these will look trivial for startups and small companies where all of those are single-person or well-connected teams, you may be surprised by how expensive those things are in some large organizations!

Encryption in the cloud Database as a Service (DBaaS) space

You may argue that storage-level encryption seems to be good enough for cloud vendors.  AWS, for example, offers encryption in RDS, which appears to be good enough for most of its customers!   However, things are a lot different in RDS environments; it is a rather restricted environment where many technical and organizational aspects are taken care of.  For example, if you have created an encrypted instance, backups will be automatically encrypted, too, eliminating the possibility of a “mistake.” 

State of TDE in PostgreSQL

Interestingly, the challenges of implementing Transparent Database Encryption in PostgreSQL are not just technical but organizational too.  In my memory, there have been several implementations offered for inclusion in PostgreSQL, but none made it through.  The Cybertec encryption patch existed for many years, and there was also work by NTT submitted a few years ago. Highgo seems to have worked on TDE, too, and there were talks at PgCon in other years.  What does not happen, though, is a feature merge into PostgreSQL Core. 

You may argue the PostgreSQL Core is conservative by design, and this is what made PostgreSQL such a stable product. I think, though, one needs to maintain the balance between innovation and stability; shifting too much to one side risks the long-term project future. 

Note I do not argue that including full TDE support in PostgreSQL Core is the right idea; it might be at this stage, we need innovation to happen through competition of several ideas and several implementations. What the PostgreSQL Core might highly benefit from is a pluggable storage interface, as this may not only implement encryption but also store data somewhere else than a locally mounted POSIX file system, such as what Neon is doing. 

Beyond Transparent Database Encryption in PostgreSQL

While I believe Transparent Database Encryption in PostgreSQL is important, I think it is just an illustration of a bigger question.  Is technical governance in PostgreSQL designed to maximize its success in the future, or is it more about sticking to the approaches that helped PostgreSQL reach current success levels? For a project of such scale and influence, there seems to be surprisingly little user impact on PostgreSQL Governance. The PostgreSQL Core Team consists of “seven long-time community members with various specializations” rather than having clear electable positions, as many other open source organizations do.  The development process in PostgreSQL is based around a mailing list rather than more modern and organized issue tracking and pull-request-based development workflows.   Interested in PostgreSQL Bugs? There is no bugs database that allows you to easily see which bug is confirmed and what version it was fixed in a user-friendly way. Instead, you need to dig through the bugs mailing list.

Do not get me wrong, PostgreSQL is a fantastic database, and it rightfully was Database of the Year more than any other database. Yet, I believe there are some opportunities to make PostgreSQL even more “future-proof” and be even more successful in the future.

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.

Download Percona Distribution for PostgreSQL Today!

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com