Author Archives: Martin Bach

About Martin Bach

Oracle DBA and Linux enthusiast, part time author and presenter.

Putty-like SSH port forwarding on Linux and MacOS

As a Linux or Mac user you benefit from a very useful, built-in terminal and SSH client implementation that’s mostly identical across all Unix-like systems. The situation used to be different on Windows.

Before Windows supported a built-in SSH client on the command line Putty was (and still is!) one of the primary tools available to perform remote administration. One of the nice things in Putty is its ability to add port forwarding rules on the fly, e.g. after the session has already been established. A similar feature exists for SSH clients on MacOS and Linux (and even Windows as its ssh client is also based on OpenSSH)

Port-forwarding in openSSH clients

The contents of this post was tested with a wide range of SSH clients. I did not go so far as to research when dynamic port forwarding was introduced but it seems to be present for a little while. For the most part I used the SSH client shipping with Oracle Linux 8.6.

Port-forwarding at connection time

You can specify either the -L or -R flag (and -D for some fancy SOCKS options not relevant to this post) when establishing a SSH session to a remote host, specifying how ports should be forwarded. Throw in the -N flag and you don’t even open your login shell! That’s a very convenient way to enable port forwarding. As long as the command shown below isn’t CTRL-C’d the SSH tunnel will persist.

[martin@host]$ ssh -i ~/.ssh/vagrant -N -L 5510:server2:5510 vagrant@server2

Occasionally I don’t know in advance which ports I have to forward, and I’m not always keen to establish a new session. Wouldn’t it be nice if you could simply add a port forwarding rules just like with Putty?

Putty-like port-forwarding on the command line

Once established you can control the behaviour of your SSH session using escape characters. The ssh(1) man page lists the available options in a section titled “ESCAPE CHARACTERS” (yes, the man page lists it in uppercase, it wasn’t me shouting).

The most interesting escape key is ~C: it opens a command line. I’m quoting from the docs here:

[~C] Open command line. Currently this allows the addition of port forwardings using the -L, -R and -D options (see above). It also allows the cancellation of existing port-forwardings with -KL[bind_address:]port for local, -KR[bind_address:]port for remote and -KD[bind_address:]port for dynamic port-forwardings. !command allows the user to execute a local command if the PermitLocalCommand option is enabled in ssh_config(5). Basic help is available, using the -h option.

man ssh(1)

Let’s try this in practice. Let’s assume I’d like to use port-forwarding to tunnel the Oracle Enterprise Manager (EM) Express port for one of my Pluggable Databases (PDBs) to my local laptop. The first step is to establish the port number used by EM Express.

SQL> show con_name

CON_NAME
------------------------------
PDB1

SQL> select dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT
------------
	5510

Right, the port number is 5510! It’s above the magic number of 1024 and therefore not a protected port (only root can work with ports <= 1024). Let’s add this to my existing interactive SSH connection:

[vagrant@server2 ~]$      # hit ~ followed by C to open the command line
ssh> L5510:server2:5510   # add a local port forwarding rule
Forwarding port.

As soon as you see the message “Forwarding port” you are all set, provided of course the ports are defined correctly and there’s no service running on your laptop’s port 5510. Next, when I point my favourite web browser to https://localhost:5510/em the connection request is forwarded to server2’s port 5510. In other words, I can connect to Enterprise Manager Express.

Should you find yourself in a situation where you’re unsure which ports you have forwarded, you can find out about that, too. Escape character ~# displays currently forwarded ports:

[vagrant@server2 ~]$ ~#
The following connections are open:
  #0 client-session (t4 r0 i0/0 o0/0 e[write]/4 fd 4/5/6 sock -1 cc -1 io 0x01/0x01)
  #3 direct-tcpip: listening port 5510 for server2 port 5510, connect from 127.0.0.1 port 58950 to 127.0.0.1 port 5510 (t4 r1 i0/0 o0/0 e[closed]/0 fd 9/9/-1 sock 9 cc -1 io 0x01/0x00)

Your client session is always present as #0. In the above output #3 indicates my browser session I established to EM Express. Unfortunately the forwarded port is only shown after an initial connection was established. This is close to Putty’s behaviour, but not a match. If you really need to know you have to use lsof or netstat and related tools.

You can even stop forwarding sessions on the command line:

[vagrant@server2 ~]$ 
ssh> KL5510
Canceled forwarding.

Once all sessions previously using the forwarded port have ended, the information is removed from the output of ~# in ssh.

Summary

The ssh command line client offers quite a few options not many users are aware of. Dynamically adding port forwarding rules to a session is a great feature I use frequently. Although it’s not quite on par with Putty’s port forwarding options dialogue it’s nevertheless very useful and I find myself mainly adding forwarding rules. The sshd (= server) configuration must of course allow port forwarding for this to work, if port forwarding fails because the admin disabled it you’ll get a message similar to this on in your ssh session:

[vagrant@server2 ~]$ channel 3: open failed: administratively prohibited: open failed

In which case you are out of luck.

Advertisement

Podman secrets: a better way to pass environment variables to containers

Podman became the standard container runtime with Oracle Linux 8 and later, and I have become a fan almost instantly. I especially like the fact that it is possible to run containers with far fewer privileges than previously required. Most Podman containers can be started by regular users, a fact I greatly appreciate in my development environments.

A common technique of passing information to containers is to use the –env command line argument when invoking podman run. Since passing sensitive information on the command line is never a good idea I searched for alternatives for a local development environment. Podman secrets are a very promising approach although not without their own issues. Before you consider using them please ensure your security department signs their use off, as always. They are pretty easy to translate back into plain text if you have access to the container host!

Podman Secrets

Podman secrets provide an alternative way for handling environment variables in containers. According to the documentation,

A secret is a blob of sensitive data which a container needs at runtime but should not be stored in the image or in source control, such as usernames and passwords, TLS certificates and keys, SSH keys or other important generic strings or binary content (up to 500 kb in size).

Why is this a big deal for me? You frequently find instructions in blog posts and other sources how to pass information to containers, such as user names and passwords. For example:

$ podman run --rm -it --name some-container \
-e USERNAME=someUsername \
-e PASSWORD=thisShouldNotBeEnteredHere \
docker.io/…/

Whilst specifying usernames and passwords on the command line is convenient for testing, passing credentials this way is not exactly secure. Quite the contrary. Plus there is a risk these things make it into a source control system and thus become publicly available.

An alternative to passing information to containers is by using Podman secrets. This way deployment and configuration can be kept separately.

Podman Secrets in Action

Secrets are separate entities, you create them using podman secret create (documentation link). They work quite well, provided they do not contain newlines. A little application using Oracle’s node driver to connect to an XE database and printing some connection details serves as an example. The example assumes that an XE 21c container database has been started. An “application account” has been created, and the password to this account is stored locally as a secret named oracle-secret.

The “application” is rather simple, it is based on the getting started section of the node-oracledb 5.5 driver documentation.

$ cat app.mjs 
import oracledb from 'oracledb';

