Sep
25
2018
--

Snyk raises $22M on a $100M valuation to detect security vulnerabilities in open source code

Open source software is now a $14 billion+ market and growing fast, in use in one way or another in 95 percent of all enterprises. But that expansion comes with a shadow: open source components can come with vulnerabilities, and so their widespread use in apps becomes a liability to a company’s cybersecurity.

Now, a startup out of the UK called Snyk, which has built a way to detect when those apps or components are compromised, is announcing a $22 million round of funding to meet the demand from enterprises wanting to tackle the issue head on.

Led by Accel, with participation from GV plus previous investors Boldstart Ventures and Heavybit, this Series B notably is the second round raised by Snyk within seven months — it raised a $7 million Series A in March. That’s a measure of how the company is growing (and how enthusiastic investors are about what it has built so far). The startup is not disclosing its valuation but a source close to the deal says it is around $100 million now (it’s raised about $33 million to date).

As another measure of Snyk’s growth, the company says it now has over 200 paying customers and 150,000 users, with revenues growing five-fold in the last nine months. In March, it had 130 paying customers.

(Current clients include ASOS, Digital Ocean, New Relic and Skyscanner, the company said.)

Snyk plays squarely in the middle of how the landscape for enterprise services exists today. It provides options for organisations to use it on-premises, via the cloud, or in a hybrid version of the two, with a range of paid and free tiers to get users acquainted with the service.

Guy Podjarny, the company’s CEO who co-founded Snyk with Assaf Hefetz and Danny Grander, explained that Snyk works in two parts. First, the startup has built a threat intelligence system “that listens to open source activity.” Tapping into open-conversation platforms — for example, GitHub commits and forum chatter — Snyk uses machine learning to detect potential mentions of vulnerabilities. It then funnels these to a team of human analysts, “who verify and curate the real ones in our vulnerability DB.”

Second, the company analyses source code repositories — including, again, GitHub as well as BitBucket — “to understand which open source components each one uses, flag the ones that are vulnerable, and then auto-fix them by proposing the right dependency version to use and through patches our security team builds.”

Open source components don’t have more vulnerabilities than closed source ones, he added, “but their heavy reuse makes those vulnerabilities more impactful.” Components can be used in thousands of applications, and by Snyk’s estimation, some 77 percent of those applications will end up with components that have security vulnerabilities. “As a result, the chances of an organisation being breached through a vulnerable open source component are far greater than a security flaw purely in their code.”

Podjarny says the plan is not to tackle proprietary code longer term but to expand how it can monitor apps built on open source.

“Our focus is on two fronts – building security tools developers love, and fixing open source security,” he said. “We believe the risk from insecure use of open source code is far greater than that of your own code, and is poorly addressed in the industry. We do intend to expand our protection from fixing known vulnerabilities in open source components to monitoring and securing them in runtime, flagging and containing malicious and compromised components.”

While this is a relatively new area for security teams to monitor and address, he added that the Equifax breach highlighted what might happen in the worst-case scenario if such issues go undetected. Snyk is not the only company that has identified the gap in the market. Black Duck focuses on flagging non-compliant open source licences, and offers some security features as well.

However, it is Snyk — whose name derives from a play on the word “sneak”, combined with the acronym meaning “so now you know” — that seems to be catching the most attention at the moment.

“Some of the largest data breaches in recent years were the result of unfixed vulnerabilities in open source dependencies; as a result, we’ve seen the adoption of tools to monitor and remediate such vulnerabilities grow exponentially,” said Philippe Botteri, partner at Accel, who is joining the board with this round. “We’ve also seen the ownership of application security shifting towards developers. We feel that Snyk is uniquely positioned in the market given the team’s deep security domain knowledge and developer-centric mindset, and are thrilled to join them on this mission of bringing security tools to developers.”

Sep
24
2018
--

Backing up Percona Server for MySQL with keyring_vault plugin enabled

Percona XtraBackup with keyring_vault

Percona XtraBackup with keyring_vaultTo use Percona XtraBackup with keyring_vault plugin enabled you need to take some special measures to secure a working backup. This post addresses how to backup Percona Server for MySQL with keyring_vault plugin enabled. We also run through the steps needed to restore the backup from the master to a slave.

This is the second of a two-part series on setting up Hashicorp Vault with Percona Server for MySQL with the keyring_vault plugin. First part is Using the keyring_vault plugin with Percona Server for MySQL 5.7.

Backing up from the master

First you need to install the latest Percona XtraBackup 2.4 package, in this tutorial I used this version:

[root@mysql1 ~]# xtrabackup --version
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=mysqld-bin --server-id=1
xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)

Create a transition key using any method you prefer.  This transition key will be used by Percona XtraBackup to encrypt keys of the files being backed up. Make sure to keep the transition key and not lose it or else the backup will be unrecoverable.

[root@mysql1 ~]# openssl rand -base64 24
NSu7kfUgcTTIY2ym7Qu6jnYOotOuMIeT

You can store the transition-key in Vault and retrieve it later:

[root@mysql1 ~]# # Store the transition-key to the Vault server
[root@mysql1 ~]# curl -H "Content-Type: application/json" -H "X-Vault-Token: be515093-b1a8-c799-b237-8e04ea90ad7a" --cacert "/etc/vault_ca/vault.pem" -X PUT -d '{"value": "NSu7kfUgcTTIY2ym7Qu6jnYOotOuMIeT"}' "https://192.168.0.114:8200/v1/secret/dc1/master/transition_key"
[root@mysql1 ~]# # Retrieve the transition-key from the Vault server
[root@mysql1 ~]# curl -s -H "X-Vault-Token: be515093-b1a8-c799-b237-8e04ea90ad7a" --cacert "/etc/vault_ca/vault.pem" -X GET "https://192.168.0.114:8200/v1/secret/dc1/master/transition_key" | jq .data.value
"NSu7kfUgcTTIY2ym7Qu6jnYOotOuMIeT"
[root@mysql1 ~]# # Delete the transition-key from the Vault server
[root@mysql1 ~]# curl -H "Content-Type: application/json" -H "X-Vault-Token: be515093-b1a8-c799-b237-8e04ea90ad7a" --cacert "/etc/vault_ca/vault.pem" -X DELETE "https://192.168.0.114:8200/v1/secret/dc1/master/transition_key"

We will stream the backup to the slave server using netcat, first run this on the slave:

[root@mysql2 ~]# ncat -l 9999 | cat - > backup.xbstream

Then on the master I used --stream=xbstream  since it fails with --stream=tar reported here (PXB-1571). Run the xtrabackup command like this:

