Author: Michiel

Convert certificate file (.cer/.crt) to PFX

Sometimes you need a .pfx style certificate for whatever purpose, this is how you create it:

openssl pkcs12 -export -in website.cer -inkey website.key -out website.pfx

You will be asked for a password, this is recommended because otherwise the key can be abused without any effort if someone has found your .pfx file.

website.cer is an example, change it with your own file.

Show MySQL client history

Like most administrators I like to execute MySQL queries direct from the MySQL commandline.

But if you want to review whatever you entered in the client it is sometimes difficult to find.

However: All commands entered are also saved in your homedir from the Linux user you are logged in from.

cat ~/.mysql_history

The result is a complete list of commands you have used:

\q
show slave status\G
show master status;
show databases;
use testdb
INSERT INTO users(name) VALUES ('bla');
\q

PowerDNS AXFR transfer import

Lately I have been pretty busy with large DNS migration projects, and fortunately PowerDNS has created a very handy tool: zone2sql

Assume you can do an AXFR transfer (dig AXFR @<ip from old server> example.com), the output you get here you can convert to a MySQL query for example.

With this generated MySQL query you can import the whole zone without any trouble into the new database backend of the new server.

Prerequisites:

1. A working PowerDNS authoritative server

2. A MySQL/MariaDB backend

3. AXFR tranfer possibility

Steps:

1. Get the zone information: (in my test case the old nsauth server is 10.0.0.1)

dig AXFR @10.0.0.1 example.com > example.com.txt

2. Examine the output:

cat example.com.txt

; <<>> DiG 9.9.4-RedHat-9.9.4-14.el7 <<>> AXFR @10.0.0.1 example.com
; (1 server found)
;; global options: +cmd
example.com.             3600    IN      SOA     nsauth1.example.com. postmaster.example.com. 2015041601 43200 3600 3600000 86400
example.com.             3600    IN      NS      nsauth1.example.com.
example.com.             3600    IN      MX      10 mx1.example.com.
www.example.com.         86400   IN      A       10.0.0.100
example.com.             3600    IN      NS      nsauth2.example.com.
example.com.             3600    IN      SOA     nsauth1.example.com. postmaster.example.com. 2015041601 43200 3600 3600000 86400
;; Query time: 3 msec
;; SERVER: 10.0.0.1#53(10.0.0.1)
;; WHEN: ma jun 22 16:23:51 CEST 2015
;; XFR size: 6 records (messages 3, bytes 315)

3. With the zone2sql tool, convert the above information to a MySQL query:

zone2sql --gmysql --zone-name=example.com --zone=example.com.txt > example.com.sql

The output written to example.com.sql is like this:

insert into domains (name,type) values (example.com','NATIVE');
insert into records (domain_id, name, type,content,ttl,prio,disabled) select id ,'example.com', 'NS', 'nsauth1.example.com', 3600, 0, 0 from domains where name='example.com';

etc

4. Now import the generated .sql file in the MySQL/MariaDB backend server from commandline (assume the backend is running locally):

mysql -u root -p pdns < example.com.sql

After this is completed the new zone including all records is now available in the new authortitative server.

The PowerAdmin tool is very helpful (http://www.poweradmin.org/) and recently I managed to enable the LDAP user function which is very handy.

Set or reset password for MySQL user

If you want to reset or set a password for an already created user, you have 3 options:

  • Set the password with a current password encryption/hash:
SET PASSWORD FOR 'mysqluser'@'localhost' = PASSWORD('newpassword');
  • Set the password with an old password encryption/hash:
SET PASSWORD FOR 'mysqluser'@'localhost' = OLD_PASSWORD('newpassword');
  • Set the password direct with a predefined hash:
SET PASSWORD FOR 'mysqluser'@'localhost' = '*67BECF85308ACF0261750DA1075681EE5C412F05';

After setting the new password:

flush privileges;

Set link status down for network interface

I Have encountered several situations where an “ifdown eth0” didn’t work, this command responded with an error or did nothing at all.

However, the command:

ip link set eth0 down

Did always work. 🙂

One of these situations were when configuring a teaming interface while the network cables were connected. When configuring a teaming interface, the slave interfaces must be in “down” state.

SSH key login without password

Create a public SSH key on your local server:

ssh-keygen -t rsa

Copy the public SSH key to the remote server

ssh-copy-id -i ~/.ssh/id_rsa.pub remote-host

After this is completed you can login with the existing username (of course on both servers) without a password.
For extra security you can set a password on your key file when you create it with the first command.

Ubuntu realmd configuration

Install packages:

apt install realmd sssd adcli -y

Try if you can discover the Active Directory domain:
realm discover domain.local

Join the domain:
realm join domain.local -U admin.username

Add access group to be able to login with SSH:
realm permit -g 'gp-linux-admins'

Replace the current/generated sssd.conf:
cat << EOF > /etc/sssd/sssd.conf
[sssd]
domains = domain.local
config_file_version = 2
services = nss, pam

[domain/domain.local]
default_shell = /bin/bash
krb5_store_password_if_offline = True
cache_credentials = True
krb5_realm = DOMAIN.LOCAL
realmd_tags = manages-system joined-with-adcli
id_provider = ad
fallback_homedir = /home/%u@%d
ad_domain = domain.local
use_fully_qualified_names = True
ldap_id_mapping = True
access_provider = ad
use_fully_qualified_names = False
simple_allow_groups = gp-linux-admins
EOF

Restart SSSD:
systemctl restart sssd

Enable automatic creation of homedirectory for the user:
cat << EOF >> /etc/pam.d/common-session
session optional pam_mkhomedir.so skel=/etc/skel umask=077
EOF

Remove file by inode (for hard to remove files)

It is sometimes difficult to remove a file which has special characters in the filename.

In these occasions it is the easiest to remove the file by it’s inode.

To find out what inode (number) is linked to what file enter:

ls -li

The result is something like this:

51249160 drwxr-xr-x  2 user group  4096 aug 22 16:16 Desktop
51249164 drwxr-xr-x  5 user group  4096 aug 18 23:04 Documents
51249161 drwxr-xr-x 12 user group 16384 sep  3 17:56 Downloads
54012956 -rw-rw-r--  1 user group     0 sep  3 18:14 file_to_delete.txt

The first column is the inode part, if you want to delete a file, you’ll need that number.

Now let’s remove the file: file_to_delete.txt

michiel@desktop:~$ find . -inum 54012956 -exec rm -i {} \;
rm: remove regular empty file ‘./file_to_delete.txt’? y

You will see, Linux detects the filename from the inode number, this way you can check if the file is really the file you want to delete.

Use at your own risk.

Destroy all content of the disk (shred)

Since a couple of years there is a brilliant tool included in Debian/Ubuntu distro’s: shred

This tool will write random stuff on your harddisk which makes it harder to recover.

There are some nice options to write multiple times over the previous data.

This is something I use:

shred -n2 -v -z /dev/sde

-n2: This will write 2 times over the previous data
-v: Verbose, so you can monitor the process
-z: Zero all previous writes out to hide the shred.
/dev/sde: Your device you want to erase, replace with your own!

Of course with very expensive hardware there are some parts recoverable, but for normal use I think it’s fine.

Use at your own risk!

Create SSH banner per user

I Wanted to show information after logging in on a server (with SSH) specific for a certain user.

For example I have a local user with the username “admin”.

Put in the /etc/ssh/sshd_config the following line:

Match user admin
        Banner /etc/banner_admin

Restart the SSH daemon:

service sshd restart

Now after the user “admin” logs in (after the user enters the password) the user sees the banner you created in /etc/banner_admin