View source for OpenLDAP/BackSQL

==SQL Schema==
<syntaxhighlight lang="bash">
cat << DDL | psql -U ${USER}
CREATE DATABASE openldap;
CREATE USER openldap WITH ENCRYPTED PASSWORD 'sadaqah!';
GRANT ALL PRIVILEGES ON DATABASE openldap TO openldap;
ALTER DATABASE openldap OWNER TO openldap;
DDL
</syntaxhighlight>
----
<syntaxhighlight lang="bash">
wget -cq https://www.openldap.org/software/download/OpenLDAP/openldap-release/openldap-2.6.7.tgz -P ${HOME}/Downloads/
tar -xzf ${HOME}/Downloads/openldap-2.6.7.tgz -C ${HOME}/Downloads
rm  -rf  ${HOME}/Downloads/openldap-2.6.7.tgz

cat openldap-2.6.7/servers/slapd/back-sql/rdbms_depend/pgsql/backsql_create.sql
cat openldap-2.6.7/servers/slapd/back-sql/rdbms_depend/pgsql/backsql_drop.sql
cat openldap-2.6.7/servers/slapd/back-sql/rdbms_depend/pgsql/slapd.conf 

LXD_BRIDGE_IP="$(ip addr show lxdbr0|awk '/inet / {print $2}'|cut -d '/' -f 1)"
echo -n password: ; read -s PGPASSWORD; export PGPASSWORD; echo
# password: sadaqah!

   cat openldap-2.6.7/servers/slapd/back-sql/rdbms_depend/pgsql/backsql_create.sql|psql -dopenldap -Uopenldap -h${LXD_BRIDGE_IP}
echo '\dt+'|psql -dopenldap -Uopenldap -h${LXD_BRIDGE_IP}
rm -rf openldap-2.6.7
</syntaxhighlight>

==SQL Backend » ODBC==
{|
| colspan="3" |
<syntaxhighlight lang="bash">
lxc launch ubuntu:24.04 openldap
lxc list -c=n -f=json|jq -r '.[]|select(.name=="openldap")|.status'
</syntaxhighlight>

|-
| colspan="3" |
----
|-
| colspan="3" |
<syntaxhighlight lang="bash">
# install `psql` & `odbc` driver
cat <<'EXE' | lxc exec openldap -- bash
apt-get update
apt-get install -y postgresql-client odbc-postgresql unixodbc
apt-get clean
EXE
</syntaxhighlight>

|-
| colspan="3" |
----
|-
| colspan="3" |
<syntaxhighlight lang="bash">
# extract lxd bridge network ip using `ip address show` command
LXD_BRIDGE_IP="$(ip addr show lxdbr0 | awk '/inet / {print $2}' | cut -d '/' -f 1)"
cat << EXE | lxc exec openldap -- bash
echo "${LXD_BRIDGE_IP} host.lxd.internal"|tee -a /etc/hosts
ping -c5 host.lxd.internal
EXE
</syntaxhighlight>

|-
| colspan="3" |
----
|-
| colspan="3" |
<syntaxhighlight lang="sql">
# psql connection test
cat <<'EXE' | lxc exec openldap -- bash
export PGPASSWORD='sadaqah!';echo
cat <<'SQL' | psql -dopenldap -Uopenldap -hhost.lxd.internal
SELECT current_database(), CURRENT_USER, CURRENT_TIMESTAMP;
SQL
EXE
</syntaxhighlight>

|-
| colspan="3" |
----
|-
| colspan="3" |
<syntaxhighlight lang="ini">
# `PostgreSQL` odbc driver config
cat <<'EXE' | lxc exec openldap -- bash
cat <<'INI' | tee -a /etc/odbcinst.ini >/dev/null
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver      = /usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so
Setup       = /usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so

INI
cat /etc/odbcinst.ini
EXE
</syntaxhighlight>

|-
| colspan="3" |
----
|-
| colspan="3" |
<syntaxhighlight lang="ini">
# `ldap` odbc datasource config
cat <<'EXE' | lxc exec openldap -- bash
cat <<'INI' | tee -a /etc/odbc.ini >/dev/null
[ldap]
Description = OpenLDAP PostgreSQL DB
Servername  = host.lxd.internal
Driver      = PostgreSQL
Database    = openldap
Username    = openldap
Password    = sadaqah!
Port        = 5432

INI
cat /etc/odbc.ini
EXE
</syntaxhighlight>

|-
| colspan="3" |
----
|-
| colspan="3" |
<syntaxhighlight lang="sql">
# `ldap` odbc datasource test
cat <<'SQL' | lxc exec openldap -- isql -v ldap openldap sadaqah!
SELECT current_database(), CURRENT_USER, CURRENT_TIMESTAMP;
SQL
</syntaxhighlight>

|-
| colspan="3" |
----
|-
| valign="top" |
<syntaxhighlight lang="bash">
lxc snapshot openldap base:2.6.7
</syntaxhighlight>

| valign="top" |
<syntaxhighlight lang="bash">
lxc publish  openldap/base:2.6.7 --alias openldap/base:2.6.7
</syntaxhighlight>

| valign="top" |
<syntaxhighlight lang="bash">
lxc restore  openldap base:2.6.7
</syntaxhighlight>

|}

