Apr
23
2017
--

Percona XtraDB Cluster: “dh key too small” error during an SST using SSL

dh key too small

dh key too smallIf you’ve tried to use SSL in Percona XtraDB Cluster and saw an error in the logs like SSL3_CHECK_CERT_AND_ALGORITHM:dh key too small, we’ve implemented some changes in Percona XtraDB Cluster 5.6.34 and 5.7.16 that get rid of these errors.

Some background

dh key too small refers to the Diffie-Hellman parameters used by the SSL code that are shorter than recommended.

Due to the Logjam vulnerability (https://weakdh.org/), the required key-lengths for the Diffie-Hellman parameters were changed from 512 bits to 2048 bits. Unfortunately, older versions of OpenSSL/socat still use 512 bits (and thus caused the error to appear).

Changes made to Percona XtraDB Cluster

Since versions of socat greater than 1.7.3 now use 2048 bits for the Diffie-Hellman parameters, we only do extra work for the older versions of socat (less than 1.7.3). The SST code now:

  1. Looks for a file with the DH params
    1. Uses the “ssl_dhparams” option in the [sst] section if it exists
    2. Looks for a “dhparams.pem” file in the datadir
  2. If the file is specified and exists, uses that file as a source for the DH parameters
  3. If the file does not exist, creates a dhparams.pem file in the datadir

Generating the dhparams yourself

Unfortunately, the time it can take several minutes to create the dhparams file. We recommend that the dhparams.pem be created prior to starting the SST.

openssl dhparam -out path/to/datadir/dhparams.pem 2048

Apr
21
2017
--

Percona Server for MySQL in Docker Swarm with Secrets

This quick post demonstrates using Percona Server for MySQL in Docker Swarm with some new authentication provisioning practices.

Some small changes to the startup script for the Percona-Server container image allows us to specify a file that contains password values to set as our root user’s secret. “Why do we need this functionality,” I hear you cry? When we use an environment variable, it’s not terribly hard to locate the value to which someone has set as their database root password. Environment variables are not well suited for sensitive data. We preach against leaving our important passwords in easy to reach places. So moving towards something more secure whilst retaining usability is desirable. I’ll detail the current methods, the problems, and finish off with Docker Secrets – which in my opinion, is the right direction to be heading.

Environment Variables

I’ll elaborate on the main reason why we would want to change from the default given behavior. In the documentation for using the MySQL/Percona and MariaDB containers, we are invited to start containers with an environment variable to control what the instance’s root password is set as upon startup. Let’s demonstrate with the latest official Percona-Server image from Percona’s repository of images on the Docker Hub registry:

moore@chom:~$ docker pull percona/percona-server:latest
latest: Pulling from percona/percona-server
e12c678537ae: Already exists
65ab4b835640: Pull complete
f63269a127d1: Pull complete
757a4fef28b8: Pull complete
b0cb547a5105: Pull complete
4214179ba9ea: Pull complete
155dafd2fd9c: Pull complete
848020b1da10: Pull complete
771687fe7e8b: Pull complete
Digest: sha256:f3197cac76cccd40c3525891ce16b0e9f6d650ccef76e993ed7a22654dc05b73
Status: Downloaded newer image for percona/percona-server:latest

Then start a container:

moore@chom:~$ docker run -d
--name percona-server-1
-e MYSQL_ROOT_PASSWORD='secret'
percona/percona-server
d08f299a872f1408c142b58bc2ce8e59004acfdb26dca93d71f5e9367b4f2a57
moore@chom:~$ docker ps
CONTAINER ID        IMAGE                            COMMAND             CREATED             STATUS              PORTS               NAMES
d08f299a872f        percona/percona-server           "/entrypoint.sh "   32 seconds ago      Up 31 seconds       3306/tcp            percona-server-1

Looks good, eh? Let’s inspect this container a little closer to reveal why this method is flawed:

moore@chom:~$ docker inspect --format '{{ index (index .Config.Env) 0}}' percona-server-1
MYSQL_ROOT_PASSWORD=secret

*facepalm*

We don’t want the root password exposed here, not really. If we wanted to use this method in docker-compose files, we would also be storing passwords inline, which isn’t considered a secure practice.

Environment File

Another approach is to use an environment file. This is simply a file that we can provide docker run or docker-compose in order to instantiate the environment variables within the container. It’s a construct for convenience. So just to illustrate that we have the same problem, the next example uses the mechanism of an environment file for our database container:

moore@chom:~$ echo 'MYSQL_ROOT_PASSWORD=secret' > /tmp/ps.env
moore@chom:~$ docker run -d --name percona-server-2 --env-file=/tmp/ps.env percona/percona-server
d5105d044673bd5912e0e29c2f56fa37c5f174d9d2a4811ceaba284092837c84
moore@chom:~$ docker inspect --format '{{ index (index .Config.Env) 0}}' percona-server-2
MYSQL_ROOT_PASSWORD=secret
NOTE: shortly after starting this container failed because we didn't provide mysql root password options

While we’re not specifying it in our docker run command or our docker-compose.yml file, the password value remains on our filesystem within the environment file. Again, not ideal.

Password File

With the ability to use a password file it obscures this from the inspect output. Let’s roll through the steps we would use to leverage this new option. With our new Percona-Server image, we’re going to start a container, but first let’s create an arbitrary file containing our desired password:

moore@chom:~$ docker:cloud> echo "secret" > /tmp/mysql_pwd_file

Now start a container where we’re going to bind mount the file, and use our new environment variable to point to it:

moore@chom:~$ docker run -v /tmp/mysql_pwd_file:/tmp/mysqlpwd --name percona-secret
-e MYSQL_ROOT_PASSWORD_FILE=/tmp/mysqlpwd percona/percona-server:latest

With the same inspect command, let’s show that there’s no snooping on our password value:

moore@chom:~$ docker inspect --format '{{ index (index .Config.Env) 0}}' percona-secret
MYSQL_ROOT_PASSWORD_FILE=/tmp/mysqlpwd

We are revealing the path where our password was read from within the container. For those eagle-eyed readers, this file was just a bind mounted file in the docker run command, and it’s still on the host’s filesystem.

moore@chom:~$ cat /tmp/mysql_pwd_file
secret
moore@chom:~$ docker exec percona-secret cat /tmp/mysqlpwd
secret

Not perfect, because we need to have that file available on all of our Docker hosts, but it works and we’re closer to a more robust solution.

Docker Secrets

The main reason for the new environment variable is to leverage the docker secrets feature. Since Docker version 1.13 (17.03 is now GA), we have the Docker Secrets feature, however it’s only available to the Docker Swarm workflow. If you’re not already working with Docker Swarm mode, I can’t recommend it enough. It’s part of Docker-engine, simple to get started, and intuitive since 1.13 it is compatible with docker-compose files. You don’t need to have a cluster of hardware, it’s entirely valid to use Docker Swarm on a single node. This allows you to test on your local environment with ease.

I won’t waste pixels explaining what’s already well documented in official channels, but in summary: Docker secrets is a new feature that allows us to keep sensitive information out of source code and configuration files. Secrets are stored in the Raft log which is encrypted and replicated throughout the Docker Swarm cluster. The protection and distribution come for free out of the box, which is a fantastic feature if you ask me.

So, let’s create a Docker Secret. Please note that I’ve moved to my Docker Swarm installation for this next part:

moore@chom:~$ docker:cloud> docker info | egrep -i 'swarm|version'
Server Version: 17.03.0-ce
Swarm: active

Operating as a swarm manager we have the ability to create a new secret to serve as our root user’s password:

moore@chom:~$ docker:cloud> echo "{secret_string}" | docker secret create mysql_root_password -
ugd8dx0kae9hbyt4opbolukgi

We can list all of our existing secrets:

moore@chom:~$ docker:cloud> docker secret ls
ID                          NAME                  CREATED                  UPDATED
ugd8dx0kae9hbyt4opbolukgi   mysql_root_password   Less than a second ago   Less than a second ago

Now our secret has been created, it’s obscured from us. We are unable to see it’s value.

moore@chom:~$ docker secret inspect mysql_root_password
[
    {
        "ID": "ugd8dx0kae9hbyt4opbolukgi",
        "Version": {
            "Index": 905780
        },
        "CreatedAt": "2017-04-11T23:33:08.118037434Z",
        "UpdatedAt": "2017-04-11T23:33:08.118037434Z",
        "Spec": {
            "Name": "mysql_root_password"
        }
    }
]

Now we can use our secret to set our authentication for the MySQL instance by doing the following:

moore@chom:~$ docker service create
--name percona-secret
--secret mysql_root_password
-e MYSQL_ROOT_PASSWORD_FILE=/run/secrets/mysql_root_password
percona/percona-server:latest

You can see that instead of docker run, I’ve issued the swarm equivalent docker service create, which is going to start a new Percona-Server container in the scope of my Swarm workflow. I’m also using the –secret option to tell docker to mount my secret in the container, which gets mounted to a file under the path /run/secrets/{secret_name}. The final point here, I’m passing MYSQL_ROOT_PASSWORD_FILE=/path/to/secret as an environment variable to let the startup script know where to find the file with my secret value for the root password. Once the startup routine has completed and the container has started successfully I can connect to my container to test the password was set correctly:

moore@chom:~$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
397bdf9b75f9 percona/percona-server "/entrypoint.sh " 46 seconds ago Up 44 seconds 3306/tcp percona-secret.1.9xvbneset9363dr5xv4fqqxua
moore@chom:~$ docker exec -ti 397bdf9b75f9 bash
mysql@397bdf9b75f9:/$ cat /run/secrets/mysql_root_password
{secret_string}
mysql@397bdf9b75f9:/$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.7.17-11 Percona Server (GPL), Release '11', Revision 'f60191c'
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, 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>

The secret can be shared around any container where it’s necessary, simply by telling Docker to use the secret when instantiating a container. For example, if I wanted to start an application container such as a WordPress instance, I can use a secret object to easily share credentials to the data source safely and consistently.

This method is totally viable for other forms of sensitive data. For example, I can generate SSL certificates and use Docker secrets to add them to my containers for encrypted replication or forcing secure logins from remote clients. I’m still thinking of all the possible use cases for this option and no doubt will have some more to share with you in the near future.

Please share your comments, suggestions and corrections in the comments below. Thank you for reading.

Apr
21
2017
--

Simplified Percona XtraDB Cluster SSL Configuration

Percona XtraDB Cluster SST Traffic Encryption

Percona XtraDB Cluster SSLIn this blog post, we’ll look at a feature that recently added to Percona XtraDB Cluster 5.7.16, that makes it easier to configure Percona XtraDB Cluster SSL for all related communications. It uses mode “encrypt=4”, and configures SSL for both IST/Galera communications and SST communications using the same SSL files. “encrypt=4” is a new encryption mode added in Percona XtraDB Cluster 5.7.16 (we’ll cover it in a later blog post).

If this option is used, this will override all other Galera/SST SSL-related file options. This is to ensure that a consistent configuration is applied.
Using this option also means that the Galera/SST communications are using the same keys as client connections.

Example

This example shows how to startup a cluster using this option. We will use the default SSL files created by the bootstrap node. Basically, there are two steps:

  1. Set
    pxc_encrypt_cluster_traffic=ON

     on all nodes

  2. Ensure that all nodes share the same SSL files

Step 1: Configuration (on all nodes)

We enable the

pxc_encrypt_cluster_traffic

 option in the configuration files on all nodes. The default value of this option is “OFF”, so we enable it here.

[mysqld]
 pxc_encrypt_cluster_traffic=ON

Step 2: Startup the bootstrap node

After initializing and starting up the bootstrap node, the datadir will contain the necessary data files. Here is some SSL-related log output:

[Note] Auto generated SSL certificates are placed in data directory.
 [Warning] CA certificate ca.pem is self signed.
 [Note] Auto generated RSA key files are placed in data directory.

The required files are ca.pem, server-cert.pem and server-key.pem, which are the Certificate Authority (CA) file, the server certificate and the server private key, respectively.

Step 3: Copy the SSL files to all other nodes

Galera views the cluster as a set of homogeneous nodes, so the same configuration is expected on all nodes. Therefore, we have to copy the CA file, the server’s certificate and the server’s private key. By default, MySQL names these: ca.pem, server-cert.pem, and server-key.pem, respectively.

Step 4: Startup the other nodes

This is some log output showing that the SSL certificate files have been found. The other nodes should be using the files that were created on the bootstrap node.

[Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
[Note] Skipping generation of SSL certificates as certificate files are present in data directory.
[Warning] CA certificate ca.pem is self signed.
[Note] Skipping generation of RSA key pair as key files are present in data directory.

This is some log output (with

log_error_verbosity=3

), showing the SST reporting on the configuration used.

WSREP_SST: [DEBUG] pxc_encrypt_cluster_traffic is enabled, using PXC auto-ssl configuration
WSREP_SST: [DEBUG] with encrypt=4 ssl_ca=/my/data//ca.pem ssl_cert=/my/data//server-cert.pem ssl_key=/my/data//server-key.pem

Customization

The “ssl-ca”, “ssl-cert”, and “ssl-key” options in the “[mysqld]” section can be used to specify the location of the SSL files. If these are not specified, then the datadir is searched (using the default names of “ca.pem”, “server-cert.pem” and “server-key.pem”).

[mysqld]
 pxc_encrypt_cluster_traffic=ON
 ssl-ca=/path/to/ca.pem
 ssl-cert=/path/to/server-cert.pem
 ssl-key=/path/to/server-key.pem

If you want to implement this yourself, the equivalent configuration file options are:

[mysqld]
wsrep_provider_options=”socket.ssl_key=server-key.pem;socket.ssl_cert=server-cert.pem;socket.ssl_ca=ca.pem”
[sst]
encrypt=4
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

How it works

  1. Determine the location of the SSL files
    1. Uses the values if explicitly specified (via the “ssl-ca”, “ssl-cert” and “ssl-key” options in the “[mysqld]” section)
    2. If the SSL file options are not specified, we look in the data directory for files named “ca.pem”, “server-cert.pem” and “server-key.pem” for the CA file, the server certificate, and the server key, respectively.
  2. Modify the configuration
    1. Overrides the values for socket.ssl_ca, socket.ssl_cert, and socket.ssl_key in
      wsrep_provider_options

       in the “[mysqld]” section.

    2. Sets “encrypt=4” in the “[sst]” section.
    3. Overrides the values for ssl-ca, ssl-cert and ssl-key in the “[sst]” section.

This is not a dynamic setting, and is only available on startup.

Apr
21
2017
--

How to Setup and Troubleshoot Percona PAM with LDAP for External Authentication

Percona PAM

Percona PAMIn this blog, we’ll look at how to setup and troubleshoot the Percona PAM authentication plugin.

We occasionally get requests from our support clients on how to get Percona Server for MySQL to authenticate with an external authentication service via LDAP or Active Directory. However, we normally do not have access to client’s infrastructure to help troubleshoot these cases. To help them effectively, we need to setup a testbed to reproduce their issues and guide them on how to get authentication to work. Fortunately, we only need to install Samba to provide an external authentication service for both LDAP and AD.

In this article, I will show you how to (a) compile and install Samba, (b) create a domain environment with Samba, (c) add users and groups to this domain and (d) get Percona Server to use these accounts for authentication via LDAP. In my follow-up article, I will discuss how to get MySQL to authenticate credentials with Active Directory.

My testbed environment consists of two machines

Samba PDC
OS: CentOS 7
IP Address: 172.16.0.10
Hostname: samba-10.example.com
Domain name: EXAMPLE.COM
DNS: 8.8.8.8(Google DNS), 8.8.4.4(Google DNS), 172.16.0.10(Samba)
Firewall: none

Percona Server 5.7 with LDAP authentication
OS: CentOS 7
IP Address: 172.16.0.20
Hostname: ps-ldap-20.example.com

and have several users and groups:

Domain Groups and Users
Support: jericho, jervin and vishal
DBA: sidd, paul and arunjith
Search: ldap

Compile and Install Samba

We will install an NTP client on the Samba PDC/samba-10.example.com machine because time synchronization is a requirement for domain authentication. We will also compile and install Samba from source because the Samba implementation in the official repository doesn’t include the Active Directory Domain Controller role. Hence, samba-tool is not included in the official repository. For our testbed, we need this tool because it makes it easier to provision a domain and manage users and groups. So, for CentOS 7, you can either build from source or use a trusted 3rd party build of Samba (as discussed in Samba’s wiki).

For more information, please read Setting up Samba as an Active Directory Domain Controller as well.

  1. Install, configure, and run the NTP client. Ensure that this client service runs when the server boots up:
[root@samba-10 ~]# yum -y install ntp
* * *
Installed:
  ntp.x86_64 0:4.2.6p5-25.el7.centos.1
Dependency Installed:
  autogen-libopts.x86_64 0:5.18-5.el7                     ntpdate.x86_64 0:4.2.6p5-25.el7.centos.1
[root@samba-10 ~]# ntpdate 0.centos.pool.ntp.org
 7 Apr 06:06:07 ntpdate[9788]: step time server 202.90.132.242 offset 0.807640 sec
[root@samba-10 ~]# systemctl enable ntpd.service
Created symlink from /etc/systemd/system/multi-user.target.wants/ntpd.service to /usr/lib/systemd/system/ntpd.service.
[root@samba-10 ~]# systemctl start ntpd.service

  1. Install compilers and library dependencies for compiling Samba:
[root@samba-10 ~]# yum -y install gcc perl python-devel gnutls-devel libacl-devel openldap-devel
* * *
Installed:
  gcc.x86_64 0:4.8.5-11.el7  gnutls-devel.x86_64 0:3.3.24-1.el7  libacl-devel.x86_64 0:2.2.51-12.el7  openldap-devel.x86_64 0:2.4.40-13.el7  perl.x86_64 4:5.16.3-291.el7  python-devel.x86_64 0:2.7.5-48.el7
Dependency Installed:
  cpp.x86_64 0:4.8.5-11.el7                            cyrus-sasl.x86_64 0:2.1.26-20.el7_2               cyrus-sasl-devel.x86_64 0:2.1.26-20.el7_2             glibc-devel.x86_64 0:2.17-157.el7_3.1
  glibc-headers.x86_64 0:2.17-157.el7_3.1              gmp-devel.x86_64 1:6.0.0-12.el7_1                 gnutls-c++.x86_64 0:3.3.24-1.el7                      gnutls-dane.x86_64 0:3.3.24-1.el7
  kernel-headers.x86_64 0:3.10.0-514.10.2.el7          ldns.x86_64 0:1.6.16-10.el7                       libattr-devel.x86_64 0:2.4.46-12.el7                  libevent.x86_64 0:2.0.21-4.el7
  libmpc.x86_64 0:1.0.1-3.el7                          libtasn1-devel.x86_64 0:3.8-3.el7                 mpfr.x86_64 0:3.1.1-4.el7                             nettle-devel.x86_64 0:2.7.1-8.el7
  p11-kit-devel.x86_64 0:0.20.7-3.el7                  perl-Carp.noarch 0:1.26-244.el7                   perl-Encode.x86_64 0:2.51-7.el7                       perl-Exporter.noarch 0:5.68-3.el7
  perl-File-Path.noarch 0:2.09-2.el7                   perl-File-Temp.noarch 0:0.23.01-3.el7             perl-Filter.x86_64 0:1.49-3.el7                       perl-Getopt-Long.noarch 0:2.40-2.el7
  perl-HTTP-Tiny.noarch 0:0.033-3.el7                  perl-PathTools.x86_64 0:3.40-5.el7                perl-Pod-Escapes.noarch 1:1.04-291.el7                perl-Pod-Perldoc.noarch 0:3.20-4.el7
  perl-Pod-Simple.noarch 1:3.28-4.el7                  perl-Pod-Usage.noarch 0:1.63-3.el7                perl-Scalar-List-Utils.x86_64 0:1.27-248.el7          perl-Socket.x86_64 0:2.010-4.el7
  perl-Storable.x86_64 0:2.45-3.el7                    perl-Text-ParseWords.noarch 0:3.29-4.el7          perl-Time-HiRes.x86_64 4:1.9725-3.el7                 perl-Time-Local.noarch 0:1.2300-2.el7
  perl-constant.noarch 0:1.27-2.el7                    perl-libs.x86_64 4:5.16.3-291.el7                 perl-macros.x86_64 4:5.16.3-291.el7                   perl-parent.noarch 1:0.225-244.el7
  perl-podlators.noarch 0:2.5.1-3.el7                  perl-threads.x86_64 0:1.87-4.el7                  perl-threads-shared.x86_64 0:1.43-6.el7               unbound-libs.x86_64 0:1.4.20-28.el7
  zlib-devel.x86_64 0:1.2.7-17.el7
Complete!

  1. Download, compile and install Samba:
[root@samba-10 ~]# yum -y install wget
* * *
[root@samba-10 ~]# wget https://www.samba.org/samba/ftp/samba-latest.tar.gz
* * *
2017-04-07 06:16:59 (337 KB/s) - 'samba-latest.tar.gz' saved [21097045/21097045]
[root@samba-10 ~]# tar xzf samba-latest.tar.gz
[root@samba-10 ~]# cd samba-4.6.2/
[root@samba-10 samba-4.6.2]# ./configure --prefix=/opt/samba
Checking for program gcc or cc           : /usr/bin/gcc
Checking for program cpp                 : /usr/bin/cpp
Checking for program ar                  : /usr/bin/ar
Checking for program ranlib              : /usr/bin/ranlib
* * *
Checking compiler for PIE support                                                               : yes
Checking compiler for full RELRO support                                                        : yes
Checking if toolchain accepts -fstack-protector                                                 : yes
'configure' finished successfully (39.119s)
[root@samba-10 samba-4.6.2]# make
WAF_MAKE=1 python ./buildtools/bin/waf build
Waf: Entering directory `/root/samba-4.6.2/bin'
symlink: tevent.py -> python/tevent.py
* * *
[3773/3775] Linking default/source3/modules/libvfs_module_acl_xattr.so
[3774/3775] Linking default/source3/modules/libvfs_module_shadow_copy.so
[3775/3775] Linking default/source3/modules/libvfs_module_dirsort.so
Waf: Leaving directory `/root/samba-4.6.2/bin'
'build' finished successfully (6m58.144s)
[root@samba-10 samba-4.6.2]# make install
WAF_MAKE=1 python ./buildtools/bin/waf install
Waf: Entering directory `/root/samba-4.6.2/bin'
* creating /opt/samba/etc
* creating /opt/samba/private
* * *
* installing bin/default/source3/nmbd/nmbd.inst as /opt/samba/sbin/nmbd
* installing bin/default/file_server/libservice_module_s3fs.inst.so as /opt/samba/lib/service/s3fs.so
Waf: Leaving directory `/root/samba-4.6.2/bin'
'install' finished successfully (1m44.377s)

Please take note that when I downloaded Samba, the latest version was 4.6.2. If you have a problem with compiling the latest version of Samba, try using version 4.6.2.

  1. Include executable path of Samba to the PATH variable so we can call samba binaries without specifying its absolute path:
[root@samba-10 samba-4.6.2]# echo "PATH=/opt/samba/sbin:/opt/samba/bin:/usr/sbin:/usr/bin" >> /etc/environment
[root@samba-10 samba-4.6.2]# PATH=/opt/samba/sbin:/opt/samba/bin:/usr/sbin:/usr/bin
[root@samba-10 samba-4.6.2]# which samba-tool
/opt/samba/bin/samba-tool

  1. Setup systemd script for Samba and ensure that this service auto starts on server boot
[root@samba-10 samba-4.6.2]# echo "[Unit]
Description=Samba PDC
After=syslog.target network.target
[Service]
Type=forking
PIDFile=//opt/samba/var/run/samba.pid
ExecStart=/opt/samba/sbin/samba -D
ExecReload=/usr/bin/kill -HUP $MAINPID
ExecStop=/usr/bin/kill $MAINPID
[Install]
WantedBy=multi-user.target" > /etc/systemd/system/samba.service
[root@samba-10 samba-4.6.2]# systemctl enable samba.service
Created symlink from /etc/systemd/system/multi-user.target.wants/samba.service to /etc/systemd/system/samba.service.

  1. Remove existing /etc/krb5.conf, because the existing configuration prevents us from provisioning a new domain.
[root@samba-10 samba-4.6.2]# rm -f /etc/krb5.conf
[root@samba-10 samba-4.6.2]# cd
[root@samba-10 ~]#

  1. Done.

Create a domain environment with Samba

  1. To setup a domain, all we need to do is to run “samba-tool domain provision” and pass the following details:

Realm: EXAMPLE.COM
Domain: EXAMPLE
Server Role: dc(domain controller)
DNS backend: SAMBA_INTERNAL
DNS forwarder IP address: 8.8.8.8

You will also need to supply the Administrator password. This account is used to join a workstation or server to a domain:

[root@samba-10 ~]# samba-tool domain provision
Realm [EXAMPLE.ORG]: EXAMPLE.COM
 Domain [EXAMPLE]: EXAMPLE
 Server Role (dc, member, standalone) [dc]: dc
 DNS backend (SAMBA_INTERNAL, BIND9_FLATFILE, BIND9_DLZ, NONE) [SAMBA_INTERNAL]: SAMBA_INTERNAL
 DNS forwarder IP address (write 'none' to disable forwarding) [8.8.8.8]: 8.8.8.8
Administrator password:
Retype password:
Looking up IPv4 addresses
Looking up IPv6 addresses
No IPv6 address will be assigned
Setting up secrets.ldb
Setting up the registry
Setting up the privileges database
Setting up idmap db
Setting up SAM db
Setting up sam.ldb partitions and settings
Setting up sam.ldb rootDSE
Pre-loading the Samba 4 and AD schema
Adding DomainDN: DC=example,DC=com
Adding configuration container
Setting up sam.ldb schema
Setting up sam.ldb configuration data
Setting up display specifiers
Modifying display specifiers
Adding users container
Modifying users container
Adding computers container
Modifying computers container
Setting up sam.ldb data
Setting up well known security principals
Setting up sam.ldb users and groups
Setting up self join
Adding DNS accounts
Creating CN=MicrosoftDNS,CN=System,DC=example,DC=com
Creating DomainDnsZones and ForestDnsZones partitions
Populating DomainDnsZones and ForestDnsZones partitions
Setting up sam.ldb rootDSE marking as synchronized
Fixing provision GUIDs
A Kerberos configuration suitable for Samba AD has been generated at /opt/samba/private/krb5.conf
Once the above files are installed, your Samba4 server will be ready to use
Server Role:           active directory domain controller
Hostname:              samba-10
NetBIOS Domain:        EXAMPLE
DNS Domain:            example.com
DOMAIN SID:            S-1-5-21-1337223342-1741564684-602463608

Please take note that if you get the error below, it’s likely due to not removing the existing /etc/krb5.conf before using samba-tool:

ERROR(ldb): uncaught exception - operations error at ../source4/dsdb/samdb/ldb_modules/password_hash.c:2820
  File "/opt/samba/lib64/python2.7/site-packages/samba/netcmd/__init__.py", line 176, in _run
    return self.run(*args, **kwargs)
  File "/opt/samba/lib64/python2.7/site-packages/samba/netcmd/domain.py", line 471, in run
    nosync=ldap_backend_nosync, ldap_dryrun_mode=ldap_dryrun_mode)
  File "/opt/samba/lib64/python2.7/site-packages/samba/provision/__init__.py", line 2175, in provision
    skip_sysvolacl=skip_sysvolacl)
  File "/opt/samba/lib64/python2.7/site-packages/samba/provision/__init__.py", line 1787, in provision_fill
    next_rid=next_rid, dc_rid=dc_rid)
  File "/opt/samba/lib64/python2.7/site-packages/samba/provision/__init__.py", line 1447, in fill_samdb
    "KRBTGTPASS_B64": b64encode(krbtgtpass.encode('utf-16-le'))
  File "/opt/samba/lib64/python2.7/site-packages/samba/provision/common.py", line 55, in setup_add_ldif
    ldb.add_ldif(data, controls)
  File "/opt/samba/lib64/python2.7/site-packages/samba/__init__.py", line 225, in add_ldif
    self.add(msg, controls)