[root@mysql1 ~]# xtrabackup --stream=xbstream --backup --target-dir=backup/ --transition-key=NSu7kfUgcTTIY2ym7Qu6jnYOotOuMIeT | nc 192.168.0.117 9999
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=mysqld-bin --server-id=1 --transition-key=*
xtrabackup: recognized client arguments: --datadir=/var/lib/mysql --log_bin=mysqld-bin --server-id=1 --transition-key=* --user=root --stream=xbstream --backup=1 --target-dir=backup/
180715 01:28:56  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: NO).
180715 01:28:56  version_check Connected to MySQL server
180715 01:28:56  version_check Executing a version check against the server...
180715 01:28:56  version_check Done.
180715 01:28:56 Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: not set
Using server version 5.7.22-22-log
xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 65536
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
180715 01:28:56 Added plugin 'keyring_vault.so' to load list.
180715 01:28:56 >> log scanned up to (2616858)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
...
180715 01:28:58 Finished backing up non-InnoDB tables and files
180715 01:28:58 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2616849'
xtrabackup: Stopping log copying thread.
.180715 01:28:58 >> log scanned up to (2616865)
180715 01:28:58 Executing UNLOCK TABLES
180715 01:28:58 All tables unlocked
180715 01:28:58 [00] Streaming ib_buffer_pool to
180715 01:28:58 [00]        ...done
180715 01:28:58 Backup created in directory '/root/backup/'
180715 01:28:58 [00] Streaming
180715 01:28:58 [00]        ...done
180715 01:28:58 [00] Streaming
180715 01:28:58 [00]        ...done
180715 01:28:58 Saving xtrabackup_keys.
xtrabackup: Transaction log of lsn (2616849) to (2616865) was copied.
Shutting down plugin 'keyring_vault'
180715 01:28:58 completed OK!

Restoring the backup on the Slave server

Extract the backup to a temporary location:

[root@mysql2 backup]# xbstream -x < ../backup.xbstream

And then prepare it with the following command. Notice that we are still using the same transition key we used when backing up the database in the master server.

[root@mysql2 ~]# xtrabackup --prepare --target-dir=backup/ --transition-key=NSu7kfUgcTTIY2ym7Qu6jnYOotOuMIeT
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1 --transition-key=*
xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1 --transition-key=* --prepare=1 --target-dir=backup/
xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
xtrabackup: cd to /root/backup/
xtrabackup: This target seems to be not prepared yet.
...
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2617384
180715 01:31:10 completed OK!

Configure keyring_vault.conf on slave

Create the keyring_vault.conf file with the following contents:

[root@mysql2 ~]# cat /var/lib/mysql-keyring/keyring_vault.conf
vault_url = https://192.168.0.114:8200
secret_mount_point = secret/dc1/slave
token = be515093-b1a8-c799-b237-8e04ea90ad7a
vault_ca = /etc/vault_ca/vault.pem

Notice that it uses the same token as the master server but has a different secret_mount_point. The same CA certificate will be used across all servers connecting to this Vault server.

Use –copy-back option to finalize backup restoration

Next use the --copy-back option to copy the files from the temporary backup location to the mysql data directory on the slave. Observe that during this phase XtraBackup generates a new master key, stores it in the Vault server and re-encrypts tablespace headers using this key.

[root@mysql2 ~]# xtrabackup --copy-back --target-dir=backup/ --transition-key=NSu7kfUgcTTIY2ym7Qu6jnYOotOuMIeT --generate-new-master-key --keyring-vault-config=/var/lib/mysql-keyring/keyring_vault.conf
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=mysqld-bin --server-id=2 --transition-key=* --generate-new-master-key=1
xtrabackup: recognized client arguments: --datadir=/var/lib/mysql --log_bin=mysqld-bin --server-id=2 --transition-key=* --generate-new-master-key=1 --copy-back=1 --target-dir=backup/
xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
180715 01:32:28 Loading xtrabackup_keys.
180715 01:32:28 Loading xtrabackup_keys.
180715 01:32:29 Generated new master key with ID 'be1ba51c-87c0-11e8-ac1c-00163e79c097-2'.
...
180715 01:32:29 [01] Encrypting /var/lib/mysql/mysql/plugin.ibd tablespace header with new master key.
180715 01:32:29 [01] Copying ./mysql/servers.ibd to /var/lib/mysql/mysql/servers.ibd
180715 01:32:29 [01]        ...done
180715 01:32:29 [01] Encrypting /var/lib/mysql/mysql/servers.ibd tablespace header with new master key.
180715 01:32:29 [01] Copying ./mysql/help_topic.ibd to /var/lib/mysql/mysql/help_topic.ibd
180715 01:32:29 [01]        ...done
180715 01:32:29 [01] Encrypting /var/lib/mysql/mysql/help_topic.ibd tablespace header with new master key.
180715 01:32:29 [01] Copying ./mysql/help_category.ibd to /var/lib/mysql/mysql/help_category.ibd
180715 01:32:29 [01]        ...done
180715 01:32:29 [01] Encrypting /var/lib/mysql/mysql/help_category.ibd tablespace header with new master key.
180715 01:32:29 [01] Copying ./mysql/help_relation.ibd to /var/lib/mysql/mysql/help_relation.ibd
180715 01:32:29 [01]        ...done
...
180715 01:32:30 [01] Encrypting /var/lib/mysql/encryptedschema/t1.ibd tablespace header with new master key.
180715 01:32:30 [01] Copying ./encryptedschema/db.opt to /var/lib/mysql/encryptedschema/db.opt
180715 01:32:30 [01]        ...done
...
180715 01:32:31 [01] Copying ./xtrabackup_binlog_pos_innodb to /var/lib/mysql/xtrabackup_binlog_pos_innodb
180715 01:32:31 [01]        ...done
180715 01:32:31 [01] Copying ./xtrabackup_master_key_id to /var/lib/mysql/xtrabackup_master_key_id
180715 01:32:31 [01]        ...done
180715 01:32:31 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
180715 01:32:31 [01]        ...done
Shutting down plugin 'keyring_vault'
180715 01:32:31 completed OK!

Once that’s done, change file/directory ownership to mysql.

[root@mysql2 ~]# chown -R mysql:mysql /var/lib/mysql/

Start the mysqld instance on the slave server configured similarly to the master configuration in the first part of this series.

early-plugin-load="keyring_vault=keyring_vault.so"
loose-keyring_vault_config="/var/lib/mysql-keyring/keyring_vault.conf"
encrypt_binlog=ON
innodb_encrypt_online_alter_logs=ON
innodb_encrypt_tables=ON
innodb_temp_tablespace_encrypt=ON
master_verify_checksum=ON
binlog_checksum=CRC32
log_bin=mysqld-bin
binlog_format=ROW
server-id=2
log-slave-updates

[root@mysql2 ~]# systemctl status mysqld
? mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: active (running) since Sun 2018-07-15 01:32:59 UTC; 6h ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1390 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 1372 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 1392 (mysqld)
   CGroup: /system.slice/mysqld.service
           ??1392 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Jul 15 01:32:58 mysql2 systemd[1]: Starting MySQL Server...
Jul 15 01:32:59 mysql2 systemd[1]: Started MySQL Server.

From here, you should be able to create the replication user on the master, and then configure slave replication based on the coordinates in the xtrabackup_binlog_info file. You can follow this section of the manual on starting replication.