==SQL Backend » Config==
{|
| colspan="3" |
<syntaxhighlight lang="bash">
# install `slapd` and `ldap-utils`
cat <<'EXE' | lxc exec openldap -- bash
apt-get update
echo 'slapd slapd/password1 password sadaqah!' | debconf-set-selections
echo 'slapd slapd/password2 password sadaqah!' | debconf-set-selections
DEBIAN_FRONTEND=noninteractive apt-get install -y slapd ldap-utils
ls -lah /usr/lib/ldap | grep back_sql
apt-get clean
EXE
</syntaxhighlight>

|-
| colspan="3" |
----
|-
| colspan="3" |
<syntaxhighlight lang="bash">
cat <<'EXE' | lxc exec openldap -- bash
OLC_LOG_LEVEL=$(mktemp -u)
cat <<'INI' | tee ${OLC_LOG_LEVEL} >/dev/null
dn: cn=config
changetype: modify
replace: olcLogLevel
olcLogLevel: stats config
INI
ldapadd -Y EXTERNAL -H ldapi:/// -f ${OLC_LOG_LEVEL}
EXE
</syntaxhighlight>

|-
| colspan="3" |
----
|-
| colspan="3" |
<syntaxhighlight lang="bash">
echo 'systemctl restart slapd' | lxc exec openldap -- bash
echo 'tail -n10 -f /var/log/syslog' | lxc exec openldap -- bash
echo "ldapsearch -Y EXTERNAL -H ldapi:/// -b cn=config '(olcLogLevel=*)'" | lxc exec openldap -- bash
echo "ldapsearch -Y EXTERNAL -H ldapi:/// -b cn=config '(olcRootDN=*)' olcRootDN" | lxc exec openldap -- bash
</syntaxhighlight>

|-
| colspan="3" |
----
|-
| colspan="3" |
<syntaxhighlight lang="bash">
# checking ldap `back-sql` module
cat <<'EXE' | lxc exec openldap -- bash
ldapsearch -Y EXTERNAL -H ldapi:/// -b cn=module{0},cn=config
printf '\n=============================================================\n'
ldapsearch -Y EXTERNAL -H ldapi:/// -b cn=module{0},cn=config | grep -i back_sql
EXE
</syntaxhighlight>

|-
| colspan="3" |
----
|-
| colspan="3" |
<syntaxhighlight lang="bash">
# sql backend module load
cat <<'EXE' | lxc exec openldap -- bash
BACK_SQL_INI=$(mktemp -u)
cat <<'INI' | tee ${BACK_SQL_INI} >/dev/null
dn: cn=module{0},cn=config
changetype: modify
add: olcModuleLoad
olcModuleLoad: back_sql
INI
ldapadd -Y EXTERNAL -H ldapi:/// -f ${BACK_SQL_INI}
EXE
</syntaxhighlight>

|-
| colspan="3" |
----
|-
| colspan="3" |
<syntaxhighlight lang="bash">
# admin password added using `slappasswd`
# slappasswd -h {SSHA} -s sadaqah!

cat <<'EXE' | lxc exec openldap -- bash
ADMIN_PASS_INI=$(mktemp -u)
cat <<'INI' | tee ${ADMIN_PASS_INI} >/dev/null
dn: olcDatabase={0}config,cn=config
changetype: modify
replace: olcRootPW
olcRootPW: {SSHA}Pg5ICKylRb08GzjwP9/M7gi8SQ8re9BA
INI
ldapmodify -Y EXTERNAL -H ldapi:/// -f ${ADMIN_PASS_INI}
EXE
</syntaxhighlight>

|-
| colspan="3" |
----
|-
| colspan="3" |
<syntaxhighlight lang="bash">
# postgresql sql backend configuration
cat <<'EXE' | lxc exec openldap -- bash
PG_BACK_SQL=$(mktemp -u)
cat <<'INI' | tee ${PG_BACK_SQL} >/dev/null
dn: olcDatabase={1}sql,cn=config
objectClass: olcDatabaseConfig
objectClass: olcSqlConfig
olcDatabase: {1}sql
olcDbSqlBaseObject: dc=chorke,dc=org
olcDbSqlDBName: openldap
olcDbSqlDBUser: openldap
olcDbSqlDBPass:: c2FkYXFhaCE=
olcDbSqlDBURI: postgresql://host.lxd.internal/openldap
olcDbSqlUpperCase: FALSE
olcSuffix: dc=chorke,dc=org
INI
ldapadd -Y EXTERNAL -H ldapi:/// -f ${PG_BACK_SQL}
EXE
</syntaxhighlight>
----
<syntaxhighlight lang="bash">
echo "ldapsearch -Y EXTERNAL -H ldapi:/// -b cn=config '(objectClass=olcSqlConfig)'"|lxc exec openldap -- bash
echo "ldapsearch -Y EXTERNAL -H ldapi:/// -b cn=config '(objectClass=*)'"|lxc exec openldap -- bash|grep sql
echo 'ldapsearch -Y EXTERNAL -H ldapi:/// -b cn=module{0},cn=config'|lxc exec openldap -- bash
</syntaxhighlight>

|-
| colspan="3" |
----
|-
| valign="top" |
<syntaxhighlight lang="bash">
lxc snapshot openldap ldap:2.6.7
</syntaxhighlight>

| valign="top" |
<syntaxhighlight lang="bash">
lxc publish  openldap/slapd:2.6.7 --alias openldap/ldap:2.6.7
</syntaxhighlight>

| valign="top" |
<syntaxhighlight lang="bash">
lxc restore  openldap ldap:2.6.7
</syntaxhighlight>

|}