You could also get an error if you entered a simple password for the Administrator account.

  1. Create a symlink of the generated krb5.conf in /etc. This configuration is used authenticate machines, accounts and services:
[root@samba-10 ~]# ln -s /opt/samba/private/krb5.conf /etc

  1. Start the Samba service:
[root@samba-10 ~]# systemctl start samba.service

  1. Check network ports to see if Samba is running:
[root@samba-10 ~]# yum -y install net-tools
* * *
[root@samba-10 ~]# netstat -tapn
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:464             0.0.0.0:*               LISTEN      13296/samba
tcp        0      0 0.0.0.0:53              0.0.0.0:*               LISTEN      13302/samba
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      875/sshd
tcp        0      0 0.0.0.0:88              0.0.0.0:*               LISTEN      13296/samba
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1327/master
tcp        0      0 0.0.0.0:636             0.0.0.0:*               LISTEN      13294/samba
tcp        0      0 0.0.0.0:445             0.0.0.0:*               LISTEN      13307/smbd
tcp        0      0 0.0.0.0:1024            0.0.0.0:*               LISTEN      13291/samba
tcp        0      0 0.0.0.0:1025            0.0.0.0:*               LISTEN      13291/samba
tcp        0      0 0.0.0.0:3268            0.0.0.0:*               LISTEN      13294/samba
tcp        0      0 0.0.0.0:3269            0.0.0.0:*               LISTEN      13294/samba
tcp        0      0 0.0.0.0:389             0.0.0.0:*               LISTEN      13294/samba
tcp        0      0 0.0.0.0:135             0.0.0.0:*               LISTEN      13291/samba
tcp        0      0 0.0.0.0:139             0.0.0.0:*               LISTEN      13307/smbd

  1. Done.

