Database Management is an important part when you have a large amount of data around you. MySQL is one of the most famous open-source Relational Databases to store & handle your data.
Deploy Sample PHP/MySQL Web application
We will create two deployments; one for the webserver and the other for MySQL DB. The web server will read data from MySQL DB and show it in the browser.
Here we used the GKE environment for the application deployment.
[Step #1] Clone GitHub Repo:
kubectl apply -f https://raw.githubusercontent.com/accuknox/microservices-demo/main/php-mysql-webapp/webserver.yaml
kubectl get pods in the terminal to get the response:
Alright, the pod is created, but we can't access it despite having its IP; the reason is that the Pod IP is not public. So we use service. When a user tries to access an app, for instance, a web server here, it requests a service that checks where it should forward the request. You will access the IP and port defined in the service configuration file to access the webserver.
[Step #3] Now, let's deploy web-service:
kubectl apply -f https://raw.githubusercontent.com/accuknox/microservices-demo/main/php-mysql-webapp/webserver-svc.yaml
Check the status of the service:
kubectl get svc
[Step #4] Create the persistent volume claim to keep your data intact.
kubectl apply -f https://raw.githubusercontent.com/accuknox/microservices-demo/main/php-mysql-webapp/mysql-pv-claim.yaml
[Step #5] Create deployment and service for MySQL DB.
kubectl apply -f https://raw.githubusercontent.com/accuknox/microservices-demo/main/php-mysql-webapp/mysql.yaml kubectl apply -f https://raw.githubusercontent.com/accuknox/microservices-demo/main/php-mysql-webapp/mysql-svc.yaml
Check the status of the pod and service.
kubectl get po,svc
Now the application is deployed. You can insert data into the database in two ways. You can use a MySQL Client or directly execute to the MySQL server pod.
Connect using a MySQL client:
kubectl run -it --rm --image=mysql:5.6 --restart=Never mysql-client -- mysql -h mysql8-service -p.sweetpwd.
Directly executing into MySQL pod:
kubectl exec -it mysql-796674bfb-dl495 -- bash
mysql-796674bfb-dl495with your mysql pod name.
Now you are inside MySQL pod. Use the below command to enter mysql command prompt.
mysql -u root -p.sweetpwd.
Now you are inside the MySQL terminal. First, you need to create a
users table and add values to the table.
CREATE TABLE users ( name varchar(20) );
INSERT INTO users (name) VALUES ('John');
Now check the external IP of the
web service. If everything goes fine, then this screen should be seen.
Audit your MySQL Server Sensitive Configuration files with Kubearmor
MySQL Server, also known as mysqld, is a single multithreaded program that does most of the work in a MySQL installation. It does not spawn additional processes. MySQL Server manages access to the MySQL data directory that contains databases and tables. The data directory is also the default location for other information such as log files and status files.
The default configuration file is called my.cnf and can be located in several directories. On Linux and other Unix related platforms, the locations are using /etc/my.cnf, /etc/mysql/my.cnf, /var/lib/mysql/my.cnf or in the default installation directory. This file contains configuration settings that will be loaded when the server starts, including settings for the clients, server, mysqld_safe wrapper and various other MySQL client programs.
However, if they're not there, you can use
mysqld to find the configuration. Run the following command inside the MySQL server pod.
mysqld --help --verbose
The first part of the lengthy response describes the options you can send to the server when you launch it. The second part displays the configuration settings during the server compilation.
Near the start of the output, find a couple of lines that look similar to the following example:
Starts the MySQL database server. Usage: mysqld [OPTIONS] Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf The following groups are read: mysqld server mysqld-8.0
This file is created when there is an existing my.cnf file and the mysql_install_db script is run. The mysql_install_db script is designed to develop the my.cnf file if it does not exist. If the file does exist, then the file is created using the name my-new.cnf to avoid overwriting an existing configuration file. It is then up to the user to compare the two, determine files and determine which options are still valid, for the new install and change the files as required to get the new my.cnf configuration file.
By default, MySQL stores its log files in the following directory:
Check the MySQL configuration if you don't find the MySQL logs in the default directory. View the my.cnf file and look for a
log_error line, as in:
log_error = /var/log/mysql/error.log
The main two options are to copy the database files or use
By default, MySQL creates a directory for each database in its data directory, /var/lib/mysql.
Ensure you set the permissions on that file to restrict read access for password-security reasons.
Another approach to backing up your database is to use the
mysqldump tool. Rather than copying the database files directly,
mysqldump generates a text file that represents the database. By default, the text file contains a list of SQL statements to recreate the database, but you can also export the database in another format like .CSV or .XML. You can read the man page for
mysqldump to see all its options.
The statements generated by
mysqldump go straight to standard output. You can specify a to redirect the output by running the following command in the command line:
$ mysqldump -u root -p demodb > dbbackup.sql
This command tells
mysqldump to recreate the demodb database in SQL statements and to write them to the file dbbackup.sql. Note that the username and password options function the same as the MySQL client to include the password directly after
-p in a script.
With the help of Kubearmor, You can audit/restrict all these sensitive configuration files and processes that use these files. If you know the locations of files and processes you can create policy at ease.
Following Kubearmor policy will Audit configuration files and block
# KubeArmor is an open source software that enables you to protect your cloud workload at run-time. # To learn more about KubeArmor visit: # https://www.accuknox.com/kubearmor/ apiVersion: security.kubearmor.com/v1 kind: KubeArmorPolicy metadata: name: ksp-restrict-access-mysql-server-config namespace: default # Change your namespace spec: tags: ["STIGS", "MYSQL"] message: "Alert! mysql configuration files access has been accessed" selector: matchLabels: app: mysql8 # Change your labels file: severity: 5 matchPaths: - path: /etc/mysql/my.cnf ownerOnly: true matchDirectories: - dir: /etc/mysql/ recursive: true ownerOnly: true - dir: /var/lib/mysql/ readOnly: true recursive: true - dir: /var/log/mysql/ recursive: true action: Audit process: severity: 10 matchPaths: - path: /usr/bin/mysqldump action: Block
Apply Kubearmor Security Policy (KSP):
kubectl apply -f https://raw.githubusercontent.com/accuknox/microservices-demo/main/php-mysql-webapp/ksp-audit-mysql-config-files.yaml
Try to use
kubectl exec -it mysql-7d9977c67d-7rcmb -- bash
[email protected]:/# mysqldump -u db_user -p .mypwd my_db users > dumpfilename.sql
/etc/mysql/my.cnf config. file;
[email protected]:/# cat /etc/mysql/my.cnf
Kubearmor detects this event, and we will receive logs like this:
Securing configuration files are a necessity for any application. With Kubearmor you can effectively do that and with the options like
recursive,matchDirectoriesetc you can fine-tune the policy enforcement.
Protect Using Auto Discovered Policies
Accuknox policy auto-discovery engine leverages the pod visibility provided by KubeArmor and Cilium to auto-generate network and system policies.
Policy Manager ->
Auto Discovered Policies
We deployed our sample application on the default namespace. Check
default namespace for policies
Following are the auto-discovered network policies generated by Accuknox. Let's briefly explain the policies.
apiVersion: cilium.io/v2 kind: CiliumNetworkPolicy metadata: name: autopol-ingress-anvyjxzgmiqcyws spec: description: Auto Discovered Policy endpointSelector: matchLabels: app: mysql8 ingress: - fromEndpoints: - matchLabels: app: apache k8s:io.kubernetes.pod.namespace: default toPorts: - ports: - port: "3306" protocol: TCP
This policy will be enforced at the ingress (against the inbound network flows) of the MySQL pod (pods labelled with
app: mysql8 will be picked).
This enables endpoints with the label
app: apache and
k8s:io.kubernetes.pod.namespace: default to communicate with all endpoints with the label
app: mysql8, but they must share using TCP on port 3306.
Endpoints with other labels will not communicate with the MySQL pod.
apiVersion: cilium.io/v2 kind: CiliumNetworkPolicy metadata: name: autopol-egress-nqfbfchvptougbs spec: description: Auto Discovered Policy endpointSelector: matchLabels: app: apache egress: - toEndpoints: - matchLabels: app: mysql8 k8s:io.kubernetes.pod.namespace: default toPorts: - ports: - port: "3306" protocol: TCP
This policy is very similar to the first policy. This will be enforced at the webserver pod's egress (against the outbound network flows) (pods labelled with the
app: apache will be picked).
apiVersion: cilium.io/v2 kind: CiliumNetworkPolicy metadata: name: autopol-egress-xkvuxpmcxgtpacv spec: description: Auto Discovered Policy endpointSelector: matchLabels: app: apache egress: - toEndpoints: - matchLabels: k8s-app: kube-dns k8s:io.kubernetes.pod.namespace: kube-system toPorts: - ports: - port: "53" protocol: UDP
This one is the DNS policy for the
Auto-discovered policies are generated based on the network flow of the sample application. This will restrict all unwanted connections and reduce the application's attack surface. It allows only minimum traffic that the application needs to operate.
AccuKnox provides out-of-the-box recommended policies to secure the cloud environment. The policy-templates open-source repository provides policy templates based on KubeArmor and Cilium for known CVEs, attack vectors, compliance frameworks (such as MITRE, PCI-DSS, STIG, etc.) and popular workloads. You can also handcraft your security policies to secure your MySQL cluster.