Page tree
Skip to end of metadata
Go to start of metadata

MidPoint Configuration

Database create

CREATE USER midpoint WITH PASSWORD 'password' LOGIN SUPERUSER;
CREATE DATABASE midpoint WITH OWNER = midpoint ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;

Configuration sample

<configuration>
	<midpoint>
		<repository>
			<repositoryServiceFactoryClass>com.evolveum.midpoint.repo.sql.SqlRepositoryFactory</repositoryServiceFactoryClass>
            <database>postgresql</database>
			<jdbcUsername>midpoint</jdbcUsername>
			<jdbcPassword>password</jdbcPassword>
			<jdbcUrl>jdbc:postgresql://localhost/midpoint</jdbcUrl>
		</repository>
	</midpoint>
</configuration>

Driver

Driver for this database is bundled in MidPoint WAR archive.

Tips and Tricks

Ubuntu

Create Database User

sudo -u postgres createuser --pwprompt --no-superuser --no-createdb --no-createrole midpoint

Delete Database User

sudo -u postgres dropuser midpoint

Delete Database

sudo -u postgres dropdb midpoint

Create database

sudo -u postgres createdb --owner=midpoint midpoint

Run databse script

psql --host=localhost --username=midpoint < postgresql-schema.sql

Working with explicit schema

CREATE SCHEMA midpoint;
ALTER DATABASE db_midpoint SET search_path TO midpoint,public;
GRANT USAGE, SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA midpoint TO midpoint_user_role;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA midpoint TO midpoint_user_role;

Development installation on Windows

(Tested in March 2020 with PostgreSQL 12.2.)

Installation, initdb

To avoid any problems with admin rights we will install Postgres from ZIP archive as suggested on this page (for advanced users (smile)).

  • Unpack the archive, e.g. to c:\work\tools\pgsql (pgsql is the name of zipped directory).
  • Optional: Add C:\work\tools\pgsql\bin to your PATH environment variable for convenience.
  • Optional: Decide where you want your postgres data and set PGDATA environment variable. This is useful for initdb and pg_ctl commands, but can be also provided with switches. I strongly recommend to set this one.
  • Run (and provide admin password and remember it): initdb.exe -E utf8 --auth-host=scram-sha-256 -U admin -W
    • If you don't have PGDATA set, provide the expected directory name with -D switch.
    • By default superuser is named postgres, you can use that, if you want. This is useful on Linux where the name matches system user name, less so on Windows.

If we try to mess with locales (e.g. LC_CTYPE is set) initdb command may fail mysteriously with:

initdb: error: The program "postgres" was found by "C:/work/tools/pgsql/bin/initdb.exe"
but was not the same version as initdb.
Check your installation.

This is the start of the locale related problems on Windows, but not the end of it.

  • Start the database with: pg_ctl start
  • Again, use -D switch if you don't have PGDATA set.
  • Log in: psql -W postgres admin
    • -W forces the password prompt and is not necessary, unless you already set some psql environment variables; postgres is name of the default database, admin is the user name

Creating user and database

Now we can create user and the database as indicated at the start of this page - but Windows uses different names for locales, so we simply leave this out and hope it will not matter for our development purposes.

CREATE USER midpoint WITH PASSWORD 'password' LOGIN SUPERUSER;
CREATE DATABASE midpoint WITH OWNER = midpoint ENCODING = 'UTF8' TABLESPACE = pg_default CONNECTION LIMIT = -1;

If we try the locales provided above, it will very likely complain about it and fallback to something like CPUTF-8.

Now the database is up and running, operated manually with pg_ctl start/stop, fully under our control, no services, no access rights problems (provided the directories you used are available to you).

Then we want to populate the database, e.g. from the root of Midpoint repository: psql midpoint midpoint < config\sql\postgresql-4.0-all.sql

Configuring midPoint

  • djust the Midpoint configuration. If you use multiple databases I recommend to add new midpoint.home, e.g.: c:\work\tmp\midpoint-home-postgres
  • Add config.xml there with repository configured to use Postgres (the same as at the start of this page).
  • Start your Midpoint application with switch: -Dmidpoint.home=c:\work\tmp\midpoint-home-postgres

To make psql easier to use you can set environment variables PGHOST=localhost (probably default), PGPORT=5432 (probably default), PGDATABASE=midpoint, PGUSER=midpoint, PGPASSWORD=password (normally obviously unsafe).

Installation up to this point does not allow connection from other machines and we don't need it for Midpoint and PostgreSQL running on the same machine. In the next experiment, with PostgreSQL on virtual machine, we have to solve this issue as well.