Add users and groups to this domain

Now that Samba is running we can add users and groups, and assign users to groups with samba-tool.

  1. Add groups by running “samba-tool group add group_name”:
[root@samba-10 ~]# samba-tool group add support
Added group support
[root@samba-10 ~]# samba-tool group add dba
Added group dba
[root@samba-10 ~]# samba-tool group add search
Added group search

  1. Add users by running “samba-tool user create username”:
[root@samba-10 ~]# samba-tool user create jericho
New Password:
Retype Password:
User 'jericho' created successfully
[root@samba-10 ~]# samba-tool user create jervin
New Password:
Retype Password:
User 'jervin' created successfully
[root@samba-10 ~]# samba-tool user create vishal
New Password:
Retype Password:
User 'vishal' created successfully
[root@samba-10 ~]# samba-tool user create sidd
New Password:
Retype Password:
User 'sidd' created successfully
[root@samba-10 ~]# samba-tool user create paul
New Password:
Retype Password:
User 'paul' created successfully
[root@samba-10 ~]# samba-tool user create arunjith
New Password:
Retype Password:
User 'arunjith' created successfully
[root@samba-10 ~]# samba-tool user create ldap
New Password:
Retype Password:
User 'ldap' created successfully

  1. Add users to their corresponding groups with “samba-tool group addmembers group_name user,user2,usern”:
[root@samba-10 ~]# samba-tool group addmembers support jericho,jervin,vishal
Added members to group support
[root@samba-10 ~]# samba-tool group addmembers dba sidd,paul,arunjith
Added members to group dba
[root@samba-10 ~]# samba-tool group addmembers search ldap
Added members to group search

  1. Verify that users, groups and memberships exist with commands “samba-tool user list”, “samba-tool group list” and “samba-tool group listmembers group_name”:
[root@samba-10 ~]# samba-tool user list
Administrator
arunjith
jericho
jervin
krbtgt
vishal
Guest
ldap
paul
sidd
[root@samba-10 ~]# samba-tool group list
Allowed RODC Password Replication Group
Enterprise Read-Only Domain Controllers
Denied RODC Password Replication Group
Pre-Windows 2000 Compatible Access
Windows Authorization Access Group
Certificate Service DCOM Access
Network Configuration Operators
Terminal Server License Servers
Incoming Forest Trust Builders
Read-Only Domain Controllers
Group Policy Creator Owners
Performance Monitor Users
Cryptographic Operators
Distributed COM Users
Performance Log Users
Remote Desktop Users
Account Operators
Event Log Readers
RAS and IAS Servers
Backup Operators
Domain Controllers
Server Operators
Enterprise Admins
Print Operators
Administrators
Domain Computers
Cert Publishers
DnsUpdateProxy
Domain Admins
Domain Guests
Schema Admins
Domain Users
Replicator
IIS_IUSRS
DnsAdmins
Guests
Users
support
search
dba
[root@samba-10 ~]# samba-tool group listmembers support
jervin
jericho
vishal
[root@samba-10 ~]# samba-tool group listmembers dba
arunjith
sidd
paul
[root@samba-10 ~]# samba-tool group listmembers search
ldap

For more information on using samba-tool, just run

samba-tool --help

.

  1. Done.

How to get Percona Server to use these accounts for authentication via LDAP

We will be using the machine ps-ldap-20.example.com to offer MySQL service with LDAP authentication via Percona PAM. If you’re not familiar with Percona PAM, please have a look at this before moving forward.

At this point, our Samba service is running with users, groups and memberships added. We can now query Samba via LDAP ports 389 and 636. We will configure the server to do LDAP lookups when searching for users and groups. This is necessary because we use the name service to validate group membership. We will then install Percona Server for MySQL and configure our PAM plugin to use

