MetabaseでMetabase自身のバックエンドデータベースをPostgreSQLにする

Metabaseをjava -jar metabase.jarで実行すると、特に設定していなければH2データベースがバックエンドとして利用される。 環境変数に指定した上で起動すれば、PostgreSQLも利用できる

export MB_DB_TYPE=postgres
export MB_DB_DBNAME=metabase
export MB_DB_PORT=5432
export MB_DB_USER=metabase_user
export MB_DB_PASS=metabase_password
export MB_DB_HOST=localhost
java -jar metabase.jar

ここで利用するユーザーとデータベースは以下のように用意しておく。 初回起動時にテーブルの作成等は自動的に実施されるので、ユーザーとパスワード、データベースの作成だけしておけば良い。

$ sudo -u pgsql /usr/pkg/bin/createuser metabase_user
$ sudo -u pgsql /usr/pkg/bin/createdb metabase
$ sudo -u pgsql psql -U metabase_user -d metabase
metabase=> alter user metabase_user with password 'metabase_password';
ALTER ROLE
\q

Metabaseで数字8桁で記録された日付を扱う

COBOL派生の環境から移行された情報システムを利用していると、日付が8桁の数字 (例: 20200621) で日付を格納していることがあると思う。 Metabaseでは、画面から日付をdatepickerで入力して検索条件等に利用できるのだが、それは当然のように数字8桁で格納された日付とは一致や比較できない。 データベースはOracle Databaseなので、dateparse()で数字8桁を日付データに変換できると思ったのだが、エラーになってできなかった。 逆に、Metabaseの与える日付を数字8桁に変換することで一致や比較ができるようになったので、例を書いておく。

select
	*
from
    table1
where
	column1 = 'DATA'
	[[and StartDate <= to_char({{TODAY}}, 'yyyymmdd')
	  and EndDate >= to_char({{TODAY}}, 'yyyymmdd')]]

ここで、TODAYがMetabaseが与える変数である。

MetabaseでActive Directory認証する

Metabaseを利用するにあたって、Active Directoryと認証を連携しておきたい。 Metabaseは比較的自由に設定できるのだが、自由であるが故に分かりにくいようにも感じたので、書いておく。

私の環境では、以下のように設定すれば良かった。 LDAP認証の設定である。

サーバーホスト名
tetera-ad1.tetera.org
LDAPポート
3268
LDAPセキュリティ
None
ユーザーネームまたはDN
domainusername@tetera.org
パスワード
domainusernameユーザーのパスワード
ユーザー検索ベース
dc=tetera,dc=org
ユーザーフィルター
(&(sAMAccountName={login}))
これにより、Active Directoryのユーザー名をMetabaseへのログイン名として利用できる。
グループスキーマ
無効
今回はグループについては利用しないため。
メール属性
mail
これにより、電子メールアドレスがアカウントに自動設定できるようだ。
名の属性
givenName
姓の属性
sn

注意としては以下がある。 設定画面に行くには、初回はLDAPではなく電子メールアドレスでログインする必要がある。しかし、その場合にAcrive Directoryのmailアトリビュートと同じ電子メールにすると、 電子メールアドレスでもユーザー名でも、LDAP以前のパスワードでもLDAPのパスワードでもログインできるアカウントができてしまう。 それで良いという考えもあるかもしれないが、これを避けるためにはLDAPに存在しない電子メールアドレスで初回ログインをしておくのが良いようだ。 別に電子メールが送られるという訳でもないので、任意の電子メールアドレスで良いと考えられる。

Metabaseを使ってみる

良く実行するSQLを保存しておけて、ウェブブラウザーから結果を参照しておけるものが欲しいと考えていた。 RedashMetabaseか、似たような他のものを候補にしていた。 私の用途の場合には、Oracle Databaseに接続できることが条件だったし、 サーバーOSはWindows ServerでもLinuxでもなかったので、Java Virtual Machineで動くMetabaseしか最終的には目的を逹することはできなかった。 MetabaseはJDBCでOracle Databaseに接続できるので、NetBSDやFreeBSDのサーバーでも問題なく動かすことができた。

ただ、Business Intelligence (BI)ツールとして使おうとすると、まだ機能不足があるようにも感じた。 しかし、当初の目的である良く使うSQLを保存しておけて、ウェブブラウザーから結果を参照する用途は満足できたと感じた。 変数の扱いがもう少し柔軟だと良いのだが…。

How to use Google G suite OAuth2 with mbsync (from isync) and msmtp on NetBSD

This post will be irrelevant for Google Workspace after 2022-10-04 because OAuth out-of-band (OBB) flow will be deprecated. See my newer post which describes loopback IP flow for mbsync and msmtp.