==Playground==
{|
| valign="top" |
<syntaxhighlight lang="bash">
lxc image info ubuntu:noble --vm|yq .Aliases
lxc image info ubuntu:24.04 --vm|yq .Aliases

lxc image info ubuntu:noble     |yq .Aliases
lxc image info ubuntu:24.04     |yq .Aliases
</syntaxhighlight>

| colspan="2" |
<syntaxhighlight lang="bash">
lxc image list ubuntu: os=ubuntu release=noble type=virtual-machine
lxc image list ubuntu: os=ubuntu release=noble type=virtual-machine

lxc image list ubuntu: os=ubuntu release=noble type=container
lxc image list ubuntu: os=ubuntu release=noble type=container
</syntaxhighlight>

|-
| colspan="3" |
----
|-
| valign="top" |
<syntaxhighlight lang="bash">
lxc image list ubuntu:24.04
lxc launch ubuntu:24.04 openldap
lxc launch ubuntu:40d8df642812 openldap
</syntaxhighlight>

| colspan="2" |
<syntaxhighlight lang="bash">
lxc list status=running name=openldap --format=json |jq  -r '.[].state.network.[].addresses'
lxc list status=running name=openldap --format=yaml |yq  -r '.[].state.network.[].addresses'
lxc info openldap|yq '.Resources.["Network usage"][]["IP addresses"].inet'
</syntaxhighlight>

|-
| colspan="3" |
----
|-
| valign="top" |
<syntaxhighlight lang="bash">
systemctl list-dependencies graphical.target
dpkg -l | grep 'gnome-shell-extension'
apt list --installed|grep linux-image
</syntaxhighlight>

| colspan="2" |
<syntaxhighlight lang="bash">
lxc snapshot openldap base:2.6.7
lxc publish  openldap/base:2.6.7 --alias openldap/base:2.6.7
lxc restore  openldap base:2.6.7
</syntaxhighlight>

|-
| colspan="3" |
----
|-
| valign="top" |
<syntaxhighlight lang="bash">
cat <<-'EXE'|sudo bash
umount /var/snap/firefox/common/host-hunspell
snap remove firefox
EXE
</syntaxhighlight>

| valign="top" |
<syntaxhighlight lang="bash">
cat <<-'EXE'|sudo bash
snap disable lxd
snap remove --purge lxd
EXE
</syntaxhighlight>

| valign="top" |
 systemctl list-dependencies --type=service --state=running
 systemctl list-dependencies --type=service --state=active
 systemctl list-dependencies docker.service
 systemctl list-dependencies --all

|-
| colspan="3" |
----
|-
| colspan="3" |
<syntaxhighlight lang="bash">
LXD_BRIDGE_IP="$(lxc network ls --format=json|jq -r '.[]|select(.name=="lxdbr0")|.config."ipv4.address"|split("/")[0]')"
LXD_BRIDGE_IP="$(ip addr show lxdbr0 | awk '/inet / {print $2}' | cut -d '/' -f 1)"
</syntaxhighlight>
|-
| colspan="3" |
----
|-
| valign="top" |
<syntaxhighlight lang="bash">
ls -lah /etc/ldap/slapd.d/cn\=config/
ls -alh /etc/ldap/slapd.d/
ls -alh /etc/ldap/schema/
ls -alh /etc/ldap/
</syntaxhighlight>

| valign="top" |
<syntaxhighlight lang="bash">
cat /etc/ldap/slapd.d/cn\=config/cn\=schema.ldif
cat /etc/ldap/slapd.d/cn\=config.ldif
cat /etc/ldap/ldap.conf
cat /etc/default/slapd
</syntaxhighlight>

| valign="top" |
<syntaxhighlight lang="bash">
cat /etc/ldap/slapd.d/cn\=config/olcDatabase\=\{-1\}frontend.ldif
cat /etc/ldap/slapd.d/cn\=config/olcDatabase\=\{0\}config.ldif
cat /etc/ldap/slapd.d/cn\=config/olcDatabase\=\{1\}mdb.ldif 
cat /etc/ldap/slapd.d/cn\=config/cn\=module\{0\}.ldif 
</syntaxhighlight>
|-
| colspan="3" |
----
|-
| valign="top" |
<syntaxhighlight lang="bash">
systemctl status slapd
</syntaxhighlight>

| valign="top" |
<syntaxhighlight lang="bash">

</syntaxhighlight>

| valign="top" |
<syntaxhighlight lang="bash">

</syntaxhighlight>

|}