(async() => {

  let connection;

  try {
    connection = await oracledb.getConnection( {
      user          : process.env.USERNAME,
      password      : process.env.PASSWORD,
      connectString : process.env.CONNECTSTRING
    });

    const result = await connection.execute(`
      select
        sys_context('userenv', 'instance_name') instance, 
        sys_context('userenv', 'con_name') pdb_name, 
        user
       from dual`,
      [],
      {
        outFormat: oracledb.OUT_FORMAT_OBJECT
      }
    );
    
    for (let r of result.rows) {
      console.log(`you are connected to ${r.INSTANCE}, PDB ${r.PDB_NAME} as ${r.USER}`);
    }

  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
})();

The important bit is right at the top: the Connection object is created using information provided via environment variables (USERNAME, PASSWORD, CONNECTSTRING). I locked the oracledb driver version in package.json at version 5.5.0, the most current release at the time of writing.

Running the Container

After building the container image, the container can be started as follows:

$ podmanrun --rm -it \
--net oracle-net \
--name some-node-app \
-e USERNAME=martin \
-e CONNECTSTRING="oraclexe:/xepdb1" \
--secret oracle-secret,type=env,target=PASSWORD \
localhost/nodeapp:0.1

Translated into plain English this commands starts an interactive, temporary container instance with an attached terminal for testing. The container is instructed to connect to the oracle-net network (a Podman network). A couple of environment variables are passed to the container: USERNAME and CONNECTSTRING. The use of the secret requires a little more explanation. The (existing) secret oracle-secret is passed as an environment variable (type=env). If it weren’t for the target=PASSWORD directive the secret would be accessible in the container by its name oracle-secret. Since I need an environment variable named PASSWORD (cf app.mjs above) I changed the target name to match. Once the container is up an environment variable named PASSWORD is available.

The new container should connect to the database and print something along the lines of:

you are connected to XE, PDB XEPDB1 as MARTIN

Summary

Podman secrets allow developers to provide information that shouldn’t be part of a container image or (configuration) code to containers. Provided the secrets are set up correctly they provide a much better way of passing sensitive information than hard-coded values on the command line. I said they provide a better way, but whilst secrets are definitely a step in the right direction they aren’t perfect (read: secure).

Whenever touching the point of sensitive information the advice remains the same: please have someone from the security team review the solution and sign it off before going ahead and using it. There are certainly more secure ways available to provide credentials to applications. Podman secrets however are a good first step in the right direction in my opinion.

Vagrant: always provision virtual machines

Since Spectre and Meltdown (2 infamous side channel attack vectors on CPUs) have become public I thought about better, more secure ways to browse the web. When I read that a commercial vendor for operating systems created a solution where a browser is started in a disposable sandbox that gets discarded when you exit the browser session I thought of ways to implement this feature myself.

Since I’m a great fan of both Virtualbox and Vagrant I decided to use the combination of the two to get this done. My host runs Ubuntu 22.04 LTS, and I’m using Vagrant 2.2.19 (the one shipping with the distribution, it’s not the latest version!) as well as Virtualbox 6.1.40. Whilst the solution presented in this article provides a more secure (notice how I didn’t claim this to be secure ;) ) approach to web browsing it doesn’t keep the host up to date. Security updates for the host O/S and hypervisor (read: Virtualbox) are crucial, too.

Please be super-careful when thinking of implementing a strategy where provisioners are run always, it can and potentially will break your system! For most use cases provisioning a VM each time it starts is not what you want.

Building a “browser” VM

I started off by creating a small “browser” VM with a minimal GUI and a web browser – nothing else – and registered this system as a vagrant box. This is the first step towards my solution: being able to create/tear down the sandbox. Not perfect, and there are more secure ways, but I’m fine with my approach.

The one thing that’s necessary though is updating the VM, ideally performed automatically, at each start. Vagrant provisioners can help with that.

Defining one or more provisioners in the Vagrantfile is a great way to initially configure a VM when it is created for the first time and works really well. Provisioners thankfully do NOT run with each subsequent start of the VM. If they were run each time it would probably be a disaster for all of my other Vagrant VMs. For my sandbox browser VM though I want all packages to be updated automatically.

Switching from on-demand provisioning to automatic provisioning

As I said, VMs are provisioned once by default, subsequent starts won’t run the provisioners as you can see in the output:

$ vagrant up

[output of vagrant bringing my VM up skipped]

==> default: Machine already provisioned. Run `vagrant provision` or use the `--provision`
==> default: flag to force provisioning. Provisioners marked to run always will still run.

The section detailing provisioners in my Vagrantfile is super simple because it has to run in Linux and Windows and I’m too lazy to install Ansible on my Windows box. The above output was caused by the following directive:

Vagrant.configure("2") do |config|

  # ... more directives ...

  config.vm.provision "shell",
    inline: "sudo apt-get update --error-on=any && sudo apt-get dist-upgrade -y"

  # ... even more directives ...

Looking at the command you may have guessed that this is a Debian-based VM, and I’m neither using Flatpack nor Snaps. All packages in this environment are DEBs. That’s easier to maintain for me.

To change the provision section to always run, simply tell it to:

Vagrant.configure("2") do |config|

  # ... more directives ...

  config.vm.provision "shell",
    inline: "sudo apt-get update --error-on=any && sudo apt-get dist-upgrade -y",
    run: "always"

Next time the vagrant VM starts, the provisioner marked as “run: always” will be triggered, even though the VM wasn’t created from scratch:

$ vagrant up

[output of vagrant bringing my VM up skipped once more]

==> default: Machine already provisioned. Run `vagrant provision` or use the `--provision`
==> default: flag to force provisioning. Provisioners marked to run always will still run.
==> default: Running provisioner: shell...
    default: Running: inline script

[output of apt-get omitted for brevity]

There you go! I could have achieved the same by telling vagrant to provision the VM using the --provision flag but I’m sure I would have forgotten that half the time.

Anyone using Ansible can benefit from running provisioners always, too:

Vagrant.configure("2") do |config|

  # ... more directives ...

  config.vm.provision "ansible", run: "always" do |ansible|
      ansible.playbook = "/path/to/ansible/playbook.yaml"
  end

Next time the VM is started by vagrant the Ansible playbook will be executed.

Summary

Vagrant can be instructed to run provisioners always if the use case merits it. For the most part it’s not advisable to run provisioners each time the VM comes up as it might well mess with the installation already present.

Creating a Java Stored Procedure in Oracle Database

This blog post provides a quick (and dirty) way of creating Java Stored Procedures in Oracle Database because I can’t ever remember how to do that. The Java Developer’s Guide details the use of Java in the database, chapter 5 explains how to create Java Stored Procedures. Please refer to the documentation for a proper discussion of Java in the Oracle database.

This blog was written using

  • Oracle 21c Enterprise Edition patched to 21.7.0
  • Oracle Linux 8.6
  • VirtualBox 6.1.40

Java stored procedures are written in Java (unsurprisingly). Before they can be used they have to be made available to the PL/SQL and SQL layer of the Oracle database. Therefore there are a few extra steps involved compared to writing stored procedures in PL/SQL.

Creating the Java Source

Rather than relying on the loadjava tool this post uses the CREATE JAVA command to create and compile the Java source. Note that errors in the code are not reported back to you so make sure that what you’re loading into the database is valid Java and complies with the requirements for Java Stored Procedures (like using static functions etc).