For further reference, please read the manual related to Encrypted InnoDB tablespace backups.

Is validating your security strategy a concern?

Do you need to demonstrate that the security strategy that you have implemented for your databases is sufficient and appropriate? Perhaps you could benefit from a professional database audit? It could provide the reassurance that your organization needs.

The post Backing up Percona Server for MySQL with keyring_vault plugin enabled appeared first on Percona Database Performance Blog.

Sep
24
2018
--

Yubico’s new security keys now support FIDO2

Yubico, the company behind the popular Yubikey security keys, today announced the launch of its 5 Series keys. The company argues that these new keys, which start at $45, are the first multi-protocol securities keys that support the FIDO2 standard. With this, Yubico argues, the company will be able to replace password-based authentication, which is often a hassle and unsecure, with stronger hardware-based authentication.

“Innovation is core to all we do, from the launch of the original YubiKey ten years ago, to the concept of one authentication device across multiple services, and today as we are accelerating into the passwordless era,” said Stina Ehrensvard, the CEO and founder of Yubico in today’s announcement. “The YubiKey 5 Series can deliver single-factor, two-factor, or multi-factor secure login, supporting many different uses cases on different platforms for different verticals with a variety of authentication scenarios.”

The company made the announcement ahead of Microsoft’s Ignite conference this week, where Microsoft, too, is expected to make a number of security announcements around the future of passwords.

“Passwordless login brings a monumental change to how business users and consumers will securely log in to applications and services,” said Alex Simons, the corporate vice president of Microsoft’s Identity Division. “With FIDO2, Microsoft is working to remove the dependency on password-based logins, with support from devices like the YubiKey 5.”

For the most part, the new keys looks very much like the existing ones, but new to the series is the YubiKey 5 NFC, which combines supports all of the major security protocols over both USB and NFC — and the addition of NFC makes it a better option for those who want to use the same key on they desktops, laptops and mobile phones or tablets.

Supported protocols, in addition to FIDO2, include FIDO U2F, smart card (PIV), Yubico OTP, OpenPGP, OATH-TOTP, OATH-HOTP, and Challenge-Response.

The new keys will come in all of the standard Yubico form factors, including the large USB-A key with NFC support, as well as smaller versions and those for USB-C devices.

In its press release, Yubico stresses that its keys are manufactured and programmed in the USA and Sweden. The fact that it’s saying that is no accident, given that Google recently launched its own take on security keys (after years of recommending Yubikeys). Google’s keys, however, are being built by a Chinese company and while Google is building its own firmware for them, there are plenty of sceptics out there who aren’t exactly waiting for a key that was manufactured in China.

Sep
21
2018
--

Securing PostgreSQL as an Enterprise-Grade Environment

PostgreSQL enterprise-grade security

PostgreSQL® logoIn this post, we review how you can build an enhanced and secure PostgreSQL database environment using community software. We look at the features that are available in PostgreSQL that, when implemented, provide improved security.

As discussed in the introductory blog post of this series, in our webinar of October 10, 2018 we highlight important aspects an enterprise should consider for their PostgreSQL environments. This series of blogs addressing particular aspects of the enterprise-grade postgres environment complements the webinar. This post addresses security.

Authentication Layer

Client connections to PostgreSQL Server using host based authentication

PostgreSQL uses a host based authentication file (pg_hba.conf) to authorize incoming connections. This file contains entries with a combination of 5 categories: type, database, user, address, and method. A client is allowed to connect to a database only when the combination of username, database and the hostname of the client matches an entry in the pg_hba.conf file.

Consider the following entry in pg_hba.conf file :

# TYPE DATABASE USER ADDRESS METHOD
host percona pguser 192.168.0.14/32 md5

This entry says that connections from server 192.168.0.14 are only allowed from user pguser and only to the database percona. The method md5 forces password authentication.

The order of the entries in the pg_hba.conf file matters. If you have an entry that rejects connections from a given server followed by another that allows connections from it, the first entry in the order is considered. So, in this case, the connection is rejected.

This is the first layer of protection in authentication. If this criteria is not satisfied in this Access Control List (ACL), PostgreSQL will discard the request without considering even the server authentication.

Server Authentication

Historically, PostgreSQL uses MD5 digest as a password hash by default. The problem with pure MD5 hashing is that this function will always return the same hash for a given password, which renders a MD5 digest more susceptible for password cracking. Newer versions of PostgreSQL implement SCRAM Authentication (Simple Authentication and Secured Layer) that stores passwords in salted and iterated hash formats to strengthen PostgreSQL against offline attacks. SCRAM-SHA-256 support was introduced in PostgreSQL 10. What matters most in terms of “enterprise-grade” security is that PostgreSQL supports industry-standard authentication methods out of the box, like SSL certificates, PAM/LDAP, Kerberos, etc.

Authorization Layer

User management through roles and privileges

It is always recommended to implement segregation of users through roles and privileges. There may be several user accounts in your PostgreSQL server. Only a few of them may be application accounts while the rest are developers or admin accounts. In such cases, PostgreSQL allows you to create multiple roles. Those can be assigned with a set of privileges. Thus, instead of managing user privileges individually, standard roles can be maintained and the appropriate role from the list can be assigned to a user. Through roles, database access can be standardized, which helps in user management and avoids granting too much or too little privilege to a given user.

For example, we might have six roles:

app_read_write
app_read_only
dev_read_write
dev_read_only
admin_read_write
admin_read_only

Now, if you need to create a new dev user who can only have read access, grant one among the appropriate roles, such as dev_read_only:

GRANT dev_read_only to avi_im_developer;

Row level Security

Starting with version 9.5, PostgreSQL implements row level security, which can limit access to only a subset of records/rows in a table. Usually a user is granted a mix of SELECT, INSERT, DELETE and UPDATE privileges on a given table, which allows access to all records in the table. Through row level security, however, such privileges can be restricted to a subset of the records by means of a policy, which in turn can be  assigned to a role.

In the next example, we create an employee table and two manager accounts. We then enable row level security on the table and create a policy that allows the managers to only view/modify their own subordinates’ records:

CREATE TABLE scott.employee (id INT, first_name VARCHAR(20), last_name VARCHAR(20), manager VARCHAR(20));
INSERT INTO scott.employee VALUES (1,'avinash','vallarapu','carina');
INSERT INTO scott.employee VALUES (2,'jobin','augustine','stuart');
INSERT INTO scott.employee VALUES (3,'fernando','laudares','carina');
CREATE USER carina WITH ENCRYPTED PASSWORD 'carina';
CREATE USER stuart WITH ENCRYPTED PASSWORD 'stuart';
CREATE ROLE managers;
GRANT managers TO carina, stuart;
GRANT SELECT, INSERT, UPDATE, DELETE ON scott.employee TO managers;
GRANT USAGE ON SCHEMA scott TO managers;
ALTER TABLE scott.employee ENABLE ROW LEVEL SECURITY;
CREATE POLICY employee_managers ON scott.employee TO managers USING (manager = current_user);