Vagrantbox with PostgreSQL on Linux

(Tested in March 2020, based on bento/ubuntu-19.10 box, PostgreSQL is 11.7)

To shield ourselves from non-linux operating system, we can use power of Vagrant+VirtualBox combo - both must be installed in advance.

Virtual machine provisioning

Create empty directory, which will be the working directory for this Vagrant environment, in our case representing a single Linux virtual machine we want to provision with PostgreSQL - we will call it vagrant directory. Create a single file called Vagrantfile there with the following content:

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

Vagrant.configure("2") do |config|
  # base Vagrantbox we're starting with
  config.vm.box = "bento/ubuntu-19.10"
  config.vm.box_check_update = false

  # multiple entries can be added, but we don't need web, and we need 8080 free on host
  # config.vm.network "forwarded_port", guest: 80, host: 8080
  # PostgreSQL
  config.vm.network "forwarded_port", guest: 5432, host: 5432

  config.vm.provider "virtualbox" do |vb|
  #   vb.gui = true
    vb.name = "postgres-linux"
    vb.memory = "1024"
  end

  config.vm.provision "shell", inline: <<-SHELL
    sudo apt-get update
    sudo apt-get install -y postgresql postgresql-contrib
    sudo -i -u postgres psql -c "SELECT version();"

    # allow connections from outside (not just from localhost)
    sudo ed /etc/postgresql/*/main/postgresql.conf << EOF
/^#listen_addresses
i
# Listen on all interfaces to allow connections from outside
listen_addresses = '*'
.
wq
EOF
    sudo cat >> /etc/postgresql/*/main/pg_hba.conf << EOF
# Allow connections from outside (with password)
host    all             all             0.0.0.0/0            md5

EOF
    sudo service postgresql restart

    sudo -i -u postgres psql -c "CREATE USER midpoint WITH PASSWORD 'password' LOGIN SUPERUSER;"
    sudo -i -u postgres psql -c "CREATE DATABASE midpoint WITH OWNER = midpoint ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;"

    # let's make it easy for "vagrant" user to use psql command
    sudo cat >> ~vagrant/.bashrc << EOF

export PGHOST=localhost
export PGPORT=5432
export PGDATABASE=midpoint
export PGUSER=midpoint
export PGPASSWORD=password

EOF

  SHELL
end

Now inside the vagrant directory (the one with Vagrantfile) run: vagrant up

No PostgreSQL can run on the host taking the 5432 port. We either have to stop the Postgres on the host, or use different port for host. This is perfectly fine, but we have to change it later in JDBC URL as it will not be default anymore.

Using the VM

This will provision the virtual machine and starts it up for us. Next vagrant up is faster, first provisioning always lasts longer. Now we want to log into the machine: vagrant ssh

We can check that everthing works just running psql, checking the list of databases there with \l - but we still don't have midpoint database populated, \d shows nothing. Let's get out with: \q

To halt the VM later we need to get out of it (exit) and run vagrant halt on our host machine, still in our vagrant directory. To restart it later just run vagrant up again. VM remembers everything when used like this.

Populating the DB

There are two ways how to run the initial script:

  • We can copy postgresql-4.0-all.sql to the vagrant directory, which is shared with the virtual machine as its /vagrant directory. Than we can run from inside the box (as vagrant user): psql < /vagrant/postgresql-4.0-all.sql
  • Or we can use psql from the host machine, if PostgreSQL is available there. Database port 5432 is forwarded from guest to host again as 5432, so it seems to be running on host directly.

The first way has an advantage that we don't need to install anything additional on the host - Midpoint and driver is obvious prerequisite that we need anyway. From here on it's again about setting the configuration, preferably in newly created midpoint.home that we provide to Midpoint using -D switch.

Vagrant advantages

One big advantage of using Vagrant is repeatability. When we're done with our VM or we want to recreated it from scratch (e.g. something went wrong beyond repair) we just destroy it with: vagrant destroy -f

After that we just start it again with: vagrant up

This time it does not need to download the base box, but it still needs to provision it - that is to run update, PG install and other commands in the Vagrantfile, but this is much faster than manual work.

Vagrant, or better said VirtualBox used under the hood, sometimes has its own problems. If provisioning doesn't work as expected, first upgrade both tools and then restart the computer before trying again. This helps most of the time.

You can also run the VM directly from VirtualBox, but this is not recommended for Vagrant managed boxes. It may be needed to use VirtualBox to really get rid of some stuck VM.

Despite these possible annoying problems, I highly recommend Vagrant for provisioning of development environments.

  • No labels