CHEF - Sequel - RuntimeError: unknown privilege type SYSDBA

I explained in my previous post how to install Sequel Ruby gem to interact with Oracle databases in CHEF. However, there's little documentation on Sequel's website about connecting to Oracle databases.

I had two issues, (1) I couldn't figure out the connection string pattern for the database and (2) Once I did, I had issues connecting to the database using elevated privileges (e.g. connecting as SYSDBA).

Issue 1
The correct format for Oracle database is, you must quote the whole thing.
DB = Sequel.connect('oracle://user:password@hostname:port/service_name')
e.g.
DB = Sequel.connect('oracle://scott:tiger@oel1.mydomain.com:1523/orcl.mydomain.com')

Issue 2
If you want to connect with SYS user, the above syntax needs to be changed to

DB = Sequel.connect('oracle://sys:mypassword@oel1.mydomain.com:1523/orcl.mydomain.com', :privilege => :SYSDBA)

CHEF - Using an Oracle Database

There are a lot of cookbooks that let you run queries/operations against open source databases (MySQL/PostgreSQL etc). I wanted to use something to deal with Oracle database. I found an old blog on Oracle's website that shows few examples of this integration using oci8. But oci8 works specifically for Oracle and while it solved my problem, I couldn't use it for other databases. A friend recommended me Sequel which I found really useful. But using Oracle with Sequel still requires you to install and configure oci8. Here's the process:

Example below is for configuring on Mac OS. See footnotes for Linux.

Step 1
Install Ruby gem oci8. To install oci8, you must have at a minimum Oracle Instant client and SDK. Download them from Oracle's website.

Step 2
Copy the zip files to the homebrew library folder. If you are not using homebrew, you should!
ls /Library/Caches/Homebrew
instantclient-sdk-macos.x64-11.2.0.4.0.zip
instantclient-basic-macos.x64-11.2.0.4.0.zip

Step 3
Install the packages using homebrew.

brew install InstantClientTap/instantclient/instantclient-basic
==> Tapping instantclienttap/instantclient
Cloning into '/usr/local/Library/Taps/instantclienttap/homebrew-instantclient'...
Tapped 4 formulae (43 files, 37.9K)
==> Installing instantclient-basic from instantclienttap/instantclient
==> Downloading http://download.oracle.com/otn/mac/instantclient/11204/instantclient-basic-macos.x64-11.2.0.4.0.zip
Already downloaded: /Library/Caches/Homebrew/instantclient-basic-macos.x64-11.2.0.4.0.zip
==> /usr/bin/install_name_tool -id /usr/local/lib/libclntsh.dylib.11.1 /usr/local/Cellar/instantclient-basic/11.2.0.4.0/lib/libclntsh.dylib
.... [output clipped]
🍺  /usr/local/Cellar/instantclient-basic/11.2.0.4.0: 8 files, 181.0M, built in 3 seconds
/Library/Caches/Homebrew >

brew install InstantClientTap/instantclient/instantclient-sdk
==> Installing instantclient-sdk from instantclienttap/instantclient
==> Downloading http://download.oracle.com/otn/mac/instantclient/11204/instantclient-sdk-macos.x64-11.2.0.4.0.zip
Already downloaded: /Library/Caches/Homebrew/instantclient-sdk-macos.x64-11.2.0.4.0.zip
🍺  /usr/local/Cellar/instantclient-sdk/11.2.0.4.0: 40 files, 1.9M, built in 0 seconds
/Library/Caches/Homebrew >

Step 4
Install the oci8 Ruby gem, in this case I want to use it with chef so I am using chef's syntax to install the gem, it will work for just ruby too (without preceding the command with chef)

chef gem install ruby-oci8
Building native extensions.  This could take a while...
Successfully installed ruby-oci8-2.2.2
1 gem installed

Step 5
Install the Sequel Ruby gem.

chef gem install sequel
Fetching: sequel-4.34.0.gem (100%)
Successfully installed sequel-4.34.0
1 gem installed

That's it! You should now be able to use Sequel gem to query Oracle databases.

