Olav Grønås Gjerde

profile image
Full stack system architect with passion for Unix, Java, Python and databases.
Twitter @olavgg
8 months ago

How to use Spring Boot JPA CriteriaBuilder and PostgreSQL ILIKE operator

First create a Postgres Function

-- This function is for working around the lack of support for ILIKE operator in Hibernate
CREATE OR REPLACE FUNCTION ILIKE_FN(name text, name_arg text) RETURNS bool
    LANGUAGE SQL
RETURN name ILIKE name_arg;

Then do the following in your CriteriaBuilder

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Person> q = cb.createQuery(Person.class);
Root<Person> root = q.from(Person.class);
root.fetch("metadata", JoinType.LEFT);

List<Predicate> predicates = new ArrayList<>();
if(personFilter.getName() != null){
    // We have created a function named ILIKE_FN in psql that can handle the
    // ILIKE operator that hibernate doesn't support
    Expression<Boolean> ilikeExp = cb.function(
            "ILIKE_FN",
            Boolean.class,
            root.get("name"),
            cb.literal(personFilter.getName().toUpperCase())
    );
    predicates.add(cb.isTrue(ilikeExp));
}
if(personFilter.getId() != null){
    predicates.add(cb.equal(root.get("id"), personFilter.getId()));
}

q.where(predicates.toArray(new Predicate[0]));

TypedQuery<Person> query = entityManager.createQuery(q);
List<Person> persons = query.getResultList();

And you will get the following SQL generated:

select
        a1_0.id,
        a1_0.description,
        a1_0.name,
        m1_0.id,
        m1_0.key,
        m1_0.value,
    from
        person a1_0 
    left join
        person_metadata m1_0 
            on a1_0.id=m1_0.person_id 
    where
        ILIKE_FN(a1_0.name,'OLAV GJERDE')
9 months ago

How to Configure pgAdmin 4 with OAuth2 and Keycloak

I have earlier mentioned how to install pgAdmin on Linux and FreeBSD. Now we will enable OAuth2 authentication for Single Sign on support. Keycloak is a fantastic open source product for managing users. It is open source and there is plenty of great documentation and youtube videos about it.

This is not a tutorial about KeyCloak

Anyway we need to configure Keycloak. I assume you have already created a realm with users. If not read and do that first.

I am not going to cover the GUI, as GUI's change and layouts change. It only adds more confusing as this tutorial ages. We will use the REST Api instead.

First login with your keycloak admin user

bin/kcadm.sh config credentials --server http://mykeycloak:8080 --realm master --user admin --password secret

Generate a client secret

export CLIENT_SECRET=`tr -dc A-Za-z0-9 </dev/urandom | head -c 24 ; echo ''`

Create the client

bin/kcadm.sh create clients -r my-realm-name  -f - << EOF
        {
          "clientId": "pgadmin",
          "baseUrl": "/",
          "surrogateAuthRequired": false,
          "enabled": true,
          "alwaysDisplayInConsole": false,
          "clientAuthenticatorType": "client-secret",
          "secret": "${CLIENT_SECRET}",
          "redirectUris": [
            "https://my_pgadmin_server/oauth2/authorize"
          ],
          "webOrigins": ["+"],
          "bearerOnly": false,
          "consentRequired": false,
          "standardFlowEnabled": true,
          "implicitFlowEnabled": false,
          "directAccessGrantsEnabled": false,
          "serviceAccountsEnabled": true,
          "publicClient": false,
          "frontchannelLogout": false,
          "protocol": "openid-connect",
          "defaultClientScopes": ["web-origins","role_list","roles","profile","email"],
          "optionalClientScopes": ["address","phone","offline_access","microprofile-jwt"],
          "name" : "pgAdmin Client"
        }
      EOF

That's basically all you need todo with Keycloak.

Next step is configuring pgAdmin, edit following file /var/lib/pgadmin/.local/lib/python3.9/site-packages/pgadmin4/config_local.py Note: your install location of pgadmin may vary

SERVER_MODE = True
MASTER_PASSWORD_REQUIRED = True
AUTHENTICATION_SOURCES = ['oauth2', 'internal']
OAUTH2_AUTO_CREATE_USER = True
OAUTH2_CONFIG = [{
    'OAUTH2_NAME': 'MyKeyCloak',
    'OAUTH2_DISPLAY_NAME': 'MyKeyCloak',
    'OAUTH2_CLIENT_ID': 'pgadmin',
    'OAUTH2_CLIENT_SECRET': 'your-client-secret-here',
    'OAUTH2_TOKEN_URL': 'https://mykeycloak/realms/datahub/protocol/openid-connect/token',
    'OAUTH2_AUTHORIZATION_URL': 'https://mykeycloak/realms/datahub/protocol/openid-connect/auth',
    'OAUTH2_API_BASE_URL': 'https://mykeycloak/realms/datahub/',
    'OAUTH2_USERINFO_ENDPOINT': 'https://mykeycloak/realms/datahub/protocol/openid-connect/userinfo',
    'OAUTH2_SCOPE': 'openid email profile',
    'OAUTH2_BUTTON_COLOR': '#51c65b;',
    'OAUTH2_SERVER_METADATA_URL': 'https://mykeycloak/realms/datahub/.well-known/openid-configuration'
}]

Restart pgAdmin and test your login.

11 months ago

Delayed start for service in systemd on Debian

I had an issue when I configured SR-IOV on Mellanox NIC where libvirt would start before the network was ready.

The fix for this is to enable 2 services

sudo systemctl enable systemd-networkd.service systemd-networkd-wait-online.service

Then edit the libvirt systemd file: /usr/lib/systemd/system/libvirtd.service and add:

After=systemd-networkd-wait-online.service
Wants=systemd-networkd-wait-online.service

Now libvirt will start when the network is ready and when all the virtual functions are ready.

11 months ago

KVM Debian 12 bookworm - virsh start VM with error message: failed to open /dev/vfio/62 Permission denied

So I was playing around with SR-IOV and adding a network pool with virtual functions for my virtual machines. Unfortunately I got stuck with and error message when I tried to start my virtual machine.

failed to open /dev/vfio/62 Permission denied

Then it is most likely AppArmor denying libvirt access to /dev/vfio

You can fix this by editing /etc/apparmor.d/local/abstractions/libvirt-qemu Add:

/dev/vfio/* rw,

And restart your VM for success:

View older blog posts ⇾