Google will remove Less Secure app Access (LSA) from Gmail of G Suite. And I want to continue to use Gmail from NetBSD laptop with mbsync (isync) and msmtp. However I cannot find any successful report about mbsync with XOAuth2.

I do not understand OAuth2 and its derivatives at all. This post is for reporting how to use mbsync and msmtp for Gmail without LSA, not for understanding OAuth2.

Prerequiestics

I am a NetBSD desktop user. I will use pkgsrc as a package management system. The pkgsrc is portable for almost all POSIX system. You can bootstrap pkgsrc on non-NetBSD POSIX-like operating system that you are using.

I should install the following pkgsrc packages.

  • pkgsrc/mail/isync (isync-1.3.1nb2 or later)
  • pkgsrc/security/cyrus-sasl-xoauth2
  • pkgsrc/mail/msmtp
  • Be sure to use isync-1.3.1nb2 or later. isync-1.3.1nb1 has short buffer (80 bytes) for an output of PassCmd.

    Generate client ID and client secret on Google CLoud Platform console

    Goole Cloud Platform console is very complicated. I will not show how to generate clinet ID and client secret step by step.

    I should follow Google Cloud Platform console -> API&Services -> Credentials -> OAuth 2.0 Client IDs.

    Get my Access Token and Refresh Token with Google's oauth2.py

    With my client ID and client secret, I can get my (temporal) Access Token and Refresh Token with Google's oauth2.py commandline tool. I can get oauth2.py tool from https://raw.githubusercontent.com/google/gmail-oauth2-tools/master/python/oauth2.py. This oauth2.py is for Python 2.7 not for Python 3. Be sure to execute the tool with python2.7 command from pkgsrc/lang/python27.

    And I must have modern web browser like pkgsrc/www/firefox to access Google's webpage.

    An outline of my steps is as follows.

    1. Execute oauth2.py with my client ID and client secret.
    2. I can get a URI of Google's webpage and open the webpage with my Firefox.
    3. I can get a code and input the code to oauth2.py.
    4. I can get the access token and refresh token.

    I will use the refresh token to renew the access token. Do not discard my refresh token.

    I will execute oauth2.py as follows.

    $ python2.7 /opt/share/oauth2.py \
    --user=MY_GSUITE_EMAIL_ADDRESS \
    --client_id=MY_CLIENT_ID.apps.googleusercontent.com \
    --client_secret=MY_CLIENT_SECRET  \
    --generate_oauth2_token
    

    Create a script to generate a access token from the refresh token

    My access token will expire in 3600 seconds. I will regenerate my access token every e-mail send/fetch. Google's oauth2.py has no special mode to output refresh access token only. I will create the following script as /opt/bin/get_teteraorg_token.sh.

    #! /bin/sh
    /usr/pkg/bin/python2.7 /opt/share/oauth2.py \
    --user=MY_GSUITE_EMAIL_ADDRESS \
    --client_id=MY_CLIENT_ID.apps.googleusercontent.com \
    --client_secret=MY_CLIENT_SECRET \
    --generate_oauth2_token \
    --refresh_token=MY_REFRESH_TOKEN | \
    awk -F" " '{if(NR==1)print $3}'
    

    Do not ferget to execute chmod 700 /opt/bin/get_teteraorg_token.sh.

    My ~/.mbsyncrc

    My .mbsyncrc is very simple. PassCmd and AuthMechs are special.

    IMAPAccount gmail
    Host imap.gmail.com
    User MY_GSUITE_EMAIL_ADDRESS
    #AuthMechs LOGIN
    AuthMechs XOAUTH2
    PassCmd "/opt/bin/get_teteraorg_token.sh"
    SSLType IMAPS
    CertificateFile /etc/openssl/certs/ca-certificates.crt
    
    IMAPStore gmail-remote
    Account gmail
    
    MaildirStore gmail-local
    Path ~/.maildir/teteraorg-gmail/
    Inbox ~/.maildir/teteraorg-gmail/inbox
    
    Channel gmail-inbox
    Master :gmail-remote:
    Slave :gmail-local:
    
    Channel gmail-trash
    Master :gmail-remote:"[Gmail]/Trash"
    Slave :gmail-local:trash
    
    Channel gmail-sent
    Master :gmail-remote:"[Gmail]/Sent Mail"
    Slave :gmail-local:sent
    
    Channel gmail-archive
    Master :gmail-remote:
    Slave :gmail-local:
    Patterns "Arch*"
    
    Group teteraorg
    Channel gmail-inbox
    Channel gmail-sent
    Channel gmail-trash
    Channel gmail-archive
    

    My ~/.msmtprc

    For msmtp, no special patch is reaquired. msmtp has no XOAuth2 support (it seems that XOauth2 support is removed already). Use auth oauthbearer instead.

    defaults
    
    account teteraorg
    tls on
    tls_certcheck off
    tls_starttls off
    host smtp.gmail.com
    port 465
    protocol smtp
    auth oauthbearer
    from MY_GSUITE_EMAIL_ADDRESS
    user MY_GSUITE_EMAIL_ADDRESS
    passwordeval "/opt/bin/get_teteraorg_token.sh"
    
    account default: teteraorg
    

    NetBSD上で、isyncのmbsyncコマンドとmsmtpコマンドでOAuth2を使いGoogle G SuiteのGmailの電子メールを送受信する

    2022-10-04以降は以下の方法は利用できない。How to use Google Workspace OAuth2 with mbsync (from isync) and msmtp on NetBSDに英語ではあるが 対応策を記載した。

    はじめに

    どうやら延期になっているようだが、Google G SuiteのGmailでLess Secure App Access (LSA)を継続使用できないと言うお知らせが来ていた。 私は、NetBSD上でpkgsrc/mail/isyncのmbsyncコマンドを使って電子メールを受信しており、pkgsrc/mail/msmtpを使って電子メールを送信している。 これらができなくなると困ったことになってしまう。

    Oauth2アクセストークンの取得方法について把握し、 mbsyncコマンドに必要なパッチを用意し、mbsyncとmsmtpでアクセストークンの更新を自動的に実施するように設定したので書いておく。

    クライアントIDとクライアントシークレットの生成

    Google CLoud Platformコンソールについては、完全に理解しているとは言えないので、概要だけ書いておく。 https://console.cloud.google.com/より、 API&Service内のCredentialsを選択し、 OAuth 2.0 Client IDsに新規登録して、client IDとclient secretを生成しておく必要がある。

    アクセストークンとリフレッシュトークンの取得

    アクセストークンとリフレッシュトークンを取得するためには、以下のような手順に従う必要がある。 ここでは、github.com/google/gmail-oauth2-toolsoauth2.pyを利用する。ただし、これはPython 2.7用のスクリプトのようなので、Python 3.8では実行できなかった。

  • oauth2.pyにclient IDとclient secretを与え実行する。
  • 表示されたURLにFirefoxでアクセスし、確認用のコードを入手する。
  • 確認用コードをoauth2.pyに入力する。
  • refresh tokenとaccess tokenを入手する。
  • ここで、入手できたaccess tokenの有効期限は3600秒のようである。 refresh tokenは、access tokenの再生成に必要なトークンで、有効期間はないようだ。

    実際に利用したコマンドは以下のようである。

    $ python2.7 /opt/share/oauth2.py \
    --user=MY_GSUITE_EMAIL_ADDRESS \
    --client_id=MY_CLIENT_ID.apps.googleusercontent.com \
    --client_secret=MY_CLIENT_SECRET  \
    --generate_oauth2_token
    

    リフレッシュトークンを使ってアクセストークンを再生成するスクリプトを作成する

    アクセストークンは3600秒で有効期限を迎えてしまうので、リフレッシュトークンを使って再生成をする必要がある。 どうやら、毎回リフレッシュトークンを使って再生成要求をしても問題なさそうなので、 実行すると有効なアクセストークンを標準出力に出力するコマンドを用意することにした。 mbsyncにはPassCmd、msmtpにはpasswordevalという設定があり、動的にアクセストークンを与えることができるので、 それに対応するために使う。

    oauth2.pyは、アクセストークンのみを表示させるモードはないようなので、以下のような内容のコマンドを /opt/bin/get_teteraorg_token.shとして用意した。

    #! /bin/sh
    /usr/pkg/bin/python2.7 /opt/share/oauth2.py \
    --user=MY_GSUITE_EMAIL_ADDRESS \
    --client_id=MY_CLIENT_ID.apps.googleusercontent.com \
    --client_secret=MY_CLIENT_SECRET \
    --generate_oauth2_token \
    --refresh_token=MY_REFRESH_TOKEN | \
    awk -F" " '{if(NR==1)print $3}'
    

    XOAUTH2 SASLプラグインの導入

    https://github.com/moriyoshi/cyrus-sasl-xoauth2/の プラグインを利用することした。 Issue 3より後のリビジョンであれば利用できることが分かった。

    pkgsrc/security/cyrus-sasl-xoauth2として用意しているので、以下のように導入しておく。

    # cd /usr/pkgsrc/security/cyrus-sasl-xoauth2
    # make install
    

    isyncのmbsyncコマンドの設定

    まず、pkgsrc/mail/isyncを導入しておく。 必要なパッチが当てられているisync-1.3.1nb2以降である必要がある。

    必要なパッチというのは、PassCmdを実行した結果を保持するバッファーのサイズが80バイトなのを拡張するというものである。 G Suiteのアクセストークンは170バイト強になるようなので、80バイトでは不足し途中で切られてしまうため、正常にログインできない。

    ~/.mbsyncrcでの設定内容は以下のようになる。

    IMAPAccount gmail
    Host imap.gmail.com
    User MY_GSUITE_EMAIL_ADDRESS
    #AuthMechs LOGIN
    AuthMechs XOAUTH2
    PassCmd "/opt/bin/get_teteraorg_token.sh"
    SSLType IMAPS
    CertificateFile /etc/openssl/certs/ca-certificates.crt
    
    IMAPStore gmail-remote
    Account gmail
    
    MaildirStore gmail-local
    Path ~/.maildir/teteraorg-gmail/
    Inbox ~/.maildir/teteraorg-gmail/inbox
    
    Channel gmail-inbox
    Master :gmail-remote:
    Slave :gmail-local:
    
    Channel gmail-trash
    Master :gmail-remote:"[Gmail]/Trash"
    Slave :gmail-local:trash
    
    Channel gmail-sent
    Master :gmail-remote:"[Gmail]/Sent Mail"
    Slave :gmail-local:sent
    
    Channel gmail-archive
    Master :gmail-remote:
    Slave :gmail-local:
    Patterns "Arch*"
    
    Group teteraorg
    Channel gmail-inbox
    Channel gmail-sent
    Channel gmail-trash
    Channel gmail-archive
    

    msmtpコマンドの設定

    pkgsrc/mail/msmtpからmsmtp-1.8.11を導入した。 msmtpは、XOAuth2には現状では対応していないようであり、 oauthbearerを使うように設定する必要があった。 msmstpはSASLプラグインを利用している訳ではないようだ。

    # cd /usr/pkgsrc/mail/msmtp
    # make install
    

    ~/.msmtprcでの設定内容は以下のようである。

    defaults
    
    account teteraorg
    tls on
    tls_certcheck off
    tls_starttls off
    host smtp.gmail.com
    port 465
    protocol smtp
    auth oauthbearer
    from MY_GSUITE_EMAIL_ADDRESS
    user MY_GSUITE_EMAIL_ADDRESS
    passwordeval "/opt/bin/get_teteraorg_token.sh"
    
    account default: teteraorg
    

    Amazon Music FreeとAmazon Prime Music

    Amazon Music Freeというサービスが Amazon Prime会員でなくても利用できるものとして始まったという報道を、しばらく前に見ていた。 どうやら、このAmazon Music Freeとうサービスは、Amazon Prime会員では利用できないようだ。

    https://www.amazon.co.jp/music/free にアクセスしても、Amazon Music Unlimitedの案内のページになってしまう。 Amazon.co.jpからログアウトしてアクセスすると、Amazon Music Freeのウェブページが表示されるので、 Amazon Prime会員では利用できないと考えて良さそうだ。

    HTMLからWindowsの共有ファイルサーバー上のファイルやフォルダーを開く

    Windows上の共有ファイルサーバー上のリソースをHTMLから参照するようなことは、全くお勧めできないことのように思うのだが、 そうしたいという人がいるのも理解はできる。Internet Explorer 11でローカルイントラネットとして認識されている ウェブサイトからは参照可能だった。

    以下のように、file://///server/path/to/folder/or/file.suffixのように書けば、 Internet Explorer 11で開くことができた。 ただし、以下のようなHTMLの置いてあるウェブサイトが「ローカル イントラネット」のウェブサーバーとして 設定されている必要がある。 Mozilla FirefoxやGoogle Chromeではウェブサーバー上のHTMLからは開くことはできなかった。 ローカルのファイルシステム上のHTMLを開いた場合には開くことができた。

    <!DOCTYPE html>
    <html lang="ja">
    <head>
    <meta charset="utf-8" />
    <title>UNC link test</title>
    </head>
    <body>
    <p>
    <a href="file://///ruthenium/c/ryo/AND9352JP-D.PDF">
    LINK to file
    </a>
    </p>
    <p>
    <a href="file://///ruthenium/c/ryo">
    LINK to folder
    </a>
    </p>
    </body>
    </html>
    
    フォルダーを開く方は、Internet ExplorerではWindows Explorerでそのフォルダーが開いたが、 ローカルのHTMLから開いた場合のFirefoxとGoogle Chromeではブラウザー内のファイル参照画面で表示された。

    MPEG 2.5

    MPEG 2.5と言う表記を見掛けたのだが、どういうものか理解できなかった。 /usr/pkgsrc/audio を以下のように検索すると、 /usr/pkgsrc/audio/libmad/DESCR に、MPEG-2 extension to Lower Sam...