Important note for Linux:
If you want the CHEF recipes to work on your target systems (say Linux), these packages must be installed on them. The problem is that if you attempt to install these gems using chef_gem package in a recipe, the oci8 gem will fail to execute unless you set the LD_LIBRARY_PATH which at the time of writing I couldn't figure out how to. So as a workaround, I installed the Oracle instant client packages (rpm) manually, set the LD_LIBRARY_PATH to where libclntsh.so file was. In my case it was export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib and then installed the gems using the CHEF recipe. Make sure you are in the same session or the LD_LIBRARY_PATH is set at the profile level.

CHEF - ArgumentError: could not find a temporary directory

When running chef-client as non root user, you may encounter the following error:

==============================
  Error Syncing Cookbooks:
  =============================
  Unexpected Error:
  -----------------
  ArgumentError: could not find a temporary directory
 
  Platform:
  ---------
  x86_64-linux

In the stacktrace, the error shows up as:
ArgumentError: could not find a temporary directory
/opt/chef/embedded/lib/ruby/2.1.0/tmpdir.rb:34:in `tmpdir'
/opt/chef/embedded/lib/ruby/2.1.0/tmpdir.rb:105:in `tmpdir'
/opt/chef/embedded/lib/ruby/2.1.0/tmpdir.rb:137:in `create'
/opt/chef/embedded/lib/ruby/2.1.0/tempfile.rb:136:in `initialize'
/opt/chef/embedded/lib/ruby/2.1.0/tempfile.rb:320:in `new'
/opt/chef/embedded/lib/ruby/2.1.0/tempfile.rb:320:in `open'

Solution:
This is not a CHEF issue rather a ruby one. Newer versions of ruby do not allow any directory to be used as a temporary directory that has (777) permissions set on it unless setuid of 1 is set on the directory too. As root user do the following:

chmod 1777 /tmp
ls -ld /tmp  drwxrwxrwt. /tmp

notice the "t" in the output.

CHEF - Running chef-client as non-root user

Chef works best when run as root user but there are scenarios when you would want to run a chef-client as non root user. To do that, do the following steps:

Assumption:
It is assumed that you already have bootstrapped the node and/or installed the chef-client.  Also, it assumes that you have access to the client.pem key needed to talk to the CHEF server. For this example, we'll assume the non root user is "oracle".

Step 1:
As root, copy the entire content of /etc/chef folder to "oracle" user's home directory and change permissions.
cp -rf  /etc/chef /home/oracle/.chef
chown -R oracle:oinstall /home/oracle/.chef

Step 2:
Open the /home/oracle/.che/client.rb file and add the following lines:
cache_path "/home/oracle/.chef"
client_key "/home/oracle/.chef/client.pem"
 
If you don't specify cache_path, you will get the error:
Unable to access cache at /var/chef. Switching cache to /home/oracle/.chef
whenever you run the chef-client as oracle user.

If you don't specify the client_key location, chef-client will look at /etc/chef/client.pem file, if the file is not readable (by default it won't), the client will error out with

Private Key Not Found:
----------------------
Your private key could not be loaded. If the key file exists, ensure that it is
readable by chef-client.

Relevant Config Settings:
-------------------------
validation_key "/etc/chef/validation.pem"

Step 3:
Now, run the chef-client with the following arguments:
chef-client -o recipe[] -c /home/oracle/.chef/client.rb

CHEF - Uninstalling CHEF Server

Once installed, it can be tricky to cleanup a CHEF server install. The guys at CHEF have updated their docs (https://docs.chef.io/uninstall.html) with instructions on how to deinstall CHEF server but I found that it does not cleans up the system entirely.

Note: Running these steps will completely wipe out all configurations you have, so proceed with caution.

I followed the following steps to remove CHEF server on my RHEL machine.

1. As mentioned in the CHEF docs, run
chef-server-ctl uninstall
This step will first stop all the processes and then run a cleanup.

2. If you do a grep on the running processes, you'll notice the EPM daemon would still be running. Kill it.
ps -ef|grep chef
/opt/opscode/embedded/service/opscode-chef-mover/erts-6.4/bin/epmd -daemon
kill -9 "process id of the epm daemon"

3. Now remove the CHEF packages. You can use whatever package manager you use, I use yum.
yum remove chef-*

Even if you did not install the CHEF package using yum, this will remove the package.

4. Cleanup all the folders.
rm -rf /opt/opscode*
rm -rf /opt/chef-manage
rm -rf /var/opt/opscode*
rm -rf /etc/chef

5. Restart the host.
init 6