CREATE JAVA SOURCE NAMED helloClassSRC AS
public class HelloClass { 
    public static string hello( string who )  {
        return "hello, " + who ; 
    }
}
/

This creates 2 new objects in the schema, a JAVA source and its associated class in my current schema.

SELECT
    object_name,
    object_type
FROM
    user_objects
WHERE
    created > sysdate - 1;

      OBJECT_NAME    OBJECT_TYPE 
_________________ ______________ 
HelloClass        JAVA CLASS     
HELLOCLASS_SRC    JAVA SOURCE  

With the Java class stored in the database the next step is to make it available to the SQL and PL/SQL layers.

Publishing the Java Class

The hello() Java function returns a string, and I’m going to do the same with the PL/SQL call specification.

CREATE FUNCTION hello_java (
    p_who VARCHAR2
) RETURN VARCHAR2 
AS LANGUAGE JAVA 
NAME 'HelloClass.hello(java.lang.String) return java.lang.String';
/

The hello_java (PL/SQL!) function takes a single argument, p_who of (database) type VARCHAR2 and returns a VARCHAR2. The function is then mapped to the static hello() function in HelloClass, which is where you enter the Java world. hello() takes a string as an input parameter and returns a string.

Using hello_java

Once the PL/SQL call specification is created, it’s very easy to use the function:

SELECT
    hello_java('world') AS greeting
FROM
    dual;

       GREETING 
_______________ 
hello, world 

Although I named the function hello_java, there is no need to specify that Java is used under the covers. It just makes it easier for me to see that this function isn’t a PL/SQL but rather a Java function. Any valid PL/SQL identifier can be used in the call specification. Speaking of PL/SQL, I can of course use hello_java() in PL/SQL:

DECLARE
    l_string VARCHAR2(100);
BEGIN
    l_string := hello_java('world');
    DBMS_OUTPUT.PUT_LINE(l_string);
END;
/

Which prints “hello, world” once serveroutput is enabled.

Summary

I can never remember how to create Java stored procedures and hope this post helps you save 5 minutes as it does for me. There is of course a lot more to say about the topic, so please head over to the Java Developer’s Guide for more details.

Installing Podman on Oracle Linux 8

Rather than having to use a search engine to read up on how to install podman on Oracle Linux 8, I thought I’d write the procedure down. Hopefully this saves you (and me) a few minutes next time the task comes up. I probably should write a short Ansible Playbook at some point, but that’s for another post.

The easiest way to install podman on Oracle Linux 8 is to install the entire podman module. If you haven’t used modules and application streams yet in Oracle Linux 8 yet, you can find more details in the Oracle Linux 8 documentation. Quoting from chapter 5, section “Use DNF Modules and Application Streams” in Managing Software in Oracle Linux Guide:

DNF introduces the concepts of modules, streams and profiles to allow for the management of different versions of software applications within a single operating system release. Modules can be used to group together many packages that comprise a single application and its dependencies.

This sounds exactly like what I want.

Installing the Podman Module

So what do the concepts of stream and module mean in practice? Podman is shipped as a module in the Application Stream (AppStream):

[root@ol8podman ~]# dnf module list container-tools:ol8
Last metadata expiration check: 0:12:47 ago on Fri 15 Jul 2022 10:13:15 BST.
Oracle Linux 8 Application Stream (x86_64)
Name            Stream  Profiles Summary                                                                 
container-tools ol8 [d] common [ Most recent (rolling) versions of podman, buildah, skopeo, runc, conmon,
                        d]        runc, conmon, CRIU, Udica, etc as well as dependencies such as containe
                                 r-selinux built and tested together, and updated as frequently as every 
                                 12 weeks.

Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled

Rather than installing the podman RPM on its own and figure out which other packages I need I went with the installation of the entire module:

[root@ol8podman ~]# dnf module install container-tools:ol8 
Last metadata expiration check: 0:15:25 ago on Fri 15 Jul 2022 10:13:15 BST.
Dependencies resolved.
=========================================================================================================
 Package                      Arch   Version                                     Repository         Size
=========================================================================================================
Installing group/module packages:
 buildah                      x86_64 1:1.24.2-4.module+el8.6.0+20665+a3b29bef    ol8_appstream     8.1 M
 cockpit-podman               noarch 43-1.module+el8.6.0+20665+a3b29bef          ol8_appstream     493 k
 conmon                       x86_64 2:2.1.0-1.module+el8.6.0+20665+a3b29bef     ol8_appstream      55 k
 container-selinux            noarch 2:2.179.1-1.module+el8.6.0+20665+a3b29bef   ol8_appstream      58 k
 containernetworking-plugins  x86_64 1:1.0.1-2.module+el8.6.0+20665+a3b29bef     ol8_appstream      18 M
 containers-common            x86_64 2:1-27.0.1.module+el8.6.0+20665+a3b29bef    ol8_appstream      67 k
 criu                         x86_64 3.15-3.module+el8.6.0+20665+a3b29bef        ol8_appstream     518 k
 crun                         x86_64 1.4.4-1.module+el8.6.0+20665+a3b29bef       ol8_appstream     209 k
 fuse-overlayfs               x86_64 1.8.2-1.module+el8.6.0+20665+a3b29bef       ol8_appstream      73 k
 libslirp                     x86_64 4.4.0-1.module+el8.6.0+20665+a3b29bef       ol8_appstream      70 k
 podman                       x86_64 2:4.0.2-6.module+el8.6.0+20665+a3b29bef     ol8_appstream      13 M
 python3-podman               noarch 4.0.0-1.module+el8.6.0+20665+a3b29bef       ol8_appstream     149 k
 runc                         x86_64 1:1.0.3-2.module+el8.6.0+20665+a3b29bef     ol8_appstream     3.0 M
 skopeo                       x86_64 2:1.6.1-2.module+el8.6.0+20665+a3b29bef     ol8_appstream     6.7 M
 slirp4netns                  x86_64 1.1.8-2.module+el8.6.0+20665+a3b29bef       ol8_appstream      51 k
 udica                        noarch 0.2.6-3.module+el8.6.0+20665+a3b29bef       ol8_appstream      49 k