In the log we can see that only certain records are visible to each manager:

$ psql -d percona -U carina
psql (10.5)
Type "help" for help.
percona=> select * from scott.employee ;
id | first_name | last_name | manager
----+------------+-----------+---------
 1 | avinash    | vallarapu | carina
 3 | fernando   | laudares | carina
(2 rows)
$ psql -d percona -U stuart
psql (10.5)
Type "help" for help.
percona=> select * from scott.employee ;
id | first_name | last_name | manager
----+------------+-----------+---------
 2 | jobin      | augustine | stuart
(1 row)

You can read more about row level security in the manual page.

Data Security

1. Encryption of data over the wire using SSL

PostgreSQL allows you to use SSL to enable encryption of data in motion. In addition, you may enable certification based authentication to ensure that the communication is happening between trusted parties. SSL is implemented by OpenSSL and thus it requires the OpenSSL package to be installed in your PostgreSQL server and PostgreSQL to be built –with-openssl support.

The following entry in a pg_hba.conf file says that connections to any database and from any user are allowed from server 192.68.0.13 as long as the communication is encrypted over SSL. Also, the connection is only established when a valid client certificate is provided:

# TYPE DATABASE USER ADDRESS METHOD
hostssl all all 192.168.0.13/32 md5

Optionally, you may also use Client Certificate Authentication using the following method:

# TYPE DATABASE USER ADDRESS METHOD
hostssl all all 192.168.0.13/32 cert clientcert=1

2. Encryption at Rest – pgcrypto

The pgcrypto module provides cryptographic functions for PostgreSQL, allowing certain fields to be stored encrypted. pgcrypto implements PGP encryption, which is part of the OpenPGP (RFC 4880) standard. It supports both symmetric-key and public-key encryption. Besides the advanced features offered by PGP for encryption, pgcrypto also offers functions for running simple encryption based on ciphers. These functions only run a cipher over data.

Accounting and Auditing

Logging in PostgreSQL

PostgreSQL allows you to log either all of the statements or a few statements based on parameter settings. You can log all the DDLs or DMLs or any statement running for more than a certain duration to the log file when logging_collector is enabled. To avoid write overload to the data directory, you may also move your log_directory to a different location. Here’s a few important parameters you should review when logging activities in your PostgreSQL server:

log_connections
log_disconnections
log_lock_waits
log_statement
log_min_duration_statement

Please note that detailed logging takes additional disk space and may impose an important overhead in terms of write IO depending on the activity in your PostgreSQL server. You should be careful when enabling logging and should only do so after understanding the overhead and performance degradation it may cause to your workload.

Auditing – pgaudit and set_user

Some essential auditing features in PostgreSQL are implemented as extensions, which can be enabled at will on highly secured environments with regulatory requirements.

pgaudit helps to audit the activities happening in the database. If any unauthorized user has intentionally obfuscated the DDL or DML, the statement the user has passed and the sub-statement that was actually executed in the database will be logged in the PostgreSQL log file.

set_user

  provides a method of privilege escalations. If properly implemented, it provides the highest level of auditing, which allows the monitoring of even SUPERUSER actions.

You can read more about pgaudit here.

Security Bug Fixes

PostgreSQL Global Development Group (PGDG) considers security bugs seriously. Any security vulnerabilities can be reported directly to security@postgresql.org. The list of security issues fixed for all the supported PostgreSQL versions can be found here. Security fixes to PostgreSQL are made available through minor version upgrades. This is the main reason why it is advised to always maintain PostgreSQL servers upgraded to the latest minor version.

If you liked this post…

Please join Percona’s PostgreSQL Support Technical Lead,  Avinash Vallarapu; Senior Support Engineer, Fernando Laudares; and Senior Support Engineer, Jobin Augustine, on Wednesday, October 10, 2018 at 7:00 AM PDT (UTC-7) / 10:00 AM EDT (UTC-4), as they demonstrate an enterprise-grade PostgreSQL® environment built using a combination of open source tools and extensions.

Register Now

The post Securing PostgreSQL as an Enterprise-Grade Environment appeared first on Percona Database Performance Blog.

Sep
17
2018
--

Using the keyring_vault Plugin with Percona Server for MySQL 5.7

keyring_vault store database encryption keys

keyring_vault store database encryption keysThis is the first of a two-part series on using the keyring_vault plugin with Percona Server for MySQL 5.7. The second part will walk you through on how to use Percona Xtrabackup to backup from this instance and restore to another server and set it up as a slave with keyring_vault plugin.

What is the keyring_vault plugin?

The keyring_vault is a plugin that allows the database to interface with a Hashicorp Vault server to store and secure encryption keys. The Vault server then acts as a centralized encryption key management solution which is critical for security and for compliance with various security standards.

Configuring Vault

Create SSL certificates to be used by Vault. You can use the sample ssl.conf template below to generate the necessary files.

[root@vault1 ~]# cat /etc/sslkeys/ssl.conf
[req]
distinguished_name = req_distinguished_name
x509_extensions = v3_req
prompt = no
[req_distinguished_name]
C = US
ST = NC
L =  R
O = Percona
CN = *
[v3_req]
subjectKeyIdentifier = hash
authorityKeyIdentifier = keyid,issuer
basicConstraints = CA:TRUE
subjectAltName = @alt_names
[alt_names]
IP = 192.168.0.114

Then run the two commands below to generated the cert and key files and the certificate chain:

$ openssl req -config ssl.conf -x509 -days 365 -batch -nodes -newkey rsa:2048 -keyout vault.key -out vault.crt
$ cat vault.key vault.crt > vault.pem

Once the SSL certificates are created start Vault with the sample configuration below. Take note that you should follow the suggested best practices when deploying Vault in production, this example is to get us by with a simple working setup.

[root@vault1 ~]# cat /etc/vault.hcl
listener "tcp" {
address = "192.168.0.114:8200"
tls_cert_file="/etc/sslkeys/vault.crt"
tls_key_file="/etc/sslkeys/vault.key"
}
storage "file" {
path = "/var/lib/vault"
}

Assuming Vault started up fine and you are able to unseal Vault, the next step is to create the policy file. For more details on initializing and unsealing Vault please read the manual here.

[root@vault1 ~]# cat /etc/vault/policy/dc1.hcl
path "secret/*" {
capabilities = ["list"]
}
path "secret/dc1/*" {
capabilities = ["create", "read", "delete", "update", "list"]
}

Create a Vault policy named dc1-secrets using the dc1.hcl file like this:

[root@vault1 ~]# vault policy write dc1-secrets /etc/vault/policy/dc1.hcl -ca-cert=/etc/sslkeys/vault.pem
Success! Uploaded policy: dc1-secrets

Next, create a token associated with the newly created policy:

[root@vault1 ~]# vault token create -policy=dc1-secrets -ca-cert=/etc/sslkeys/vault.pem > dc1-token
[root@vault1 ~]# cat dc1-token
Key                  Value
---                  -----
token                be515093-b1a8-c799-b237-8e04ea90ad7a
token_accessor       4c1ba5c5-3fed-e9bb-d230-5bf1392e2d7e
token_duration       8760h
token_renewable      true
token_policies       ["dc1-secrets" "default"]
identity_policies    []
policies             ["dc1-secrets" "default"]

Setting up MySQL

The following instructions should work starting from Percona Server for MySQL 5.7.20-18 and through later versions.

Configure my.cnf with the following variables:

early-plugin-load="keyring_vault=keyring_vault.so"
loose-keyring_vault_config="/var/lib/mysql-keyring/keyring_vault.conf"
encrypt_binlog=ON
innodb_encrypt_online_alter_logs=ON
innodb_encrypt_tables=ON
innodb_temp_tablespace_encrypt=ON
master_verify_checksum=ON
binlog_checksum=CRC32
log_bin=mysqld-bin
binlog_format=ROW
server-id=1
log-slave-updates

Create the keyring_vault.conf file in the path above with the following contents:

[root@mysql1 ~]# cat /var/lib/mysql-keyring/keyring_vault.conf
vault_url = https://192.168.0.114:8200
secret_mount_point = secret/dc1/master
token = be515093-b1a8-c799-b237-8e04ea90ad7a
vault_ca = /etc/vault_ca/vault.pem

Here we are using the vault.pem file generated by combining the vault.crt and vault.key files. Observe that our secret_mount_point is secret/dc1/master. We want to make sure that this mount point is unique across all servers, this is in fact advised in the manual here.

Ensure that the CA certificate is owned by mysql user:

[root@mysql1 ~]# ls -la /etc/vault_ca/
total 24
drwxr-xr-x  2 mysql mysql   41 Jul 14 11:39 .
drwxr-xr-x 63 root  root  4096 Jul 14 13:17 ..
-rw-------  1 mysql mysql 1139 Jul 14 11:39 vault.pem

Initialize the MySQL data directory on the Master:

[root@mysql1 ~]# mysqld --initialize-insecure --datadir=/var/lib/mysql --user=mysql

For production systems we do not recommend using --initialize-insecure option, this is just to skip additional steps in this tutorial.

Finally, start mysqld instance and then test the setup by creating an encrypted table.

[root@mysql1 ~]# systemctl status mysqld
? mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
Active: active (running) since Sat 2018-07-14 23:53:16 UTC; 2s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 1401 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 1383 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 1403 (mysqld)
CGroup: /system.slice/mysqld.service
??1403 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Jul 14 23:53:16 mysql1 systemd[1]: Starting MySQL Server...
Jul 14 23:53:16 mysql1 systemd[1]: Started MySQL Server.

At this point you should have Percona Server for MySQL instance with tablespace encryption using Vault.

Researching database security?

You might also enjoy this pre-recorded webinar securing your database servers from external attacks presented by my colleague Colin Charles.

The post Using the keyring_vault Plugin with Percona Server for MySQL 5.7 appeared first on Percona Database Performance Blog.

Sep
14
2018
--

Encryption of the InnoDB System Tablespace and Parallel Doublewrite Buffer

encryption of InnoDB tablespace parallel doublewrite buffer

encryption of InnoDB tablespace parallel doublewrite bufferIn my last post I compared data at-rest encryption features available for MySQL and MariaDB. As noted at the time, some of the features available for Percona Server for MySQL were in development, and the latest version (5.7.23) sees two of them released as ALPHA quality.

Encrypting the InnoDB system tablespace

The first of the new features is InnoDB system tablespace encryption via innodb_sys_tablespace_encrypt, which would provide encryption of the following data:

  • the change buffer, which caches changes to secondary index pages as a result of DML operations for pages that are not in the InnoDB buffer pool
  • The undo logs if they have not been configured to be stored in separate undo tablespaces
  • data from any tables that exist in the main tablespace, which occurs when innodb_file_per_table is disabled

There are some related changes on the horizon that would allow this to be applied to an existing instance. However, for now this is only available for new instances as it can only be applied during bootstrap. This means that it would require a logical restore of your data to use it with an existing cluster–I should restate that this is an ALPHA feature and not production-ready.

There are some extra points to note about this new variable:

  • an instance with an encrypted tablespace cannot be downgraded to use a version prior to 5.7.23, due to the inability to read the tablespace
  • as noted, it is not currently possible to convert the tablespace between encrypted and unencrypted states, or vice versa
  • the key for the system tablespace can be manually rotated using ALTER INSTANCE ROTATE INNODB MASTER KEY as per any other tablespace

Encrypting the parallel doublewrite buffer

To complement the encryption of the system tablespace, it is also possible to encrypt the parallel doublewrite buffer using innodb_parallel_dblwr_encrypt, a feature unique to Percona Server for MySQL.  This means that any data for an encrypted tablespace is also only written in an encrypted form in the parallel doublewrite buffer; unencrypted tablespace data remains in plaintext. Unlike innodb_sys_tablespace_encrypt, you are able to set innodb_parallel_dblwr_encrypt dynamically on an existing instance.

There are more encryption features planned–or already in development–for Percona Server for MySQL so watch this space!

The post Encryption of the InnoDB System Tablespace and Parallel Doublewrite Buffer appeared first on Percona Database Performance Blog.

Sep
10
2018
--

Using ProxySQL to connect to IPv6-only databases over IPv4

connect to ipv6 database from ipv4 application using proxysql

connect to ipv6 database from ipv4 application using proxysqlIt’s 2018. Maybe now is the time to start migrating your network to IPv6, and your database infrastructure is a great place to start. Unfortunately, many legacy applications don’t offer the option to connect to MySQL directly over IPv6 (sometimes even if passing a hostname). We can work around this by using ProxySQL’s IPv6 support which was added in version 1.3. This will allow us to proxy incoming IPv4 connections to IPv6-only database servers.

Note that by default ProxySQL only listens on IPv4. We don’t recommended changing that until this bug is resolved. The bug causes ProxySQL to segfault frequently if listening on IPv6.

In this example I’ll use centos7-pxc57-1 as my database server. It’s running Percona XtraDB Cluster (PXC) 5.7 on CentOS 7,  which is only accessible over IPv6. This is one node of a three node cluster, but l treat this one node as a standalone server for this example.  One node of a synchronous cluster can be thought of as equivalent to the entire cluster, and vice-versa. Using the PXC plugin for ProxySQL to split reads from writes is the subject of a future blog post.

The application server, centos7-app01, would be running the hypothetical legacy application.

Note: We use default passwords throughout this example. You should always change the default passwords.

We have changed the IPv6 address in these examples. Any resemblance to real IPv6 addresses, living or dead, is purely coincidental.

  • 2a01:5f8:261:748c::74 is the IPv6 address of the ProxySQL server
  • 2a01:5f8:261:748c::71 is the Percona XtraDB node

Step 1: Install ProxySQL for your distribution