nss-pam-ldapd

 to authenticate to LDAP. Finally, we will test LDAP authentication on Percona Server for MySQL using a regular user and proxy user.

  1. Install
    nss-pam-ldapd

     and

    nscd

    . We will use these packages to query LDAP server from our server:

[root@ps-20 ~]# yum -y install nss-pam-ldapd

  1. Configure
    nss-pam-ldapd

     by incorporating our Samba’s LDAP settings:

[root@ps-20 ~]# echo "uid nslcd
gid ldap
pagesize 1000
referrals off
idle_timelimit 800
filter passwd (&(objectClass=user)(objectClass=person)(!(objectClass=computer)))
map    passwd uid           sAMAccountName
map    passwd uidNumber     objectSid:S-1-5-21-1337223342-1741564684-602463608
map    passwd gidNumber     objectSid:S-1-5-21-1337223342-1741564684-602463608
map    passwd homeDirectory "/home/$cn"
map    passwd gecos         displayName
map    passwd loginShell    "/bin/bash"
filter group (|(objectClass=group)(objectClass=person))
map    group gidNumber      objectSid:S-1-5-21-1337223342-1741564684-602463608
uri ldaps://172.16.0.10
base dc=example,dc=com
tls_reqcert never
binddn cn=ldap,cn=Users,dc=example,dc=com
bindpw MyLdapPasswordDontCopyIt2017" > /etc/nslcd.conf

As you can see above, this config contains LDAP settings, mapping custom LDAP attributes, and LDAP credentials. The value of objectSid was taken from “DOMAIN SID” that was generated when I created a new domain. So, be sure to use the value of “DOMAIN SID” generated on your end. Otherwise, your LDAP queries will not match any record. However, if you’re authenticating from an existing Windows AD server, you can obtain the value of “DOMAIN SID” by running “Get-ADDomain”. Also, you can take a look at this link to get to know more about other configurations for nslcd.conf.

  1. Add LDAP lookup to nsswitch service by editing /etc/nsswitch.conf:

Find:
passwd: files sss
shadow: files sss
group: files sss

Replace with:
passwd: files sss ldap
shadow: files sss ldap
group: files sss ldap

  1. Run nslcd in debug mode:
[root@ps-20 ~]# nslcd -d
nslcd: DEBUG: add_uri(ldaps://172.16.0.10)
nslcd: DEBUG: ldap_set_option(LDAP_OPT_X_TLS_REQUIRE_CERT,0)
nslcd: version 0.8.13 starting
nslcd: DEBUG: unlink() of /var/run/nslcd/socket failed (ignored): No such file or directory
nslcd: DEBUG: initgroups("nslcd",55) done
nslcd: DEBUG: setgid(55) done
nslcd: DEBUG: setuid(65) done
nslcd: accepting connections

  1. Test if LDAP lookups work by running “id ” and “getent passwd” on another terminal:
[root@ps-20 ~]# id jervin
uid=1107(jervin) gid=1107(jervin) groups=1107(jervin),1103(support)
[root@ps-20 ~]# id paul
uid=1110(paul) gid=1110(paul) groups=1110(paul),1104(dba)
[root@ps-20 ~]# getent passwd
root:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin
adm:x:3:4:adm:/var/adm:/sbin/nologin
lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
sync:x:5:0:sync:/sbin:/bin/sync
shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
halt:x:7:0:halt:/sbin:/sbin/halt
mail:x:8:12:mail:/var/spool/mail:/sbin/nologin
operator:x:11:0:operator:/root:/sbin/nologin
games:x:12:100:games:/usr/games:/sbin/nologin
ftp:x:14:50:FTP User:/var/ftp:/sbin/nologin
nobody:x:99:99:Nobody:/:/sbin/nologin
avahi-autoipd:x:170:170:Avahi IPv4LL Stack:/var/lib/avahi-autoipd:/sbin/nologin
systemd-bus-proxy:x:999:997:systemd Bus Proxy:/:/sbin/nologin
systemd-network:x:998:996:systemd Network Management:/:/sbin/nologin
dbus:x:81:81:System message bus:/:/sbin/nologin
polkitd:x:997:995:User for polkitd:/:/sbin/nologin
tss:x:59:59:Account used by the trousers package to sandbox the tcsd daemon:/dev/null:/sbin/nologin
postfix:x:89:89::/var/spool/postfix:/sbin/nologin
sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
user:x:1000:1000:user:/home/user:/bin/bash
mysql:x:27:27:Percona Server:/var/lib/mysql:/bin/false
nscd:x:28:28:NSCD Daemon:/:/sbin/nologin
nslcd:x:65:55:LDAP Client User:/:/sbin/nologin
Administrator:*:500:500::/home/Administrator:/bin/bash
arunjith:*:1111:1111::/home/arunjith:/bin/bash
jericho:*:1106:1106::/home/jericho:/bin/bash
jervin:*:1107:1107::/home/jervin:/bin/bash
krbtgt:*:502:502::/home/krbtgt:/bin/bash
vishal:*:1108:1108::/home/vishal:/bin/bash
Guest:*:501:501::/home/Guest:/bin/bash
ldap:*:1112:1112::/home/ldap:/bin/bash
paul:*:1110:1110::/home/paul:/bin/bash
sidd:*:1109:1109::/home/sidd:/bin/bash

If you take a look at the nslcd terminal again, you will see that it’s trying to resolve the user and group identification with LDAP searches:

* * *
nslcd: [7b23c6] <passwd=1107> DEBUG: ldap_simple_bind_s("cn=ldap,cn=Users,dc=example,dc=com","***") (uri="ldaps://172.16.0.10")
nslcd: [7b23c6] <passwd=1107> DEBUG: ldap_result(): CN=jervin,CN=Users,DC=example,DC=com
nslcd: [7b23c6] <passwd=1107> DEBUG: ldap_result(): end of results (1 total)
nslcd: [3c9869] DEBUG: connection from pid=10468 uid=0 gid=0
nslcd: [3c9869] <passwd=1107> DEBUG: myldap_search(base="dc=example,dc=com", filter="(&(&(objectClass=user)(objectClass=person)(!(objectClass=computer)))(objectSid=?1?5?0?0?0?0?0?515?0?0?0ae68b44f?c2bce6778dde8...
* * *
nslcd: [5558ec] <passwd="paul"> DEBUG: myldap_search(base="dc=example,dc=com", filter="(&(&(objectClass=user)(objectClass=person)(!(objectClass=computer)))(sAMAccountName=paul))")
nslcd: [5558ec] <passwd="paul"> DEBUG: ldap_result(): CN=paul,CN=Users,DC=example,DC=com
nslcd: [5558ec] <passwd="paul"> DEBUG: ldap_result(): end of results (1 total)
* * *
nslcd: [e2a9e3] <passwd(all)> DEBUG: myldap_search(base="dc=example,dc=com", filter="(&(objectClass=user)(objectClass=person)(!(objectClass=computer)))")
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=Administrator,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=arunjith,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=jericho,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=jervin,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=krbtgt,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=vishal,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=Guest,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=ldap,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=paul,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=sidd,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): end of results (10 total)

Now that we know nslcd is working, shut it down by running “Ctrl-C”.

  1. Run nslcd normally and make sure it starts up on boot:
[root@ps-20 ~]# systemctl start nslcd.service
[root@ps-20 ~]# systemctl enable nslcd.service
Created symlink from /etc/systemd/system/multi-user.target.wants/nslcd.service to /usr/lib/systemd/system/nslcd.service.

  1. Install and run Percona Server for MySQL 5.7 and make sure it runs when the server boots up:
[root@ps-20 ~]# rpm -Uvh https://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-4.noarch.rpm
Retrieving https://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-4.noarch.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:percona-release-0.1-4            ################################# [100%]
[root@ps-20 ~]# yum -y install Percona-Server-server-57
* * *
[root@ps-20 ~]# mysqld --initialize-insecure --user=mysql
[root@ps-20 ~]# systemctl start mysqld.service
[root@ps-20 ~]# systemctl enable mysqld.service
Created symlink from /etc/systemd/system/mysql.service to /usr/lib/systemd/system/mysqld.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.

  1. Login to MySQL and change the root password:
[root@ps-20 ~]# mysql -uroot
mysql> SET PASSWORD=PASSWORD('MyNewAndImprovedPassword');

  1. Install the Percona PAM plugin:
mysql> delete from mysql.user where user='';
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN auth_pam SONAME 'auth_pam.so';
Query OK, 0 rows affected (0.01 sec)
mysql> INSTALL PLUGIN auth_pam_compat SONAME 'auth_pam_compat.so';
Query OK, 0 rows affected (0.00 sec)

  1. Configure Percona PAM to authenticate to LDAP by creating /etc/pam.d/mysqld with this content:
auth required pam_ldap.so
account required pam_ldap.so

  1. Create a MySQL user that will authenticate via auth_pam:
mysql> CREATE USER jervin@'%' IDENTIFIED WITH auth_pam;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON support.* TO jervin@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

  1. Login as this user and check grants:
[root@ps-20 ~]# mysql -u jervin
Password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 22
Server version: 5.7.17-13 Percona Server (GPL), Release 13, Revision fd33d43
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, 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> SHOW GRANTS;
+-----------------------------------------------------+
| Grants for jervin@%                                 |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'jervin'@'%'                  |
| GRANT ALL PRIVILEGES ON `support`.* TO 'jervin'@'%' |
+-----------------------------------------------------+
2 rows in set (0.00 sec)

It works! However, if you have 100 support users who have the same MySQL privileges, creating 100 MySQL users is tedious and can be difficult to maintain. If belonging to a group has certain MySQL privileges, setup proxy users instead to map a user’s privilege to its defined group. We will implement this for both dba and support users in the next step.

For now, delete the user we just created:

mysql> DROP USER jervin@'%';
Query OK, 0 rows affected (0.00 sec)

  1. Create proxy user and proxied accounts:
mysql> CREATE USER ''@'' IDENTIFIED WITH auth_pam as 'mysqld,support=support_users,dba=dba_users';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER support_users@'%' IDENTIFIED BY 'some_password';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER dba_users@'%' IDENTIFIED BY 'some_password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON support.* TO support_users@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO dba_users@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT PROXY ON support_users@'%' TO ''@'';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT PROXY ON dba_users@'%' TO ''@'';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

To know more about setting up proxy users, see this article written by Stephane.

  1. Let’s try logging in as “jericho” and “paul” and see if they inherit the privileges of their group.
[root@ps-20 ~]# mysql -ujericho -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 25
Server version: 5.7.17-13 Percona Server (GPL), Release 13, Revision fd33d43
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, 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> SELECT user(), current_user(), @@proxy_user;
+-------------------+-----------------+--------------+
| user()            | current_user()  | @@proxy_user |
+-------------------+-----------------+--------------+
| jericho@localhost | support_users@% | ''@''        |
+-------------------+-----------------+--------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS;
+------------------------------------------------------------+
| Grants for support_users@%                                 |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'support_users'@'%'                  |
| GRANT ALL PRIVILEGES ON `support`.* TO 'support_users'@'%' |
+------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> quit
Bye
[root@ps-20 ~]# mysql -upaul -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 27
Server version: 5.7.17-13 Percona Server (GPL), Release 13, Revision fd33d43
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, 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> SELECT user(), current_user(), @@proxy_user;
+----------------+----------------+--------------+
| user()         | current_user() | @@proxy_user |
+----------------+----------------+--------------+
| paul@localhost | dba_users@%    | ''@''        |
+----------------+----------------+--------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS;
+------------------------------------------------+
| Grants for dba_users@%                         |
+------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'dba_users'@'%' |
+------------------------------------------------+
1 row in set (0.00 sec)

As you can see, they did inherit the MySQL privileges of their groups.

  1. Done.

Conclusion

To be honest, setting up Percona PAM with LDAP can be challenging if you add this functionality with existing infrastructure. But hopefully, by setting this up in a lab environment from scratch, and doing some tests, you’ll be confident enough to incorporate this feature in production environments.

Apr
21
2017
--

Enabling Percona XtraDB Cluster SST Traffic Encryption

Percona XtraDB Cluster SST Traffic Encryption

Percona XtraDB Cluster SST Traffic EncryptionIn this blog post, we’ll look at enabling Percona XtraDB Cluster SST Traffic Encryption, and some of the changes to the SSL-based encryption of SST traffic in Percona XtraDB Cluster 5.7.16.

Some background

Percona XtraDB Cluster versions prior to 5.7 support encryption methods 0, 1, 2 and 3:

  • encrypt = 0 : (default) No encryption
  • encrypt = 1 : Symmetric encryption using AES-128, user-supplied key
  • encrypt = 2 : SSL-based encryption with a CA and cert files (via socat)
  • encrypt = 3 : SSL-based encryption with cert and key files (via socat)

We are deprecating modes encrypt=1,2,3 in favor of the new mode, encrypt=4. “encrypt=3” is not recommended, since it does not verify the cert being used (it cannot verify since no Certificate Authority (CA) file is provided). “encrypt=2” and “encrypt=3” use a slightly different way of building the SSL files than MySQL does. In order to remove confusion, we’ve deprecated these modes in favor of “encrypt=4”, which can use the MySQL generated SSL files.

New feature: encrypt= 4

The previous SSL methods (encrypt=2 and encrypt=3), are based on socat usage, http://www.dest-unreach.org/socat/doc/socat-openssltunnel.html. The certs are not built the same way as the certs created by MySQL (for encryption of client communication with MySQL). To simplify SSL configuration and usage, we added a new encryption method (encrypt=4) so that the SSL files generated by MySQL can now be used for SSL encryption of SST traffic.

For instructions on how to create these files, see https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/encrypt-traffic.html.

Configuration

In general, galera views the cluster as homogeneous, so it expects that all nodes are identically configured. This extends to the SSL configuration, so the preferred configuration is that all machines share the same certs/keys. The security implication is that possession of these certs/keys allows a machine to join the cluster and receive all of the data. So proper care must be taken to secure the SSL files.

The mode “encrypt=4” uses the same option names as MySQL, so it reads the SSL information from “ssl-ca”, “ssl-cert”, and “ssl-key” in the “[sst]” section of the configuration file.

Example my.cnf:

[sst]
 encrypt=4
 ssl-ca=/path/to/ca.pem
 ssl-cert=/path/to/server-cert.pem
 ssl-key=/path/to/server-key.pem

All three options (ssl-ca, ssl-cert, and ssl-key) must be specified otherwise the SST will return an error.

ssl-ca

This is the location of the Certificate Authority (CA) file. Only servers that have certificates generated from this CA file will be allowed to connect if SSL is enabled.

ssl-cert

This is the location fo the Certificate file. This is the digital certificate that will be sent to the other side of the SSL connection. The remote server will then verify that this certificate was generated from the Certificate Authority file in use by the remote server.

ssl-key

This is the location of the private key for the certificate specified in ssl-cert.

Apr
11
2017
--

Security startup Synack scores $21M investment from Microsoft, HPE and Singtel

A silhouette of a hacker with a black hat in a suit enters a hallway with walls textured with blue internet of things icons 3D illustration cybersecurity concept Synack, a startup that combines software security tools with a network of white-hat hackers to help keep its customers secure, announced a $21.25 million Series C funding round today. The round was led by Microsoft Ventures with participation from Hewlett Packard Enterprise and Singtel Innov8. Previous investors GGV Capital, GV (formerly Google Ventures) and Kleiner Perkins Caufield &… Read More

Apr
07
2017
--

Okta pops as Wall Street continues to take a shine to the enterprise

Okta Team Rings Bell Okta came out of the gate strong today in its Wall Street debut, attracting the type of institutional investors CEO Todd McKinnon says should be around for the long haul. This IPO comes at a time when Wall Street appears ready to embrace enterprise technology companies. Read More

Mar
16
2017
--

Percona Server for MongoDB: Dashing New LDAP Authentication Plugin

LDAP Authentication

This blog post is another in the series on the Percona Server for MongoDB 3.4 bundle release. In this blog, we’ll look at the new LDAP authentication plugin. 

Hear ye, hear ye, hear ye… With the arrival of version 3.4, Percona has included an LDAP plugin in Percona Server for MongoDB. Authentication is an essential part in client communication with the database backend.

What is LDAP?

LDAP stands for Lightweight Directory Access Protocol. It’s a centralized environment containing information on users or services. This information can be objects like passwords, roles or other meta information. Typically when connecting to the MongoDB server, you simply have to authenticate with the MongoDB servers local credential lists. Using an external authentication method like the one included in Percona Server for MongoDB, you can poll an external service. External authentication allows the MongoDB server to verify the client’s username and password against a separate service, such as OpenLDAP or Active Directory.

LDAP Authentication

Why should you use it?

Having a centralized LDAP offers you the ability to rely on one single “truth” for authentication and authorization. LDAP is essential in large organizations where maintaining users on a big infrastructure becomes troublesome.

In ideal situations, you would use your LDAP authentication for multiple MongoDB servers, and even other database solutions like MySQL. The idea is that you only need to modify the passwords or accounts centrally, so you can manage entries without having to modify them locally on each MongoDB instance.

Having a centralized authentication repository is often a requirement when managing highly sensitive information (due to compliancy requirements). A central repository for user information, like an LDAP server, solves the problem of a central source for authentication. When a user with database level privileges leaves the organization, simply shutting off the one central LDAP account will prevent access to all databases that use it as a source. If local accounts were created without being tied back to a central user repository, then the likelihood of an access revocation getting missed is far greater. This is why many security standards require accounts to be created with an LDAP/Active Directory type of service.

So what components do we actually use?

If you  want to visualize it in a figure:

LDAP Authentication

  • SASL Daemon. Used as a MongoDB server-local proxy for the remote LDAP service. This service is used for MongoDB as an intermediate service. It will translate the request and poll the LDAP server.
  • SASL Library. Used by the MongoDB client and server to create data necessary for the authentication mechanism. This library is used by the MongoDB client and server for making properly formatted requests to the SASL daemon.

So how does it work?

An authentication session uses the following sequence:

  1. A MongoDB client connects to a running mongod instance.
  2. The client creates a PLAIN authentication request using the SASL library.
  3. The client then sends this SASL request to the server as a special MongoDB command.
  4. The mongod server receives this SASL Message, with its authentication request payload.
  5. The server then creates a SASL session scoped to this client, using its own reference to the SASL library.
  6. Then the server passes the authentication payload to the SASL library, which in turn passes it on to the saslauthd daemon.
  7. The saslauthd daemon passes the payload on to the LDAP service to get a YES or NO authentication response (in other words, does this user exist and is the password correct).
  8. The YES/NO response moves back from saslauthd, through the SASL library, to mongod.
  9. The mongod server uses this YES/NO response to authenticate the client or reject the request.
  10. If successful, the client has authenticated and can proceed.

Below a  visualisation of the authentication path using the LDAP connector

An example of the output when using the authentication plugin

The mongod server is running with the added option:

cat /etc/default/mongod OPTIONS="-f /etc/mongod.conf --auth --setParameter authenticationMechanisms=PLAIN,SCRAM-SHA-1" STDOUT="/var/log/mongodb/mongod.stdout"
STDERR="/var/log/mongodb/mongod.stderr" First let’s make a user in MongoDB, I’ve created an Organisational Unit and associated user with password on LDAP >
db.getSiblingDB("$external").createUser({ ... user : 'dim0', ... roles: [ {role : "read", db: 'percona'} ] ... }) Successfully added user: { "user" : "utest", "roles" : [ {
"role" : "read", "db" : "percona" } ] }

At this point the user is correctly added on MongoDB.

Let’s try and perform a read query on the database “percona”:

db.getSiblingDB("$external").auth({mechanism: "PLAIN", user: 'dim0', pwd: 'test’, digestPassword: false }) 1
use percona db.foo.find() { "_id" : ObjectId("58b3e4b80322deccc99dc763"), "x" : 1 } { "_id" : ObjectId("58b3e8fee48bdc7edeb31cc5"), "x" : 2 } { "_id" :
ObjectId("58b3e931e48bdc7edeb31cc6"), "x" : 3 } > exit

Now let’s try and write something in it:

db.foo.insert({x : 5}) WriteResult({ "writeError" : { "code" : 13, "errmsg" : "not authorized on percona to execute command { insert: "foo", documents: [ { _id:
ObjectId('58b3e97f2343c5da97a2256e'), x: 5.0 } ], ordered: true }" } })

This is logical behavior, as we only allowed read interaction on the percona database.

After a correct login, you will find the following in the mongod.log:

2017-02-27T08:55:19.612+0000 I ACCESS   [conn2] Successfully authenticated as principal dim0 on $external

If an incorrect login happens, the following entry will appear in the mongod.log:

2017-02-27T09:10:55.297+0000 I ACCESS   [conn4] PLAIN authentication failed for dim0 on $external from client 127.0.0.1:34812 ; OperationFailed: SASL step did not complete: (authentication failure)

Conclusion

Percona Server for MongoDB has an easy way of integrating correctly with SASL authd. If you are looking for an option of centrally managing the users of your MongoDB environments, look no further. Keep in mind, however, that if you don’t need a centrally managed environment adding this functionality creates additional complexity to your infrastructure. You can find additional information on the LDAP plugin in our documentation at https://www.percona.com/doc/percona-server-for-mongodb/ext_authentication/index.html.

Mar
10
2017
--

Troubleshooting MySQL access privileges issues: Q & A

MySQL access privileges

MySQL access privilegesIn this blog, I will provide answers to the Q & A for the Troubleshooting MySQL Access Privileges Issues webinar.

First, I want to thank everybody for attending the February 23 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: Should the root@localhost user be given ALL privileges or Super privileges? Does All include Super privileges also?

A: Yes, you should have a user with all privileges. Better if this user has access from localhost only.

ALL

  includes

SUPER

.

Q: We have users who connect via a laptop that get dynamic IP addresses, so granting access with a server name is an easier way to manage these users. Can I grant access to a MySQL database with a hostname as opposed to an ipaddress? For example “myname@mymachine.mydomain.com” as opposed to “myname@10.10.10.10”?  Is the host cache/performance_schema required for this?

A: Yes, you can.

But it looks like I was not clear about host cache. Host cache is an internal structure that is always available and contains answers from DNS server. You cannot enable or disable it. Until version 5.6, you also could not control it. For example, if the cache got corrupted the only thing you could do is to restart the server. Version 5.6 the table

HOST_CACHE

 was introduced to Performance Schema. With this table you can examine the content of the host cache and truncate it if needed.

Q: If there are multiple entries in the user table that match the connecting user (e.g., with wildcards, hostname, and IP), what rules does MySQL use to select which is used for authentication?  Does it try multiple ones until it gets a password match?

A: Not, mysqld does not try to hack your passwords. Instead it sorts the user table by name and host in descending order as I showed on slide #37 (page 110). Then it takes the first matching row. So if you created users

foo@somehost

,

foo@some%

 and

foo@1.2.3.4

, and you connect as foo from

somehost

, mysqld first checks the user name and then chooses the first matching row

foo@somehost

. If you instead connect as

foo

 from

someotherhost

, mysqld chooses 

foo@some%

. An IP-based host is chosen if either mysqld started with option

skip-networking

 or if

1.2.3.4

  points to a host whose name does not start with “some”.

Mixing IP-based and name-based hosts is dangerous in situations when the same host can be resolved as

somehost

 or

1.2.3.4

. In this case, if something goes wrong with the host cache or DNS server, the wrong entry from the user table can be chosen. For example, if you initially had three hosts:

uniquehost

 (which resolves as

1.2.3.4

),

somehost

 (which resolves as

4.3.2.1

) and

someothershost

 (which resolves as

4.3.2.2

). Now you decided to re-locate

uniquehost

 to a machine with IP

1.2.3.5

 and use IP

1.2.3.4

 for the host with name

someyetanotherhost

. In this case, the clients from the machine with IP

1.2.3.4

 will be treated as

foo@some%

, which isn’t what you want.

To demonstrate this issue, I created two users and granted two different privileges to them:

mysql> create user sveta@Thinkie;
Query OK, 0 rows affected (0,01 sec)
mysql> create user sveta@'192.168.0.4';
Query OK, 0 rows affected (0,00 sec)
mysql> grant all on *.* to 'sveta'@'Thinkie';
Query OK, 0 rows affected (0,00 sec)
mysql> grant all on db1.* to 'sveta'@'192.168.0.4';
Query OK, 0 rows affected (0,00 sec)

Now I modified my

/etc/hosts

  file and pointed address

192.168.0.4

  to name

Thinkie

:

127.0.0.1   localhost
# 127.0.1.1   Thinkie
192.168.0.4 Thinkie

Now, if I connect as sveta both Thinkie and 192.168.0.4 are resolved to the same host:

sveta@Thinkie:$ mysql -hThinkie -usveta
...
mysql> select user(), current_user();
+---------------+----------------+
| user()        | current_user() |
+---------------+----------------+
| sveta@Thinkie | sveta@thinkie  |
+---------------+----------------+
1 row in set (0,00 sec)
mysql> show grants;
+--------------------------------------------------+
| Grants for sveta@thinkie                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'sveta'@'thinkie' |
+--------------------------------------------------+
1 row in set (0,00 sec)
mysql> q
Bye
sveta@Thinkie:$ mysql -h192.168.0.4 -usveta
...
mysql> show grants;
+--------------------------------------------------+
| Grants for sveta@thinkie                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'sveta'@'thinkie' |
+--------------------------------------------------+
1 row in set (0,00 sec)
mysql> select user(), current_user();
+---------------+----------------+
| user()        | current_user() |
+---------------+----------------+
| sveta@Thinkie | sveta@thinkie  |
+---------------+----------------+
1 row in set (0,00 sec)
mysql> q
Bye

Now I modified the 

/etc/hosts

  file and pointed

Thinkie

  back to

127.0.0.1

  (

localhost

):

127.0.0.1   localhost
127.0.1.1   Thinkie
# 192.168.0.4 Thinkie

But host

192.168.0.4

 still resolves to

Thinkie

:

sveta@Thinkie:$ mysql -h192.168.0.4 -usveta
...
mysql> select user(), current_user();
+---------------+----------------+
| user()        | current_user() |
+---------------+----------------+
| sveta@Thinkie | sveta@thinkie  |
+---------------+----------------+
1 row in set (0,00 sec)
mysql> show grants;
+--------------------------------------------------+
| Grants for sveta@thinkie                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'sveta'@'thinkie' |
+--------------------------------------------------+
1 row in set (0,00 sec)
mysql> q
Bye

The reason for this is a stalled host cache, which can be easily observable with Performance Schema:

sveta@Thinkie:$ mysql -uroot
...
mysql> select * from performance_schema.host_cacheG
*************************** 1. row ***************************
                                        IP: 192.168.0.4
                                      HOST: Thinkie
                            HOST_VALIDATED: YES
                        SUM_CONNECT_ERRORS: 0
                 COUNT_HOST_BLOCKED_ERRORS: 0
           COUNT_NAMEINFO_TRANSIENT_ERRORS: 0
           COUNT_NAMEINFO_PERMANENT_ERRORS: 0
                       COUNT_FORMAT_ERRORS: 0
           COUNT_ADDRINFO_TRANSIENT_ERRORS: 0
           COUNT_ADDRINFO_PERMANENT_ERRORS: 0
                       COUNT_FCRDNS_ERRORS: 0
                     COUNT_HOST_ACL_ERRORS: 0
               COUNT_NO_AUTH_PLUGIN_ERRORS: 0
                  COUNT_AUTH_PLUGIN_ERRORS: 0
                    COUNT_HANDSHAKE_ERRORS: 0
                   COUNT_PROXY_USER_ERRORS: 0
               COUNT_PROXY_USER_ACL_ERRORS: 0
               COUNT_AUTHENTICATION_ERRORS: 0
                          COUNT_SSL_ERRORS: 0
         COUNT_MAX_USER_CONNECTIONS_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0
             COUNT_DEFAULT_DATABASE_ERRORS: 0
                 COUNT_INIT_CONNECT_ERRORS: 0
                        COUNT_LOCAL_ERRORS: 0
                      COUNT_UNKNOWN_ERRORS: 0
                                FIRST_SEEN: 2017-03-02 23:19:32
                                 LAST_SEEN: 2017-03-02 23:20:31
                          FIRST_ERROR_SEEN: NULL
                           LAST_ERROR_SEEN: NULL
1 row in set (0,00 sec)
mysql> truncate performance_schema.host_cache;
Query OK, 0 rows affected (0,00 sec)
mysql> q
Bye

After I truncated table

host_cache

 the numeric host resolves as I expect:

sveta@Thinkie:$ mysql -h192.168.0.4 -usveta
...
mysql> show grants;
+----------------------------------------------------------+
| Grants for sveta@192.168.0.4                             |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'sveta'@'192.168.0.4'              |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'sveta'@'192.168.0.4' |
+----------------------------------------------------------+
2 rows in set (0,00 sec)
mysql> select user(), current_user();
+-------------------+-------------------+
| user()            | current_user()    |
+-------------------+-------------------+
| sveta@192.168.0.4 | sveta@192.168.0.4 |
+-------------------+-------------------+
1 row in set (0,00 sec)
mysql> q
Bye

Q: What privileges are required for a non-root or non-super user to be to use mysqldump to dump the database and then restore it on a different server?

A: Generally you should have

SELECT

  privilege on all objects you are going to dump. If you are dumping views, you also should have

SHOW VIEW

  privilege in order to run

SHOW CREATE TABLE

. If you want to dump stored routines/events, you need access to them as well. If you use option

--lock-tables

or

--lock-all-tables

, you should have the 

LOCK

  privilege.

Q: If the max_connection value is reached in MySQL, can root@localhost with ALL privilege still login, or with Super privilege user can login?

A:

ALL

 includes

SUPER

, so a user with

ALL

  privilege can login. Just note there can be only one such connection, thus do not grant

SUPER

 

or ALL

 privilege to the application user.

Q: Is it possible to remove a priv at a lower level? In other words, grant select and delete at the database level, but remove delete for a specific table?  Or can privs only be added to?

A: Not, MySQL will reject such a statement:

mysql> show grants for sveta@'192.168.0.4';
+----------------------------------------------------------+
| Grants for sveta@192.168.0.4                             |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'sveta'@'192.168.0.4'              |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'sveta'@'192.168.0.4' |
+----------------------------------------------------------+
2 rows in set (0,00 sec)
mysql> revoke update on db1.t1 from sveta@'192.168.0.4';
ERROR 1147 (42000): There is no such grant defined for user 'sveta' on host '192.168.0.4' on table 't1'

Q: How can we have DB user roles… like a group of grants for a particular role?

A: You have several options.

  1. Use MariaDB 10.0.5 or newer. You can read about roles support in MariaDB here
  2. Use MySQL 8.0. You can read about roles in MySQL 8.0 here
  3. With MySQL 5.7: imitate roles as I showed on slide 19 (pages 53 – 60)
  4. With MySQL 5.5 and 5.6: use the same method as shown on slides, but use the custom authentication plugin that supports proxy users.
  5. Always: create a template with privileges, assign privileges to each user manually.

Q: How would you migrate role simulation with proxy users to actual roles in MySQL 8.x?

A: I would drop the proxied user and create a role with the same privileges instead, then grant the proxy user the newly created role instead of

PROXY

.

Q: Is there a plugin to integrate Active Directory and MySQL in order to use Active Directory groups?

A: There is commercial Windows Authentication Plugin, available in versions 5.5 and newer. You can also use the open source Percona PAM authentication plugin and connect it to Active Directory the same way as can be done for LDAP. There is a blog post describing how to do so, but I’ve never used this method myself.

Q: Can we use central auth with MySQL?

A: Yes, with the help of the PAM Plugin. There are tutorials for LDAP and Active Directory. You may use similar methods to setup other kinds of authentications, such as Kerberos.

Mar
06
2017
--

MySQL, –i-am-a-dummy!

--I-am-a-dummyIn this blog post, we’ll look at how “operator error” can cause serious problems (like the one we saw last week with AWS), and how to avoid them in MySQL using

--i-am-a-dummy

.

Recently, AWS had some serious downtime in their East region, which they explained as the consequence of a bad deployment. It seems like most of the Internet was affected in one way or another. Some on Twitter dubbed it “S3 Dependency Awareness Day.”

Since the outage, many companies (especially Amazon!) are reviewing their production access and deployment procedures. It would be a lie if I claimed I’ve never made a mistake in production. In fact, I would be afraid of working with someone who claims to have never made a mistake in a production environment.

Making a mistake or two is how you learn to have a full sense of fear when you start typing:

UPDATE t1 SET c1='x' ...

I think many of us have experienced forehead sweats and hand shaking in these cases – they save us from major mistakes!

The good news is that MySQL can help you with this. All you have to do is admit that you are human, and use the following command (you can also set this in your user directory .my.cnf):

mysql --i-am-a-dummy

Using this command (also known as safe-updates) sets the following SQL mode when logging into the server:

SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;

The safe-updates and iam-a-dummy flags were introduced together in MySQL 3.23.11, and according to some sites from around the time of release, it’s “for users that once may have done a DELETE FROM table_name but forgot the WHERE clause.”

What this does is ensure you can’t perform an UPDATE or DELETE without a WHERE clause. This is great because it forces you to think through what you are doing. If you still want to update the whole table, you need to do something like WHERE ID > 0. Interestingly, safe-updates also blocks the use of WHERE 1, which means “where true” (or basically everything).

The other safety you get with this option is that SELECT is automatically limited to 1000 rows, and JOIN is limited to examining 1 million rows. You can override these latter limits with extra flags, such as:

--select_limit=500 --max_join_size=10000

I have added this to the .my.cnf on my own servers, and definitely use this with my clients.

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