Installing dependencies:
 checkpolicy                  x86_64 2.9-1.el8                                   ol8_baseos_latest 346 k
 cockpit-bridge               x86_64 264.1-1.0.1.el8                             ol8_baseos_latest 535 k
 fuse-common                  x86_64 3.3.0-15.0.2.el8                            ol8_baseos_latest  22 k
 fuse3                        x86_64 3.3.0-15.0.2.el8                            ol8_baseos_latest  55 k
 fuse3-libs                   x86_64 3.3.0-15.0.2.el8                            ol8_baseos_latest  95 k
 glib-networking              x86_64 2.56.1-1.1.el8                              ol8_baseos_latest 155 k
 gsettings-desktop-schemas    x86_64 3.32.0-6.el8                                ol8_baseos_latest 633 k
 json-glib                    x86_64 1.4.4-1.el8                                 ol8_baseos_latest 144 k
 libmodman                    x86_64 2.0.1-17.el8                                ol8_baseos_latest  36 k
 libnet                       x86_64 1.1.6-15.el8                                ol8_appstream      67 k
 libproxy                     x86_64 0.4.15-5.2.el8                              ol8_baseos_latest  75 k
 podman-catatonit             x86_64 2:4.0.2-6.module+el8.6.0+20665+a3b29bef     ol8_appstream     354 k
 policycoreutils-python-utils noarch 2.9-19.0.1.el8                              ol8_baseos_latest 253 k
 protobuf-c                   x86_64 1.3.0-6.el8                                 ol8_appstream      37 k
 python3-audit                x86_64 3.0.7-2.el8.2                               ol8_baseos_latest  87 k
 python3-chardet              noarch 3.0.4-7.el8                                 ol8_baseos_latest 195 k
 python3-idna                 noarch 2.5-5.el8                                   ol8_baseos_latest  97 k
 python3-libsemanage          x86_64 2.9-8.el8                                   ol8_baseos_latest 128 k
 python3-pip                  noarch 9.0.3-22.el8                                ol8_appstream      20 k
 python3-policycoreutils      noarch 2.9-19.0.1.el8                              ol8_baseos_latest 2.2 M
 python3-pysocks              noarch 1.6.8-3.el8                                 ol8_baseos_latest  34 k
 python3-pytoml               noarch 0.1.14-5.git7dea353.el8                     ol8_appstream      25 k
 python3-pyxdg                noarch 0.25-16.el8                                 ol8_appstream      94 k
 python3-requests             noarch 2.20.0-2.1.el8_1                            ol8_baseos_latest 123 k
 python3-setools              x86_64 4.3.0-3.el8                                 ol8_baseos_latest 624 k
 python3-setuptools           noarch 39.2.0-6.el8                                ol8_baseos_latest 163 k
 python3-urllib3              noarch 1.24.2-5.0.1.el8                            ol8_baseos_latest 177 k
 python36                     x86_64 3.6.8-38.module+el8.5.0+20329+5c5719bc      ol8_appstream      19 k
 shadow-utils-subid           x86_64 2:4.6-16.el8                                ol8_baseos_latest 112 k
 yajl                         x86_64 2.1.0-10.el8                                ol8_appstream      41 k
Installing module profiles:
 container-tools/common                                                                                 
Enabling module streams:
 container-tools                     ol8                                                                
 python36                            3.6                                                                

Transaction Summary
=========================================================================================================
Install  46 Packages

Total download size: 58 M
Installed size: 200 M

...

This is great! The current stable podman release at the time of writing is 4.1.1, so getting 4.0.2 doesn’t look too bad to me :)

Summary

Installing podman on Oracle Linux 8 is quite simple provided you are happy to install the entire podman module. The module provides a very convenient way to install recent releases for podman and its related tools (buildah, skopeo, …). Podman is important enough to get its own User Guide in the Oracle Linux 8 documentation set, the installation instructions I used when putting this post together can be found in chapter 2.

Avoid “Warning: Additional provider information from registry” for OCI Terraform Provider

After updating my main development workstation to Fedora 36 including all the tools I regularly use I noticed a change when working with Terraform code. The call to terraform init succeeded but was accompanied by a warning:

$ terraform version -no-color
Terraform v1.2.3
on linux_amd64
$ terraform init -no-color

Initializing the backend...

Initializing provider plugins...
- Finding latest version of hashicorp/oci...
- Installing hashicorp/oci v4.80.1...
- Installed hashicorp/oci v4.80.1 (signed by HashiCorp)

Terraform has created a lock file .terraform.lock.hcl to record the provider
selections it made above. Include this file in your version control repository
so that Terraform can guarantee to make the same selections by default when
you run "terraform init" in the future.


Warning: Additional provider information from registry

The remote registry returned warnings for registry.terraform.io/hashicorp/oci:
- For users on Terraform 0.13 or greater, this provider has moved to oracle/oci. 
  Please update your source in required_providers.

Terraform has been successfully initialized!

You may now begin working with Terraform. Try running "terraform plan" to see
any changes that are required for your infrastructure. All Terraform commands
should now work.

If you ever set or change modules or backend configuration for Terraform,
rerun this command to reinitialize your working directory. If you forget, other
commands will detect it and remind you to do so if necessary.

The “What’s new” section in the OCI Terraform Provider documentation mentions this change. It also describes how to switch the provider’s source to avoid this warning.

So here is what I did. I tend to put my provider details into main.tf, so this seemed like the best place to put the required_providers section:

provider "oci" {
  tenancy_ocid         = var.tenancy_ocid
  user_ocid            = var.user_ocid
  fingerprint          = var.key_fingerprint
  private_key_path     = var.private_key_path
  private_key_password = var.private_key_password
  region               = var.oci_region
}

terraform {
  required_providers {
    oci = {
      source  = "oracle/oci"
      version = ">= 4.0.0"
    }
  }
}

After adding the new terraform block I managed to use the oracle/oci provider and avoid the warning. The OCI driver version 4.80.1 was current at the time of writing.

$ terraform init -no-color

Initializing the backend...

Initializing provider plugins...
- Finding oracle/oci versions matching ">= 4.0.0"...
- Installing oracle/oci v4.80.1...
- Installed oracle/oci v4.80.1 (signed by a HashiCorp partner, key ID 1533A49284137CEB)

Partner and community providers are signed by their developers.
If you'd like to know more about provider signing, you can read about it here:
https://www.terraform.io/docs/cli/plugins/signing.html

Terraform has created a lock file .terraform.lock.hcl to record the provider
selections it made above. Include this file in your version control repository
so that Terraform can guarantee to make the same selections by default when
you run "terraform init" in the future.

Terraform has been successfully initialized!

You may now begin working with Terraform. Try running "terraform plan" to see
any changes that are required for your infrastructure. All Terraform commands
should now work.

If you ever set or change modules or backend configuration for Terraform,
rerun this command to reinitialize your working directory. If you forget, other
commands will detect it and remind you to do so if necessary.

And indeed, Terraform will now use the the oracle/oci driver:

$ terraform version -no-color
Terraform v1.2.3
on linux_amd64
+ provider registry.terraform.io/oracle/oci v4.80.1

Happy automating!

Generating Table DDL in Oracle Database

Generating table DDL is a common requirement. Unfortunately it’s not quite common enough for me to remember the syntax by heart, so this post serves as a reference to myself how to do this. Hopefully it saves you a few minutes, too.

I used Oracle SQLDeveloper Command-Line (SQLcl) version: 22.1.1.0 build: 22.1.1.131.0820 for this post, connecting to an Oracle 19c database running on Linux. The DDL command you are reading about later is not new to version 22.1, that one just so happens to be the most current version at the time of writing.

Here are the links for downloading SQLcl:

SQLcl 22.1 comes with a lot more cool stuff, Jeff Smith wrote about the details here.

DBMS_METADATA

Table DDL (and a lot of other DDL for that matter) can be generated using DBMS_METADATA. This package has been part of the database for quite some time and is documented in the PL/SQL Packages and Types Guide. In cases where SQLcl is unavailable it’s still a more than viable option to combine calls to DBMS_METADATA.set_transform_param() with DBMS_METADATA.get_ddl(). If you are in this position you might find the following sections in the PL/SQL Packages and Types Guide useful:

Tables 107-23 and 107-25 respectively (referenced above) are key to when it comes to understanding the options SQLcl offers. More on that later.

SQLcl is a LOT easier to use than DBMS_METADATA

SQLcl provides a shortcut to using the package: rather than calling DBMS_METADATA.GET_DDL() you can make use of the ddl command instead:

SQL> help ddl
DDL
---

DDL generates the code to reconstruct the object listed.  Use the type option
for materialized views. Use the save options to save the DDL to a file.

DDL [<object_name> [<type>] [SAVE <filename>]]

Let’s use the command with the ORDERS table Swingbench provides:

SQL> show user
USER is "SOE"
SQL> ddl orders

  CREATE TABLE "SOE"."ORDERS" 
   (	"ORDER_ID" NUMBER(12,0) CONSTRAINT "ORDER_ORDER_ID_NN" NOT NULL ENABLE, 
	"ORDER_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE CONSTRAINT "ORDER_DATE_NN" NOT NULL ENABLE, 
	"ORDER_MODE" VARCHAR2(8), 
	"CUSTOMER_ID" NUMBER(12,0) CONSTRAINT "ORDER_CUSTOMER_ID_NN" NOT NULL ENABLE, 
	"ORDER_STATUS" NUMBER(2,0), 
	"ORDER_TOTAL" NUMBER(8,2), 
	"SALES_REP_ID" NUMBER(6,0), 
	"PROMOTION_ID" NUMBER(6,0), 
	"WAREHOUSE_ID" NUMBER(6,0), 
	"DELIVERY_TYPE" VARCHAR2(15), 
	"COST_OF_DELIVERY" NUMBER(6,0), 
	"WAIT_TILL_ALL_AVAILABLE" VARCHAR2(15), 
	"DELIVERY_ADDRESS_ID" NUMBER(12,0), 
	"CUSTOMER_CLASS" VARCHAR2(30), 
	"CARD_ID" NUMBER(12,0), 
	"INVOICE_ADDRESS_ID" NUMBER(12,0), 
	 CONSTRAINT "ORDER_MODE_LOV" CHECK (order_mode in ('direct','online')) DEFERRABLE ENABLE NOVALIDATE, 
	 CONSTRAINT "ORDER_TOTAL_MIN" CHECK (order_total >= 0) DEFERRABLE ENABLE NOVALIDATE, 
	 CONSTRAINT "ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
	  REFERENCES "SOE"."CUSTOMERS" ("CUSTOMER_ID") ON DELETE SET NULL ENABLE NOVALIDATE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 16 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;
  CREATE UNIQUE INDEX "SOE"."ORDER_PK" ON "SOE"."ORDERS" ("ORDER_ID") REVERSE 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;
ALTER TABLE "SOE"."ORDERS" ADD CONSTRAINT "ORDER_PK" PRIMARY KEY ("ORDER_ID")
  USING INDEX "SOE"."ORDER_PK"  ENABLE NOVALIDATE;

  CREATE INDEX "SOE"."ORD_CUSTOMER_IX" ON "SOE"."ORDERS" ("CUSTOMER_ID") REVERSE 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;

  CREATE INDEX "SOE"."ORD_ORDER_DATE_IX" ON "SOE"."ORDERS" ("ORDER_DATE") REVERSE 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;

  CREATE INDEX "SOE"."ORD_SALES_REP_IX" ON "SOE"."ORDERS" ("SALES_REP_ID") REVERSE 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ;

  CREATE INDEX "SOE"."ORD_WAREHOUSE_IX" ON "SOE"."ORDERS" ("WAREHOUSE_ID", "ORDER_STATUS") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SOE_TBS" ; 

Not only does the ddl command print the table DDL, it also includes the primary key as well as the table’s indexes! If you just want to recreate the table in a different (pluggable) database/same schema, then you’re done here. Optionally store the DDL in a file and put it into version control.

Customising Output

Sometimes however you might want to customise the output. That’s possible with SQLcl, too. The set ddl command can be used to that effect:

SQL> help set ddl
SET DDL
  SET DDL [[ PRETTY | SQLTERMINATOR | CONSTRAINTS | REF_CONSTRAINTS |
          CONSTRAINTS_AS_ALTER|OID | SIZE_BYTE_KEYWORD | PARTITIONING |
          SEGMENT_ATTRIBUTES | STORAGE | TABLESPACE | SPECIFICATION |
          BODY | FORCE | INSERT | |INHERIT | RESET] {on|off}
         ] | ON | OFF ]

Table 107-23 in the DBMS_METADATA package documentation describe the meaning of each of these. SQLTERMINATOR for example defines whether a SQL terminator such as the semi-colon or slash should be added after each statement. This defaults to FALSE in DBMS_METADATA, and TRUE in SQLcl. The current settings can be viewed using the show ddl command:

SQL> show ddl
STORAGE : ON
INHERIT : ON
EMIT_SCHEMA : ON
SQLTERMINATOR : ON
OID : ON
SPECIFICATION : ON
TABLESPACE : ON
SIZE_BYTE_KEYWORD : ON
PRETTY : ON
REF_CONSTRAINTS : ON
FORCE : ON
PARTITIONING : ON
CONSTRAINTS : ON
INSERT : ON
BODY : ON
CONSTRAINTS_AS_ALTER : ON
SEGMENT_ATTRIBUTES : ON

I don’t need the storage attributes, don’t want SQLcl to emit the schema and neither do I need the segment attributes. Although turning off SEGMENT_ATTRIBUTES disables STORAGE, too, I set this flag explicitly as a note to self.

SQL> set ddl STORAGE off
DDL Option STORAGE off
SQL> set ddl EMIT_SCHEMA off
DDL Option EMIT_SCHEMA off
SQL> set ddl SEGMENT_ATTRIBUTES off
DDL Option SEGMENT_ATTRIBUTES off

With the defaults changed to my use case the output is reduced quite a bit:

SQL> ddl orders

  CREATE TABLE "ORDERS" 
   (	"ORDER_ID" NUMBER(12,0) CONSTRAINT "ORDER_ORDER_ID_NN" NOT NULL ENABLE, 
	"ORDER_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE CONSTRAINT "ORDER_DATE_NN" NOT NULL ENABLE, 
	"ORDER_MODE" VARCHAR2(8), 
	"CUSTOMER_ID" NUMBER(12,0) CONSTRAINT "ORDER_CUSTOMER_ID_NN" NOT NULL ENABLE, 
	"ORDER_STATUS" NUMBER(2,0), 
	"ORDER_TOTAL" NUMBER(8,2), 
	"SALES_REP_ID" NUMBER(6,0), 
	"PROMOTION_ID" NUMBER(6,0), 
	"WAREHOUSE_ID" NUMBER(6,0), 
	"DELIVERY_TYPE" VARCHAR2(15), 
	"COST_OF_DELIVERY" NUMBER(6,0), 
	"WAIT_TILL_ALL_AVAILABLE" VARCHAR2(15), 
	"DELIVERY_ADDRESS_ID" NUMBER(12,0), 
	"CUSTOMER_CLASS" VARCHAR2(30), 
	"CARD_ID" NUMBER(12,0), 
	"INVOICE_ADDRESS_ID" NUMBER(12,0), 
	 CONSTRAINT "ORDER_MODE_LOV" CHECK (order_mode in ('direct','online')) DEFERRABLE ENABLE NOVALIDATE, 
	 CONSTRAINT "ORDER_TOTAL_MIN" CHECK (order_total >= 0) DEFERRABLE ENABLE NOVALIDATE, 
	 CONSTRAINT "ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
	  REFERENCES "CUSTOMERS" ("CUSTOMER_ID") ON DELETE SET NULL ENABLE NOVALIDATE
   ) ;
  CREATE UNIQUE INDEX "ORDER_PK" ON "ORDERS" ("ORDER_ID") REVERSE 
  ;