Packages are available here but in this case I’m going to use the version provided by the Percona yum repository:

[...]
Installed:
proxysql.x86_64 0:1.4.9-1.1.el7
Complete!

Step 2: Configure ProxySQL to listen on IPv4 TCP port 3306 by editing /etc/proxysql.cnf and starting it

[root@centos7-app1 ~]# vim /etc/proxysql.cnf
[root@centos7-app1 ~]# grep interfaces /etc/proxysql.cnf
interfaces="127.0.0.1:3306"
[root@centos7-app1 ~]# systemctl start proxysql

Step 3: Configure ACLs on the destination database server to allow ProxySQL to connect over IPv6

mysql> GRANT SELECT on sys.* to 'monitor'@'2a01:5f8:261:748c::74' IDENTIFIED BY 'monitor';
Query OK, 0 rows affected, 1 warning (0.25 sec)
mysql> GRANT ALL ON legacyapp.* TO 'legacyappuser'@'2a01:5f8:261:748c::74' IDENTIFIED BY 'super_secure_password';
Query OK, 0 rows affected, 1 warning (0.25 sec)

Step 4: Add the IPv6 address of the destination server to ProxySQL and add users

We need to configure the IPv6 server as a mysql_server inside ProxySQL. We also need to add a user to ProxySQL as it will reuse these credentials when connecting to the backend server. We’ll do this by connecting to the admin interface of ProxySQL on port 6032:

[root@centos7-app1 ~]# mysql -h127.0.0.1 -P6032 -uadmin -padmin
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.
Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'2a01:5f8:261:748c::71',3306);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('legacyappuser', 'super_secure_password', 1);
Query OK, 1 row affected (0.00 sec)
mysql> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
mysql> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.27 sec)
mysql> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)
mysql> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.30 sec)
mysql> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
mysql> SAVE MYSQL VARIABLES TO DISK;
Query OK, 95 rows affected (0.12 sec)

Step 5: Configure your application to connect to ProxySQL over IPv4 on localhost4 (IPv4 localhost)

This is application specific and so not shown here, but I’d configure my application to use localhost4 as this is in /etc/hosts by default and points to 127.0.0.1 and not ::1

Step 6: Verify

As I don’t have the application here, I’ll verify with mysql-client. Remember that ProxySQL is listening on 127.0.0.1 port 3306, so we connect via ProxySQL on IPv4 (the usage of 127.0.0.1 rather than a hostname is just to show this explicitly):

[root@centos7-app1 ~]# mysql -h127.0.0.1 -ulegacyappuser -psuper_secure_password
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> SELECT host FROM information_schema.processlist WHERE ID=connection_id();
+-----------------------------+
| host                        |
+-----------------------------+
| 2a01:5f8:261:748c::74:57546 |
+-----------------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE legacyapp.legacy_test_table(id int);
Query OK, 0 rows affected (0.83 sec)

The query above shows the remote host (from MySQL’s point of view) for the current connection. As you can see, MySQL sees this connection established over IPv6. So to recap, we connected to MySQL on an IPv4 IP address (127.0.0.1) and were successfully proxied to a backend IPv6 server.

The post Using ProxySQL to connect to IPv6-only databases over IPv4 appeared first on Percona Database Performance Blog.

Aug
28
2018
--

Very Good Security makes data ‘unhackable’ with $8.5M from Andreessen

“You can’t hack what isn’t there,” Very Good Security co-founder Mahmoud Abdelkader tells me. His startup assumes the liability of storing sensitive data for other companies, substituting dummy credit card or Social Security numbers for the real ones. Then when the data needs to be moved or operated on, VGS injects the original info without clients having to change their code.

It’s essentially a data bank that allows businesses to stop storing confidential info under their unsecured mattress. Or you could think of it as Amazon Web Services for data instead of servers. Given all the high-profile breaches of late, it’s clear that many companies can’t be trusted to house sensitive data. Andreessen Horowitz is betting that they’d rather leave it to an expert.

That’s why the famous venture firm is leading an $8.5 million Series A for VGS, and its partner Alex Rampell is joining the board. The round also includes NYCA, Vertex Ventures, Slow Ventures and PayPal mafioso Max Levchin. The cash builds on VGS’ $1.4 million seed round, and will pay for its first big marketing initiative and more salespeople.

“Hey! Stop doing this yourself!,” Abdelkader asserts. “Put it on VGS and we’ll let you operate on your data as if you possess it with none of the liability.” While no data is ever 100 percent unhackable, putting it in VGS’ meticulously secured vaults means clients don’t have to become security geniuses themselves and instead can focus on what’s unique to their business.

“Privacy is a part of the UN Declaration of Human Rights. We should be able to build innovative applications without sacrificing our privacy and security,” says Abdelkader. He got his start in the industry by reverse-engineering games like StarCraft to build cheats and trainer software. But after studying discrete mathematics, cryptology and number theory, he craved a headier challenge.

Abdelkader co-founded Y Combinator-backed payment system Balanced in 2010, which also raised cash from Andreessen. But out-muscled by Stripe, Balanced shut down in 2015. While transitioning customers over to fellow YC alumni Stripe, Balanced received interest from other companies wanting it to store their data so they could be PCI-compliant.

Very Good Security co-founder and CEO Mahmoud Abdelkader

Now Abdelkader and his VP from Balanced, Marshall Jones, have returned with VGS to sell that as a service. It’s targeting startups that handle data like payment card information, Social Security numbers and medical info, though eventually it could invade the larger enterprise market. It can quickly help these clients achieve compliance certifications for PCI, SOC2, EI3PA, HIPAA and other standards.

VGS’ innovation comes in replacing this data with “format preserving aliases” that are privacy safe. “Your app code doesn’t know the difference between this and actually sensitive data,” Abdelkader explains. In 30 minutes of integration, apps can be reworked to route traffic through VGS without ever talking to a salesperson. VGS locks up the real strings and sends the aliases to you instead, then intercepts those aliases and swaps them with the originals when necessary.

“We don’t actually see your data that you vault on VGS,” Abdelkader tells me. “It’s basically modeled after prison. The valuables are stored in isolation.” That means a business’ differentiator is their business logic, not the way they store data.

For example, fintech startup LendUp works with VGS to issue virtual credit card numbers that are replaced with fake numbers in LendUp’s databases. That way if it’s hacked, users’ don’t get their cards stolen. But when those card numbers are sent to a processor to actually make a payment, the real card numbers are subbed in last-minute.

VGS charges per data record and operation, with the first 500 records and 100,000 sensitive API calls free; $20 a month gets clients double that, and then they pay 4 cent per record and 2 cents per operation. VGS provides access to insurance too, working with a variety of underwriters. It starts with $1 million policies that can be much larger for Fortune 500s and other big companies, which might want $20 million per incident.

Obviously, VGS has to be obsessive about its own security. A breach of its vaults could kill its brand. “I don’t sleep. I worry I’ll miss something. Are we a giant honey pot?,” Abdelkader wonders. “We’ve invested a significant amount of our money into 24/7 monitoring for intrusions.”