==References==
{|
|valign="top"|
* [https://www.iana.org/requests/pfi6-atc-0y6/ LDAP » OID » Chorke, Org » Request]
* [https://www.iana.org/requests/pfho-zca-0q4/ LDAP » OID » Shahed, Inc » Request]
* [https://www.iana.org/requests/pfzm-gui-gno/ LDAP » OID » Chorke, Inc » Request]
* [https://www.iana.org/assignments/enterprise-numbers/?q=Chorke+Inc LDAP » OID » Chorke, Org » <code>61820</code>]
* [https://www.iana.org/assignments/enterprise-numbers/?q=Shahed+Inc LDAP » OID » Shahed, Inc » <code>61752</code>]
* [https://www.iana.org/assignments/enterprise-numbers/?q=Chorke     LDAP » OID » Chorke, Inc » <code>62865</code>]
* [http://www.openldap.org/doc/admin24/schema.html#Extending%20Schema LDAP » Schema Specification]
* [https://www.openldap.org/faq/data/cache/978.html LDAP » Back SQL » Config]
* [https://serverfault.com/questions/53574/ LDAP » Back SQL]
* [[OpenLDAP|LDAP » Config]]

|valign="top"|
* [https://www.digitalocean.com/community/tutorials/how-to-manage-and-use-ldap-servers-with-openldap-utilities LDAP » Manage & Use with OpenLDAP Utilities]
* [http://pen.iana.org/pen/PenApplication.page LDAP » Internet Assigned Numbers Authority]
* [[Build LDAP Docker Image from Ubuntu | LDAP » Build Docker Image from Ubuntu]]
* [https://www.openldap.org/software/release/changes_lts.html LDAP » 2.5.17 LTS Release Changes]
* [https://dev.mysql.com/blog-archive/accessing-the-same-data-through-ldap-and-sql/ LDAP » MySQL Store & Fetch Data]
* [https://www.openldap.org/doc/admin24/guide.html LDAP » Administrator's Guide]
* [https://github.com/breisig/phpLDAPadmin LDAP » Clone phpLDAPadmin]
* [https://www.openldap.org/software/download/ LDAP » Download]

|valign="top"|

|-
| colspan="3" |
----
|-
| valign="top" |
* [[Ubuntu/Raspberry Pi]]
* [[Ubuntu Upgrade]]
* [[ActiveMQ]]
* [[Minikube]]
* [[Keycloak]]
* [[Hadoop]]
* [[Jenkins]]
* [[WildFly]]
* [[Spark]]
* [[MinIO]]

| valign="top" |
* [[Alpine/Morefine]]
* [[Ruby on Rails]]
* [[TensorFlow]]
* [[Homebrew]]
* [[Linuxbrew]]
* [[PyEnv]]
* [[TMux]]
* [[7Zip]]
* [[Zip]]
* [[Tar]]

| valign="top" |
* [[Linux Service Creation]]
* [[Bash/Port/Forward]]
* [[Linux Mount Drive]]
* [[YouTube/Channel]]
* [[Bash/Network]]
* [[Bash/RAM]]
* [[Bash/CPU]]
* [[Bash/Port]]
* [[Bash]]
* [[Port]]

|-
|colspan="3"|
----
|-
|valign="top"|
* [[Private Enterprise Number]]
* [[Linux User Creation]]
* [[Linux Containers]]
* [[PostgreSQL]]
* [[IPTables]]
* [[MySQL]]
* [[CIDR]]
* [[UFW]]
* [[YQ Tool|YQ]]
* [[JQ Tool|JQ]]

|valign="top"|
* [[Chorke Academia Backup]]
* [[Cloud Computing Cost]]
* [[Helm/PostgreSQL|Helm » PostgreSQL]]
* [[Helm/MariaDB|Helm » MariaDB]]
* [[Benchmarks]]
* [[Helm]]

|valign="top"|

|}