ALTER TABLE "ORDERS" ADD CONSTRAINT "ORDER_PK" PRIMARY KEY ("ORDER_ID")
  USING INDEX "ORDER_PK"  ENABLE NOVALIDATE;

  CREATE INDEX "ORD_CUSTOMER_IX" ON "ORDERS" ("CUSTOMER_ID") REVERSE 
  ;

  CREATE INDEX "ORD_ORDER_DATE_IX" ON "ORDERS" ("ORDER_DATE") REVERSE 
  ;

  CREATE INDEX "ORD_SALES_REP_IX" ON "ORDERS" ("SALES_REP_ID") REVERSE 
  ;

  CREATE INDEX "ORD_WAREHOUSE_IX" ON "ORDERS" ("WAREHOUSE_ID", "ORDER_STATUS") 
  ;
SQL> 

And this is exactly what I wanted ;) Instead of listing all the specifics in the storage clause and segment management I can run this script on a different system with different tablespaces and other settings. The downside is that I’m now responsible for providing useful defaults.

Summary

SQLcl provides as super convenient shortcut to using DBMS_METADATA. There are many use cases for this function, from creating DDL for use with version control systems to data migrations the possibilities are almost endless.

Retrieving passwords from OCI Vault for use in Terraform

This post is written with the intention to complement the excellent “A comprehensive guide to managing secrets in your Terraform code” by Yevgeniy Brikman. Its aim is to detail how Oracle Cloud Infrastructure Vault (OCI Vault) can be used to securely store credentials and subsequently use them in Terraform scripts.

If you haven’t done so I recommend reading Yevgeni’s post to get some background information as to why storing passwords anywhere in code, even dot-configuration files, is a Truly Bad Idea. This article provides an example for his third technique: using a dedicated secrets store.

Never, ever, store any credentials in code. Just . don’t . do it. It’s disaster waiting to happen

– every security conscious person, always

Standard disclaimer: please be advised that creating cloud resources most likely costs you money, and keeping them running even more so. Don’t create any cloud resources unless you are authorised to spend that money and know about the implications of creating the resources mentioned in this post.

The problem with the Terraform state file

Whilst using OCI Vault for storing and retrieving secrets is without a doubt a great step towards safer code management, there is still an unsolved issue with Terraform: the state file is considered sensitive information by HashiCorp at the time of writing (2022-05-30). When using the local backend (eg the default) passwords and other sensitive information are stored in clear text in a JSON file. Storing sensitive information in clear text is very much counter-productive to the article’s goals. Alternative backends providing encryption at rest are most likely better suited. Please ensure you remain compliant with your IT security department’s policies regarding the Terraform state file.

Overview

In this article you can read how to create an Autonomous Database (ADB) instance using a tiny Terraform script. Compared to some other tutorials about the subject you won’t find the ADMIN password provided in the code.

Rather than providing the ADB instance’s ADMIN password as an environment variable, the password is retrieved from an OCI Vault secret and passed to the ADB resource. The ADB instance is just one potential use case for using OCI Vault in Terraform: anywhere secrets need to be used to create/maintain resources, the technique detailed for ADB applies as well.

Secrets in the context of OCI Vault are credentials such as passwords, certificates, SSH keys, or authentication tokens that you use with Oracle Cloud Infrastructure services. An OCI Vault Secret cannot be looked up as such: secrets are wrapped into what’s referred to as a secret bundle. A secret bundle consists of the secret contents, properties of the secret and secret version (such as version number or rotation state), and user-provided contextual metadata for the secret.

To keep this article short-ish, it is assumed that a secret has already been created and its Oracle Cloud Identifier (OCID) is known. The secret’s OCID is passed to the Terraform script via a variable.

An Autonomous Database instance is perfectly suited to demonstrate the use of a Terraform Data Source for looking up vault secrets as it does not require any supporting resources such as Virtual Cloud Networks, or any elaborate network security settings. The Terraform script will create a publicly accessible ADB instance protected by an Access Control List (ACL) allowing only specific IP addresses to connect. Furthermore, mutual TLS is enabled for even stronger security.

Using an OCI Vault Secret

Lookup operations in Terraform are performed using Data Sources. There are data sources for most cloud resources, including the aforementioned secret bundle. Provided the secret’s OCID is passed via a variable, the lookup using an oci_secrets_secretbundle data source could be performed as follows:

data "oci_secrets_secretbundle" "bundle" {

  secret_id = var.secret_ocid
}

Thankfully the OCI Terraform provider is smart enough to retrieve the current, active version of the secret. Once the secret has been retrieved, it can be used for the creation of an ADB instance. Since secrets are base64 encoded, they have to be decoded before they can be used. The following snippet demonstrates the use of the data source inside the ADB resource:

resource "oci_database_autonomous_database" "demo_adb_21c" {
  compartment_id              = var.compartment_ocid
  db_name                     = "DEMO"
  admin_password              = base64decode(data.oci_secrets_secretbundle.bundle.secret_bundle_content.0.content)
  cpu_core_count              = 1
  data_storage_size_in_tbs    = 1
  db_version                  = "21c"
  db_workload                 = "OLTP"
  display_name                = "ADB Free Tier 21c"
  is_free_tier                = true
  is_mtls_connection_required = true
  ocpu_count                  = 1
  whitelisted_ips             = var.allowed_ip_addresses
}

A call to terraform plan followed by a terraform apply will initiate the creation of the ADB instance. As long as the admin password complies with the password complexity rules of the ADB resource, the database will be created. Once its lifecycle status changed to running, the database will be accessible to IP addresses specified in var.allowed_addresses (a list of strings). Should you invoke the Terraform script from a Linux shell, this might be a way to set the variable:

$ export TF_VAR_allowed_ip_addresses='[ "1.2.3.4", "4.5.6.7" ]'
$ terraform plan -out myplan

Summary

Using OCI Vault to store sensitive information is a secure way to mitigate against many password-handling problems. The Terraform state file remains a concern, especially when using the local backend as it stores all information in clear text. The IT security department should be consulted as to how this potential security vulnerability should be treated. Other backends than the local backend exist and might suit the IT security team’s needs better.

Once a Vault secret has been looked up, it can be used in any Terraform resource. Referencing data sources should lead to more secure code deployments.

Happy Automating!

Linking Containers with Podman