Beyond the threat of hackers, VGS also has to battle with others picking away at part of its stack or trying to compete with the whole, like TokenEx, HP’s Voltage, Thales’ Vormetric, Oracle and more. But it’s do-it-yourself security that’s the status quo and what VGS is really trying to disrupt.

But VGS has a big accruing advantage. Each time it works with a clients’ partners like Experian or TransUnion for a company working with credit checks, it already has a relationship with them the next time another clients has to connect with these partners. Abdelkader hopes that, “Effectively, we become a standard of data security and privacy. All the institutions will just say ‘why don’t you use VGS?’”

That standard only works if it’s constantly evolving to win the cat-and-mouse game versus attackers. While a company is worrying about the particular value it adds to the world, these intelligent human adversaries can find a weak link in their security — costing them a fortune and ruining their relationships. “I’m selling trust,” Abdelkader concludes. That peace of mind is often worth the price.

Aug
23
2018
--

Comparing Data At-Rest Encryption Features for MariaDB, MySQL and Percona Server for MySQL

Encryption at rest MariaDB MySQL Percona Server

Encryption at rest MariaDB MySQL Percona ServerProtecting the data stored in your database may have been at the top of your priorities recently, especially with the changes that were introduced earlier this year with GDPR.

There are a number of ways to protect this data, which until not so long ago would have meant either using an encrypted filesystem (e.g. LUKS), or encrypting the data before it is stored in the database (e.g. AES_ENCRYPT or other abstraction within the application). A few years ago, the options started to change, as Alexander Rubin discussed in MySQL Data at Rest Encryption, and now MariaDB®, MySQL®, and Percona Server for MySQL all support encryption at-rest. However, the options that you have—and, indeed, the variable names—vary depending upon which database version you are using.

In this blog post we will take a look at what constitutes the maximum level of at-rest encryption that can be achieved with each of the latest major GA releases from each provider. To allow a fair comparison across the three, we will focus on the file-based key management; keyring_file plugin for MySQL and Percona Server for MySQL along with file_key_management plugin for MariaDB.

MariaDB 10.3

The MariaDB team take the credit for leading the way with at-rest encryption, as most of their features have been present since the 10.1 release (most notably the beta release of 10.1.3 in March 2015). Google donated the tablespace encryption, and eperi donated per-table encryption and key identifier support.

The current feature set for MariaDB 10.3 comprises of the following variables:

Maximising at-rest encryption with MariaDB 10.3

Using the following configuration would give you maximum at-rest encryption with MariaDB 10.3:

plugin_load_add = file_key_management
file_key_management_filename = /etc/mysql/keys.enc
file_key_management_filekey = FILE:/etc/mysql/.key
file_key_management_encryption_algorithm = aes_cbc
innodb_encrypt_log = ON
innodb_encrypt_tables = FORCE
Innodb_encrypt_threads = 4
encrypt_binlog = ON
encrypt_tmp_disk_tables = ON
encrypt_tmp_files = ON
aria_encrypt_tables = ON

This configuration would provide the following at-rest protection:

  • automatic and enforced InnoDB tablespace encryption
  • automatic encryption of existing tables that have not been marked with
    ENCRYPTED=NO
  • 4 parallel encryption threads
  • encryption of temporary files and tables
  • encryption of Aria tables
  • binary log encryption
  • an encrypted file that contains the main encryption key

You can read more about preparing the keys, as well as the other key management plugins in the Encryption Key Management docs.

There is an existing bug related to encrypt_tmp_files (MDEV-14884), which causes the use of

mysqld --help --verbose

 to fail, which if you are using the official MariaDB Docker container for 10.3 will cause you to be unable to keep mysqld up and running. Messages similar to these would be visible in the Docker logs:

ERROR: mysqld failed while attempting to check config
command was: "mysqld --verbose --help --log-bin-index=/tmp/tmp.HDiERM4SPx"
2018-08-15 13:38:15 0 [Note] Plugin 'FEEDBACK' is disabled.
2018-08-15 13:38:15 0 [ERROR] Failed to enable encryption of temporary files
2018-08-15 13:38:15 0 [ERROR] Aborting

N.B. you should be aware of the limitations for the implementation, most notably log tables and files are not encrypted and may contain data along with any query text.

One of the key features supported by MariaDB that is not yet supported by the other providers is the automatic, parallel encryption of tables that will occur when simply enabling

innodb_encrypt_tables

 . This avoids the need to mark the existing tables for encryption using

ENCRYPTED=YES

 , although at the same time it also does not automatically add the comment and so you would not see this information. Instead, to check for encrypted InnoDB tables in MariaDB you should check

information_schema.INNODB_TABLESPACES_ENCRYPTION

 , an example query being:

mysql> SELECT SUBSTRING_INDEX(name, '/', 1) AS db_name,
   ->   SUBSTRING_INDEX(name, '/', -1) AS db_table,
   ->   COALESCE(ENCRYPTION_SCHEME, 0) AS encrypted
   -> FROM information_schema.INNODB_SYS_TABLESPACES
   -> LEFT JOIN INNODB_TABLESPACES_ENCRYPTION USING(name);
+---------+----------------------+-----------+
| db_name | db_table             | encrypted |
+---------+----------------------+-----------+
| mysql   | innodb_table_stats   |      1    |
| mysql   | innodb_index_stats   |      0    |
| mysql   | transaction_registry |      0    |
| mysql   | gtid_slave_pos       |      0    |
+---------+----------------------+-----------+

As can be inferred from this query, the system tables in MariaDB 10.3 are still predominantly MyISAM and as such cannot be encrypted.

MySQL

Whilst the enterprise version of MySQL has support for a number of data at-rest encryption features as of 5.7, most of them are not available to the community edition. The latest major release of the community version sees the main feature set comprise of:

The enterprise edition adds the following extra support:

Maximising at-rest encryption with MySQL 8.0

Using the following configuration would give you maximum at-rest encryption with MySQL 8.0:

early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql/keyring
innodb_redo_log_encrypt=ON
innodb_undo_log_encrypt=ON

This configuration would provide the following at-rest protection:

  • optional InnoDB tablespace encryption
  • redo and undo log encryption

You would need to create new, or alter existing tables with the

ENCRYPTION=Y

 option, which would then be visible by examining

information_schema.INNODB_TABLESPACES

 , an example query being:

mysql> SELECT TABLE_SCHEMA AS db_name,
   ->    TABLE_NAME AS db_table,
   ->    CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%' AS encrypted
   -> FROM information_schema.INNODB_TABLESPACES ts
   -> INNER JOIN information_schema.TABLES t ON t.TABLE_SCHEMA = SUBSTRING_INDEX(ts.name, '/', 1)
   ->                                        AND t.TABLE_NAME = SUBSTRING_INDEX(ts.name, '/', -1);
+---------+-----------------+-----------+
| db_name | db_table        | encrypted |
+---------+-----------------+-----------+
| sys     | sys_config      |     1     |
+---------+-----------------+-----------+

