openldap and pgadmin authentication in Ubuntu

 install postgresql:

• sudo apt update
• sudo apt -y install vim bash-completion wget
• wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
• echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt
• /sources.list.d/pgdg.list
• sudo apt update
• sudo apt -y install postgresql-12 postgresql-client-12
• systemctl status postgresql.service
• systemctl status postgresql@12-main.service
• sudo su - postgres
• psql -c "alter user postgres with password 'StrongAdminP@ssw0rd'"
• psql

source: https://computingforgeeks.com/install-postgresql-12-on-ubuntu/

 

configure postgresql

pg_hba.conf

host all all 0.0.0.0/0 ldap ldapserver=ldap.doni.local ldapprefix="cn=" ldapsuffix="=, ou=members,dc=ldap,dc=doni,dc=local" ldapport=389

https://www.enterprisedb.com/blog/making-postgres-talk-ldap-starttls

postgresql.conf

listen_addresses = '*'

 

install openldap

apt update

apt install slapd ldap-utils

dpkg-reconfigure slapd

• Omit OpenLDAP server configuration? No
• DNS domain name?
      This option will determine the base structure of your directory path. Read the message to understand exactly how this will be implemented. You can actually select whatever value you’d like, even if you don’t own the actual domain. However, this tutorial assumes you have a proper domain name for the server, so you should use that. We’ll use example.com throughout the tutorial.
• Organization name?
      For this guide, we will be using example as the name of our organization. You may choose anything you feel is appropriate.
  Administrator password? enter a secure password twice
• Database backend? MDB
• Remove the database when slapd is purged? No
• Move old database? Yes
• Allow LDAPv2 protocol? No
  

sudo ufw allow ldap
test: ldapwhoami -H ldap:// -x
sudo apt-get install phpldapadmin
    sudo nano /etc/phpldapadmin/config.php
$servers->setValue('server','name','Example LDAP');
$servers->setValue('server','base', array('dc=example,dc=com'));
$servers->setValue('login','bind_id','cn=admin,dc=example,dc=com');
$config->custom->appearance['hide_template_warning'] = true;

https://www.digitalocean.com/community/tutorials/how-to-install-and-configure-openldap-and-phpldapadmin-on-ubuntu-16-04
https://www.thegeekstuff.com/2015/02/openldap-add-users-groups/
https://www.thegeekstuff.com/2015/01/openldap-linux/

 

ldap sample user & group

# base
dn: dc=ldap,dc=doni,dc=local
objectClass: dcObject
o: ldap.doni.local

dn: ou=members,dc=ldap,dc=doni,dc=local
description: My Organization Users
objectClass: organizationalUnit
objectClass: top
ou: members

# ---------------------------------------------------------

# member group

dn: cn=userone,ou=members,dc=ldap,dc=doni,dc=local
cn: userone
sn: sn userone
#gidnumber: 500
#givenname: User One
#uid: userone
#uidnumber: 1001
objectClass: top
objectClass: inetOrgPerson
userpassword: {MD5}b427ebd39c845eb5417b7f7aaf1f9724

dn: cn=usertwo,ou=members,dc=ldap,dc=doni,dc=local
cn: usertwo
sn: sn usertwo
#gidnumber: 500
#givenname: User Two
#uid: usertwo
#uidnumber: 1002
objectClass: top
objectClass: inetOrgPerson
userpassword: {MD5}b427ebd39c845eb5417b7f7aaf1f9724

test: ldapsearch -W -D "cn=userone,ou=members,dc=ldap,dc=doni,dc=local" -b dc=ldap,dc=doni,dc=local

https://www.thegeekstuff.com/2015/02/openldap-add-users-groups/

https://www.thegeekstuff.com/2015/01/openldap-linux/

https://computingforgeeks.com/install-and-configure-openldap-phpldapadmin-on-ubuntu/

https://www.youtube.com/watch?v=x-ayyEcb0IU

  

install pgadmin4 server mode

sudo apt update
sudo apt install libgmp3-dev libpq-dev libapache2-mod-wsgi-py3
sudo mkdir -p /var/lib/pgadmin4/sessions
sudo mkdir /var/lib/pgadmin4/storage
sudo mkdir /var/log/pgadmin4
sudo chown -R sammy:sammy /var/lib/pgadmin4 // sammy is ubuntu user
sudo chown -R sammy:sammy /var/log/pgadmin4
cd environments/
source my_env/bin/activate
python -m pip install -U pip
wget https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v5.1/pip/pgadmin4-5.1-py3-none-any.whl
python -m pip install wheel
python -m pip install pgadmin4-5.1-py3-none-any.whl
nano my_env/lib/python3.6/site-packages/pgadmin4/config_local.py
nano my_env/lib/python3.6/site-packages/pgadmin4/config_local.py

LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'
SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
STORAGE_DIR = '/var/lib/pgadmin4/storage'
SERVER_MODE = True

python my_env/lib/python3.6/site-packages/pgadmin4/setup.py
deactivate
sudo chown -R www-data:www-data /var/lib/pgadmin4/
sudo chown -R www-data:www-data /var/log/pgadmin4/
sudo nano /etc/apache2/sites-available/pgadmin4.conf

<VirtualHost *>
    ServerName your_server_ip

    WSGIDaemonProcess pgadmin processes=1 threads=25 python-home=/home/sammy/environments/my_env
    WSGIScriptAlias / /home/sammy/environments/my_env/lib/python3.6/site-packages/pgadmin4/pgAdmin4.wsgi

    <Directory "/home/sammy/environments/my_env/lib/python3.6/site-packages/pgadmin4/">
        WSGIProcessGroup pgadmin
        WSGIApplicationGroup %{GLOBAL}
        Require all granted
    </Directory>
</VirtualHost>

 

configure pgadmin

my_env/lib/python3.6/site-packages/pgadmin4/config.py

AUTHENTICATION_SOURCES = ['ldap', 'internal']
LDAP_AUTO_CREATE_USER = True
LDAP_CONNECTION_TIMEOUT = 60
LDAP_SERVER_URI = 'ldap://ldap.doni.local:389'
LDAP_USERNAME_ATTRIBUTE = 'cn'
LDAP_ANONYMOUS_BIND = False
LDAP_BASE_DN = 'ou=members,dc=ldap,dc=doni,dc=local'
LDAP_SEARCH_BASE_DN = 'dc=ldap,dc=doni,dc=local'
LDAP_SEARCH_FILTER = '(objectclass=*)'
LDAP_SEARCH_SCOPE = 'SUBTREE'
LDAP_USE_STARTTLS = False
LDAP_CA_CERT_FILE = ''
LDAP_CERT_FILE = ''
LDAP_KEY_FILE = ''

https://www.pgadmin.org/docs/pgadmin4/4.29/enabling_ldap_authentication.html#

 

sudo a2dissite 000-default.conf
sudo a2ensite pgadmin4.conf
apachectl configtest
sudo systemctl restart apache2