Users of the Docker engine might find that their container runtime isn’t featured prominently in Oracle Linux 8. In fact, unless you change the default confifguration a dnf search does not reveal the engine at all. For better or for worse, it appears the industry has been gradually switching from Docker to Podman and its related ecosystem.

Whilst most Docker commands can be translated 1:1 to the Podman world, some differences exist. Instead of highlighting all the changes here please have a look at the Podman User Guide.

Overview

This article explains how to create a network link between 2 containers:

  1. Oracle XE 21c
  2. SQLcl client

These containers are going to be run "rootless", which has a few implications. By default Podman will allocate storage for containers in ~/.local/share/containers/ so please ensure you have sufficient space in your home directory.

The article refers to Gerald Venzl’s Oracle-XE images and you will create another image for SQLcl.

Installation

If you haven’t already installed Podman you can do so by installing the container-tools:ol8 module:

[opc@podman ~]$ $ sudo dnf module install container-tools:ol8
Last metadata expiration check: 0:06:04 ago on Mon 21 Mar 2022 13:19:40 GMT.
Dependencies resolved.
========================================================================================================================
 Package                         Arch      Version                                           Repository            Size
========================================================================================================================
Installing group/module packages:
 buildah                         x86_64    1:1.23.1-2.0.1.module+el8.5.0+20494+0311868c      ol8_appstream        7.9 M
 cockpit-podman                  noarch    39-1.module+el8.5.0+20494+0311868c                ol8_appstream        483 k
 conmon                          x86_64    2:2.0.32-1.module+el8.5.0+20494+0311868c          ol8_appstream         55 k
 container-selinux               noarch    2:2.173.0-1.module+el8.5.0+20494+0311868c         ol8_appstream         57 k
 containernetworking-plugins     x86_64    1.0.1-1.module+el8.5.0+20494+0311868c             ol8_appstream         19 M
 containers-common               noarch    2:1-8.0.1.module+el8.5.0+20494+0311868c           ol8_appstream         62 k
 criu                            x86_64    3.15-3.module+el8.5.0+20416+d687fed7              ol8_appstream        518 k
 crun                            x86_64    1.4.1-1.module+el8.5.0+20494+0311868c             ol8_appstream        205 k
 fuse-overlayfs                  x86_64    1.8-1.module+el8.5.0+20494+0311868c               ol8_appstream         73 k
 libslirp                        x86_64    4.4.0-1.module+el8.5.0+20416+d687fed7             ol8_appstream         70 k
 podman                          x86_64    1:3.4.2-9.0.1.module+el8.5.0+20494+0311868c       ol8_appstream         12 M
 python3-podman                  noarch    3.2.1-1.module+el8.5.0+20494+0311868c             ol8_appstream        148 k
 runc                            x86_64    1.0.3-1.module+el8.5.0+20494+0311868c             ol8_appstream        3.1 M
 skopeo                          x86_64    2:1.5.2-1.0.1.module+el8.5.0+20494+0311868c       ol8_appstream        6.7 M
 slirp4netns                     x86_64    1.1.8-1.module+el8.5.0+20416+d687fed7             ol8_appstream         51 k
 udica                           noarch    0.2.6-1.module+el8.5.0+20494+0311868c             ol8_appstream         48 k
Installing dependencies:
 fuse-common                     x86_64    3.2.1-12.0.3.el8                                  ol8_baseos_latest     22 k
 fuse3                           x86_64    3.2.1-12.0.3.el8                                  ol8_baseos_latest     51 k
 fuse3-libs                      x86_64    3.2.1-12.0.3.el8                                  ol8_baseos_latest     95 k
 libnet                          x86_64    1.1.6-15.el8                                      ol8_appstream         67 k
 podman-catatonit                x86_64    1:3.4.2-9.0.1.module+el8.5.0+20494+0311868c       ol8_appstream        345 k
 policycoreutils-python-utils    noarch    2.9-16.0.1.el8                                    ol8_baseos_latest    252 k
 python3-pytoml                  noarch    0.1.14-5.git7dea353.el8                           ol8_appstream         25 k
 python3-pyxdg                   noarch    0.25-16.el8                                       ol8_appstream         94 k
 yajl                            x86_64    2.1.0-10.el8                                      ol8_appstream         41 k
Installing module profiles:
 container-tools/common                                                                                                
Enabling module streams:
 container-tools                           ol8                                                                         

Transaction Summary
========================================================================================================================
Install  25 Packages

If you like DNS on your container network, install podman-plugins and dnsmasq. This article assumes you do so. The latter of the 2 services needs to be enabled and started:

[opc@podman ~]$ for task in enable start is-active; do sudo systemctl ${task} dnsmasq; done
active

If you see active in the output as in the example dnsmasq is working. If your system is part of a more elaborate setup, the use of dnsmasq is discouraged and you should ask your friendly network admin for advice.

Virtual Network Configuration

This section describes setting up a virtual network. That way you are emulating the way you’d previously have worked with Docker. If I should find the time for it I’ll write a second article and introduce you to Podman’s PODs, an elegant concept similar to Kubernetes that is not available with the Docker engine.

Network creation

Before containers can communicate with one another, they need to be told which network to use. The easiest way to do so is by creating a new, custom network as shown in this example:

[opc@podman ~]$ podman network create oranet
/home/opc/.config/cni/net.d/oranet.conflist
[opc@podman ~]$ podman network ls
NETWORK ID    NAME        VERSION     PLUGINS
2f259bab93aa  podman      0.4.0       bridge,portmap,firewall,tuning
4f4bfc6d2c15  oranet      0.4.0       bridge,portmap,firewall,tuning,dnsname
[opc@podman ~]$ 

As you can see the new network – oranet – has been created and it’s capable of using DNS thanks for the dnsname extension. If you opted not to install podman-plugins and dnsmasq this feature won’t be availble. Testing showed that availability of DNS on the container network made life a lot easier.

Storage Volumes

Containers are transient by nature, things you store in them are ephemeral by design. Since that’s not ideal for databases, a persistence layer should be used instead. The industry’s best known method to do so is by employing (Podman) volumes. Volumes are crated using the podman volume create command, for example:

[opc@podman ~]$ podman volume create oradata
oradata

As it is the case with the Container images, by default alll the volume’s data will reside in ~/.local/share/containers.

Database Secrets

The final step while preparing for running a database in Podman is to create a secret. Secrets are a relatively new feature in Podman and relieve you from having to consider workarounds passing sensitive data to containers. The Oracle XE containers to be used need to be initialised with a DBA password and it is prudent not to pass this in clear text on the command line.

For this example the necessary database password has been created as a secret and stored as oracle-password using podman secret create ...

[opc@podman ~]$ podman secret create oracle-password ~/.passwordFileToBeDeletedAfterUse
0c5d6d9eff16c4d30d36c6133
[opc@podman ~]$ podman secret ls
ID                         NAME             DRIVER      CREATED        UPDATED        
0c5d6d9eff16c4d30d36c6133  oracle-password  file        2 minutes ago  2 minutes ago 

This concludes the necessary preparations.

Let there be Containers

With all the setup completed the next step is to start an Oracle 21c XE instance and build the SQLcl container.

Oracle XE

Using the instructions by Gerald Venzl’s GitHub repository, adapted for this use case, a call to podman run might look like this:

[opc@podman ~]$ podman run --name oracle21xe --secret oracle-password \
-e ORACLE_PASSWORD_FILE=/run/secrets/oracle-password -d \
--net oranet -v oradata:/opt/oracle/oradata \
docker.io/gvenzl/oracle-xe:21-slim
5d94c0c3620f811bbe522273f73cbcb7c5210fecc0f88b0ecacc1f5474c0855a

The necessary flags are as follows:

  • --name assigns a name to the container so you can reference it later
  • --secret passes a named secret to the container, accessible in /run/secrets/oracle-password
  • -d tells the container to run in the background
  • --net defines the network the container should be attached to
  • -v maps the newly created volume to a directory in the container

You can check whether the container is up an running by executing podman ps:

[opc@podman ~]$ podman ps
CONTAINER ID  IMAGE                               COMMAND     CREATED         STATUS             PORTS       NAMES
5d94c0c3620f  docker.io/gvenzl/oracle-xe:21-slim              53 seconds ago  Up 54 seconds ago              oracle21xe

Creating a small SQLcl container:

Creating a container to run sqlcl is really quite straight forward. A suitable Dockerfile is shown here, please ensure you update the ZIPFILE with the current SQLcl release.

FROM docker.io/openjdk:11

RUN useradd --comment "sqlcl owner" --home-dir /home/sqlcl --uid 1000 --create-home --shell $(which bash) sqlcl 

USER sqlcl
WORKDIR /home/sqlcl

ENV ZIPFILE=sqlcl-21.4.1.17.1458.zip

RUN curl -LO "https://download.oracle.com/otn_software/java/sqldeveloper/${ZIPFILE}" && \
        /usr/local/openjdk-11/bin/jar -xf ${ZIPFILE} && \
        rm ${ZIPFILE}

ENTRYPOINT ["bash", "/home/sqlcl/sqlcl/bin/sql", "/nolog"]

You could of course pull the latest sqlcl ZIP from https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip. Using a named release should simplify the non-trivial task of naming ("tagging") your container image.

The image can be build using podman much in the same way Docker images were built:

[opc@podman ~]$ podman build . -t tools/sqlcl:21.4.1.17.1458

As you can see from the ENTRYPOINT the image cannot be sent to the backround (-d) by podman, it needs to be run interactively as you will see in the next section.

Linking Containers

The last step is to start the sqlcl container and connect to the database.

podman run --rm -it --name sqlcl --net oranet localhost/tools/sqlcl:21.4.1.17.1458

Here is an example how this works in my container:

[opc@podman ~]$ podman run --rm -it --name sqlcl --net oranet localhost/tools/sqlcl:21.4.1.17.1458


SQLcl: Release 21.4 Production on Mon Mar 21 13:35:05 2022

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

SQL> connect system@oracle21xe/xepdb1
Password? (**********?) ***************
Connected.
SQL> show con_name
CON_NAME 
------------------------------
XEPDB1

The connection string consists of a username (system) and the container name assigned as part of the call to podman run ... --name. Thanks to the dnsname extension and linking the container to the oranet network it is possible to address systems by name. XEPDB1 is the default name of the XE instance’s Pluggable Database.

Instead of connecting to a Pluggable Database it is of course possible to connect to the Container Database’s Root (CDB$ROOT).

Summary

Podman is very compatible to Docker, easing the transition. In this part of the mini-series you could read how to use Podman functionality with Oracle Linux 8 to link a container running Oracle XE and SQLcl.

Vagrant: mapping a Virtualbox VM to a Vagrant environment

This is a small post hopefully saving you a few minutes mapping Vagrant and VirtualBox environments.

I typically have lots of Vagrant environments defined. I love Vagrant as a technology, it makes it super easy to spin up Virtual Machines (VMs) and learn about new technologies.

Said Vagrant environments obviously show up as VMs in VirtualBox. To make it more interesting I have a few more VirtualBox VMs that don’t map to a Vagrant environment. Adding in a naming convention that’s been growing organically over time I occasionally find myself at a loss as to which VirtualBox VM maps to a Vagrant environment. Can this be done? Yep, and creating a mapping is quite simple actually. Here is what I found useful.

Directory structure

My Vagrant directory structure is quite simple: I defined ${HOME}/vagrant as top-level directory with a sub-directory containing all my (custom) boxes. Apart from ~/vagrant/boxes I create further sub-directories for each project. For example:

[martin@ryzen: vagrant]$ ls -ld *oracle* boxes
drwxrwxr-x 2 martin martin 4096 Nov 23 16:52 boxes
drwxrwxr-x 3 martin martin   41 Feb 16  2021 oracle_19c_dg
drwxrwxr-x 3 martin martin   41 Nov 19  2020 oracle_19c_ol7
drwxrwxr-x 3 martin martin   41 Jan  6  2021 oracle_19c_ol8
drwxrwxr-x 3 martin martin   41 Nov 25 12:54 oracle_xe

But … which of my VirtualBox VMs belongs to the oracle_xe environment?

Mapping a Vagrant environment to a VirtualBox VM

Vagrant keeps a lot of metadata in the project’s .vagrant directory. Continuing with the oracle_xe example, here is what it stores:

[martin@buildhost: oracle_xe]$ tree .vagrant/
.vagrant/
├── machines
│   └── oraclexe
│       └── virtualbox
│           ├── action_provision
│           ├── action_set_name
│           ├── box_meta
│           ├── creator_uid
│           ├── id
│           ├── index_uuid
│           ├── synced_folders
│           └── vagrant_cwd
├── provisioners
│   └── ansible
│       └── inventory
│           └── vagrant_ansible_inventory
└── rgloader
    └── loader.rb

7 directories, 10 files

Looking at the above output I guess I should look at .vagrant/machines/

The machine name (oraclexe) is derived from the Vagrantfile. I create a config.vm.define section per VM out of habit (even when I create just 1 VM), as you can see here in my shortened Vagrantfile:

# -*- mode: ruby -*-
# vi: set ft=ruby :

Vagrant.configure("2") do |config|
  
  config.vm.define "oraclexe" do |xe|
    xe.vm.box = "ol7"
    xe.vm.box_url = "file:///home/martin/vagrant/boxes/ol7.json"

    ...

    xe.vm.provision "ansible" do |ansible|
      ansible.playbook = "setup.yml"
    end
  end
end

In case you don’t give your VMs a name you should find a directory named default instead.

As I’m using Vagrant together with VirtualBox I’m not surprised to find a sub-directory named virtualbox.

Finally! You see the VM’s metadata in that directory. The VM’s ID can be found in .vagrant/machines/oraclexe/virtualbox/id. The file contains the internal ID VirtualBox uses to identify VMs. Using that knowledge to my advantage I can create the lookup as shown here:

[martin@buildhost: oracle_xe]$ vboxmanage list vms | grep $(cat .vagrant/machines/oraclexe/virtualbox/id)
"oraclexe" {67031773-bad9-4325-937b-e471d02a56a3}

Voila! This wasn’t particularly hard since the VM name is oracelxe as well. Nevertheless I found this technique works well regardless of how you curated your Vagrantfile.

Happy Automating!