N.B. You are able to encrypt the tablespaces in 5.7, in which case you should use

information_schema.INNODB_SYS_TABLESPACES

 as the internal system views on the data dictionary were renamed (InnoDB Changes).

Unfortunately, whilst all of the tables in the mysql schema use the InnoDB engine (except for the log tables), you cannot encrypt them and instead get the following error:

ERROR 3183 (HY000): This tablespace can't be encrypted.

Interestingly, you are led to believe that you can indeed encrypt the

general_log

 and

slow_log

 tables, but this is in fact a bug (#91791).

Percona Server for MySQL

Last, but not least we have Percona Server for MySQL, which, whilst not completely matching MariaDB for features, is getting very close. As we shall see shortly, it does in fact have some interesting differences to both MySQL and MariaDB.

The current feature set for 5.7, which does indeed exceed the features provided by MySQL 5.7 and for the most part 8.0, is as follows:

Maximising at-rest encryption with Percona Server for MySQL 5.7

Using the following configuration would give you maximum at-rest encryption with Percona Server 5.7:

early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring
innodb_temp_tablespace_encrypt=ON
innodb_encrypt_online_alter_logs=ON
innodb_encrypt_tables=FORCE
encrypt_binlog=ON
encrypt_tmp_files=

This configuration would provide the following at-rest protection:

  • automatic and enforced InnoDB tablespace encryption
  • encryption of temporary files and tables
  • binary log encryption
  • encryption when performing online DDL

There are some additional features that are due for release in the near future:

  • Encryption of the doublewrite buffer
  • Automatic key rotation
  • Undo log and redo log encryption
  • InnoDB system tablespace encryption
  • InnoDB tablespace and redo log scrubbing
  • Amazon KMS keyring plugin

Just like MySQL, encryption of any existing tables needs to be specified via

ENCRYPTION=Y

 via an

ALTER

, however new tables are automatically encrypted. Another difference is that in order to check which tables are encrypted you can should the flag set against the tablespace in

information_schema.INNODB_SYS_TABLESPACES

, an example query being:

mysql> SELECT SUBSTRING_INDEX(name, '/', 1) AS db_name,
   ->    SUBSTRING_INDEX(name, '/', -1) AS db_table,
   ->    (flag & 8192) != 0 AS encrypted
   -> FROM information_schema.INNODB_SYS_TABLESPACES;
+---------+---------------------------+-----------+
| db_name | db_table                  | encrypted |
+---------+---------------------------+-----------+
| sys     | sys_config                |      1    |
| mysql   | engine_cost               |      1    |
| mysql   | help_category             |      1    |
| mysql   | help_keyword              |      1    |
| mysql   | help_relation             |      1    |
| mysql   | help_topic                |      1    |
| mysql   | innodb_index_stats        |      1    |
| mysql   | innodb_table_stats        |      1    |
| mysql   | plugin                    |      1    |
| mysql   | servers                   |      1    |
| mysql   | server_cost               |      1    |
| mysql   | slave_master_info         |      1    |
| mysql   | slave_relay_log_info      |      1    |
| mysql   | slave_worker_info         |      1    |
| mysql   | time_zone                 |      1    |
| mysql   | time_zone_leap_second     |      1    |
| mysql   | time_zone_name            |      1    |
| mysql   | time_zone_transition      |      1    |
| mysql   | time_zone_transition_type |      1    |
| mysql   | gtid_executed             |      0    |
+---------+---------------------------+-----------+

Here you will see something interesting! We are able to encrypt most of the system tables, including two that are of significance, as they can contain plain text passwords:

+---------+-------------------+-----------+
| db_name | db_table          | encrypted |
+---------+-------------------+-----------+
| mysql   | servers           |      1    |
| mysql   | slave_master_info |      1    |
+---------+-------------------+-----------+

In addition to the above, Percona Server for MySQL also supports using the opensource HashiCorp Vault to host the keyring decryption information using the keyring_vault plugin; utilizing this setup (provided Vault is not on the same device as your mysql service, and is configured correctly) gains you an additional layer of security.

You may also be interested in my earlier blog post on using Vault with MySQL, showing you how to store your credentials in a central location and use them to access your database, including the setup and configuration of Vault with Let’s Encrypt certificates.

Summary

There are significant differences both in terms of features and indeed variable names, but all of them are able to provide encryption of the InnoDB tablespaces that will be containing your persistent, sensitive data. The temporary tablespaces, InnoDB logs and temporary files contain transient data, so whilst they should ideally be encrypted, only a small section of data would exist in them for a finite amount of time which is less of a risk, albeit a risk nonetheless.

Here are the highlights:

MariaDB 10.3 MySQL 8.0 Percona Server 5.7
encrypted InnoDB data Y Y Y
encrypted non-InnoDB data Aria-only N N
encrypted InnoDB logs Y Y TBA
automatic encryption Y N Y
enforced encryption Y N Y
automatic key rotation Y N TBA
encrypted binary logs Y N Y
encrypted online DDL ? N Y
encrypted keyring Y Enterprise-only N
mysql.slave_master_info N N Y
mysql.servers N N Y
Hashicorp Vault N N Y
AWS KMS Y Enterprise-only TBA

 

Extra reading:

 

The post Comparing Data At-Rest Encryption Features for MariaDB, MySQL and Percona Server for MySQL appeared first on Percona Database Performance Blog.

Aug
23
2018
--

Wickr teams up with Psiphon to ensure your packets arrive safely no matter where you are

Encrypted collaboration app Wickr has added a feather to its cap with a partnership with Psiphon, provider of smart VPN tools. Wickr will use Psiphon’s tech to guarantee your packets get where they need to go regardless of whether you’re at home, at a cafe with bad Wi-Fi or at a cafe with bad Wi-Fi in China.

The idea is that the user shouldn’t have to be auditing their own connection to be sure their apps will work properly. That can be a matter of safety, such as a poorly secured access point; connectivity, such as one where certain ports or apps are inoperable; or censorship, like requesting data from a service banned in the country you’re visiting.

Wickr already encrypts all your traffic, so there are no worries on that account, but if the connection you’re using were to block video calls or certain traffic patterns, there’s not much the company can do about that.

Psiphon, however, is in the business of circumventing deliberate or accidental blockages with a suite of tools that analyze the network and attempt to find a way to patch you through. Whether that’s anonymizing your traffic, bouncing it off non-blocked servers, doing automatic port forwarding or some other method, the idea is the packets get through one way or another.

There’s a cost in latency and throughput, of course, but while that may matter for online gaming or video streaming, it’s far less important for something like uploading an image, chatting with colleagues and the other functions Wickr provides. At all events you can turn the feature on or off at will.

There will be a monetary cost too, of course, in the form of premiums added to paid plans. Enterprise customers will be the first to receive the Psiphon-powered traffic handling, today in fact, and the feature will then trickle its way down to other paid users and free users over the next few weeks.

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