PostgreSQL Notes

How to drop all tables sharing the same prefix in PostgreSQL?

DO
$do$
DECLARE
   _tbl text;
BEGIN
FOR _tbl  IN
    SELECT quote_ident(table_schema) || '.'
        || quote_ident(table_name)
    FROM   information_schema.tables
    WHERE  table_name LIKE '[prefix]' || '%'
    AND    table_schema NOT LIKE 'pg\_%'
LOOP
-- RAISE NOTICE '%',
  EXECUTE 'DROP TABLE ' || _tbl || ' CASCADE';
END LOOP;
END
$do$;
  • Non-Cascade version:
EXECUTE 'DROP TABLE ' || _tbl;

How to drop all functions in PostgreSQL?

DO
$do$
DECLARE
   _sql text;
BEGIN
   SELECT INTO _sql
          string_agg(format('DROP %s %s;'
                          , CASE WHEN proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END
                          , oid::regprocedure)
                   , E'\n')
   FROM   pg_proc
   WHERE  pronamespace = 'public'::regnamespace;

   IF _sql IS NOT NULL THEN
      RAISE NOTICE '%', _sql;
      -- EXECUTE _sql;
   ELSE 
      RAISE NOTICE 'No fuctions found in schema %', quote_ident(_schema);
   END IF;
END
$do$  LANGUAGE plpgsql;

Running PostgreSQL in Docker on my Mac

It’s extremely convenient to run your local tools, like RDMS, development environments and so on on Docker containers on Mac. To be honest with you, I hate installing a bunch of software on my Mac, and that’s the main reason why do I use containers for my local development.

Before doing that manual, make sure your Docker is installed on Mac. To do so, you can go to https://www.docker.com/products/docker-desktop and download the latest version of Docker for your Mac.

So, how do I can use PostgreSQL on Mac in a Docker container? Let’s create a new Project folder, or you may use your own where docker-compose files.

mkdir ~/Projects
cd ~/Projects

And creating a docker-compose.yml file with the following code:

#File: docker-compose.yml 
version: "3"
services:
  db:
    image: "postgres:11"
    container_name: "local_postgres"
    ports:
      - "54320:5432"
    volumes:
      - dbdata:/var/lib/postgresql/data
    environment:
      - POSTGRES_HOST_AUTH_METHOD=trust
      - POSTGRES_PASSWORD=somesecretpassword
volumes:
  dbdata:

So, a quick clarification on what we do here. We use postgres:11 image, naming containers as local_postgres, expose port 54320, store data on dbdata volume, use passwordless access to database (as you we do everything locally), and use somesecretpassword as password. Pretty straight forward, ha?

Ok, let’s build it and run it:

docker-compose up -d

You shouldn’t see any errors, and let’s see logs with the following command:

docker logs -f local_postgres

And if we can connect to database, like this:

docker exec -it local_postgres psql -U postgres

Hit CTRL+D to exit from the prompt.

Create a database

So, to create a database, you can run the following command, where new_database is database new you want to create.

docker exec -it my_postgres psql -U postgres -c "create database new_database"

You can use a database connection now with the following login details:

host='localhost',
port=54320,
dbname='hew_database',
user='postgres',
password='somesecretpassword'

That’s pretty much it. Easy, right?

Please comment below if you have any issues or questions!

ElementaryOS: Resolution fix for 2560×1440

Edit /usr/share/X11/xorg.conf.d/10-monitor.conf and paste the following:

Section "Monitor"
  Identifier "Monitor0"
  Section "Monitor"
  Identifier "Monitor0"
  Modeline "2560x1440_60.00"  661.25  2560 2784 3064 3568 1440 1443 1448 1545 -hsync +vsync
EndSection
Section "Screen"
  Identifier "Screen0"
  Device "Virtual1"
  Monitor "Monitor0"
  DefaultDepth 24
  SubSection "Display"
    Depth 24
    Modes "2560x1440_60.00"
  EndSubSection
EndSection

And reboot system with sudo reboot

# sudo reboot

Thanks!

SendMail function in Golang

Easy-to-use function to send HTML and plain-text emails in Go

func SendMail(addr, from, subject, body string, to []string) error {
	r := strings.NewReplacer("\r\n", "", "\r", "", "\n", "", "%0a", "", "%0d", "")
	c, err := smtp.Dial(addr)
	if err != nil {
		return err
	}
	defer c.Close()
	if err = c.Mail(r.Replace(from)); err != nil {
		return err
	}
	for i := range to {
		to[i] = r.Replace(to[i])
		if err = c.Rcpt(to[i]); err != nil {
			return err
		}
	}
	w, err := c.Data()
	if err != nil {
		return err
	}
	msg := "To: " + strings.Join(to, ",") + "\r\n" +
		"From: " + from + "\r\n" +
		"Subject: " + subject + "\r\n" +
		"Content-Type: text/html; charset=\"UTF-8\"\r\n" +
		"Content-Transfer-Encoding: base64\r\n" +
		"\r\n" + base64.StdEncoding.EncodeToString([]byte(body))
	_, err = w.Write([]byte(msg))
	if err != nil {
		return err
	}
	err = w.Close()
	if err != nil {
		return err
	}
	return c.Quit()
}

Usage:

SendMail("127.0.0.1:25", (&mail.Address{"from name", "[email protected]"}).String(), "Email Subject", "message body", []string{(&mail.Address{"to name", "[email protected]"}).String()})

Apache 2.4.6 + mod_wsgi + Python 3.6 + Django 2.2.6 on CentOS 7

There are many how-to’s on installing Apache with mod_wsgi, python 3.x and Django, but nothing worked for me, looks like mod_wsgi libraries installed by default with yum use Python 2.x, and I keep getting the following error message:

[Mon Oct 14 09:42:07.713418 2019] [:error] [pid 18066] [remote 10.30.40.31:0] mod_wsgi (pid=18066): Target WSGI script '/usr/local/val/app/app/wsgi.py' cannot be loaded as Python module.
[Mon Oct 14 09:42:07.713497 2019] [:error] [pid 18066] [remote 10.30.40.31:0] mod_wsgi (pid=18066): Exception occurred processing WSGI script '/usr/local/val/app/wsgi.py'.
[Mon Oct 14 09:42:07.713560 2019] [:error] [pid 18066] [remote 10.30.40.31:0] Traceback (most recent call last):
[Mon Oct 14 09:42:07.713596 2019] [:error] [pid 18066] [remote 10.30.40.31:0]   File "/usr/local/val/app/app/wsgi.py", line 12, in <module>
[Mon Oct 14 09:42:07.713701 2019] [:error] [pid 18066] [remote 10.30.40.31:0]     from django.core.wsgi import get_wsgi_application
[Mon Oct 14 09:42:07.713718 2019] [:error] [pid 18066] [remote 10.30.40.31:0]   File "/usr/local/val/venv/lib/python3.6/site-packages/django/__init__.py", line 1, in <module>
[Mon Oct 14 09:42:07.713789 2019] [:error] [pid 18066] [remote 10.30.40.31:0]     from django.utils.version import get_version
[Mon Oct 14 09:42:07.713804 2019] [:error] [pid 18066] [remote 10.30.40.31:0]   File "/usr/local/val/venv/lib/python3.6/site-packages/django/utils/version.py", line 71, in <module>
[Mon Oct 14 09:42:07.713886 2019] [:error] [pid 18066] [remote 10.30.40.31:0]     @functools.lru_cache()
[Mon Oct 14 09:42:07.713914 2019] [:error] [pid 18066] [remote 10.30.40.31:0] AttributeError: 'module' object has no attribute 'lru_cache'

Installing Apache and devel package

$ yum install httpd httpd-devel wget

So, I had to compile mod_wsgi library from sources, which actually helped me to fix the error above with running Django app with wsgi.

Let’s make sure we have Python 3.6 or 3.7 installed or actually it would work with any Python 3 minor version.

$ python3
Python 3.6.8 (default, Aug  7 2019, 17:28:10) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux
Type "help", "copyright", "credits" or "license" for more information.

If we have Python 3 installed, we need to make sure we got mod_wsgi removed, which was installed by yum.

$ yum remove mod_wsgi

Downloading, compiling and installing a mod_wsgi package by doing this:

$ cd /opt
$ wget https://files.pythonhosted.org/packages/25/d8/1df4ba9c051cd88e02971814f0867274a8ac821baf983b6778dacd6e31f7/mod_wsgi-4.6.8.tar.gz
$ tar -zxvf mod_wsgi-4.6.8.tar.gz
$ cd mod_wsgi-4.6.8
$ ./configure --with-python=/usr/bin/python3.6
$ LD_RUN_PATH=/usr/local/lib make
$ sudo make install

Add a new entry to /etc/ld.so.conf

include ld.so.conf.d/*.conf
include /usr/local/lib

Run:

ldconfig

Make sure mod_wsgi linked properly:

# ldd usr/lib64/httpd/modules/mod_wsgi.so
	linux-vdso.so.1 =>  (0x00007ffd5bdd7000)
	libpython3.6m.so.1.0 => /lib64/libpython3.6m.so.1.0 (0x00007fe5ab02b000)
	libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fe5aae0f000)
	libdl.so.2 => /lib64/libdl.so.2 (0x00007fe5aac0b000)
	libutil.so.1 => /lib64/libutil.so.1 (0x00007fe5aaa08000)
	libm.so.6 => /lib64/libm.so.6 (0x00007fe5aa706000)
	libc.so.6 => /lib64/libc.so.6 (0x00007fe5aa338000)
	/lib64/ld-linux-x86-64.so.2 (0x00007fe5ab78f000)

Install Django:

$ pip3.6 install --upgrade pip
$ pip3.6 install Django

Verify Django installed properly:

$ django-admin version
2.2.6

My httpd configuration file looks like this:

Alias /static /usr/local/val/app/static
<Directory /usr/local/val/app/static>
 Require all granted
</Directory>

<Directory /usr/local/val/app/app>
   <Files wsgi.py>
       Require all granted
   </Files>
</Directory>

WSGIDaemonProcess app python-path=/usr/local/val/app:/usr/local/val/venv/lib/python3.6/site-packages python-home=/usr/local/val/env
WSGIProcessGroup app
WSGIScriptAlias / /usr/local/val/app/app/wsgi.py
WSGIPassAuthorization On

Where app is the name of my django app, and val is directory where app is located.

Before that I created virtual environment by running this:

$ python3 -m venv /usr/local/val/env

Test if everything works correctly, you may probably also want to add your hosts to ALLOWED_HOSTS in your settings.py file and setup static files by specifying the following paths in your settings.py

STATIC_ROOT = '/usr/local/val/app/static/'
STATIC_URL = '/static/'

Let me know if you have any questions

Thanks!

Nagios environment variables

As soon as Nagios is managed by systemd, you have to change the passing environment variables from /etc/sysconfig/nagios to systemd

Edit the file

$ vi /etc/systemd/system/multi-user.target.wants/nagios.service

Add Environment=”” or EnvironmentFile=”” to [service] section

[Service]
Type=forking
ExecStartPre=/usr/sbin/nagios -v /etc/nagios/nagios.cfg
ExecStart=/usr/sbin/nagios -d /etc/nagios/nagios.cfg
ExecStop=/usr/bin/kill -s TERM ${MAINPID}
ExecStopPost=/usr/bin/rm -f /var/spool/nagios/cmd/nagios.cmd
ExecReload=/usr/bin/kill -s HUP ${MAINPID}
Environment="PYTHONHTTPSVERIFY=1"

Restart systemd and nagios

$ sudo systemctl daemon-reload
$ sudo systemctl restart nagios

Check the process environment

$ cat /proc/30195/environ
LANG=en_US.UTF-8PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/binPYTHONHTTPSVERIFY=1
$

That will pass all variables into Nagios daemon

Nagios, the Nagios logo, and Nagios graphics are the servicemarks, trademarks, or registered trademarks owned by Nagios Enterprises.

Let me know if you have any issues with monitoring. Will be glad to help 🙂

Open-Xchange IMAP: STARTTLS failure

During some experiments with open-sources code, found an issue with STARTTLS on the latest open-xchange appsuite 7.10.1-Rev9

Something like this:

com.openexchange.exception.OXException: LGI-0003 Categories=ERROR Message='Unknown problem: "STARTTLS failure".' exceptionID=-1484511495-7
	at com.openexchange.exception.OXExceptionFactory.create(OXExceptionFactory.java:175)
	at com.openexchange.exception.OXExceptionFactory.create(OXExceptionFactory.java:165)
	at com.openexchange.exception.OXExceptionFactory.create(OXExceptionFactory.java:138)
	at com.openexchange.authentication.LoginExceptionCodes.create(LoginExceptionCodes.java:267)
	at com.openexchange.authentication.imap.impl.IMAPAuthentication.handleLoginInfo(IMAPAuthentication.java:373)
	at com.openexchange.authentication.service.Authentication.login(Authentication.java:111)
	at com.openexchange.authentication.service.Authentication.login(Authentication.java:98)
	at com.openexchange.login.internal.NormalLoginMethod.doAuthentication(NormalLoginMethod.java:83)
	at com.openexchange.login.internal.LoginPerformer.doLogin(LoginPerformer.java:216)
	at com.openexchange.login.internal.LoginPerformer.doLogin(LoginPerformer.java:157)
	at com.openexchange.login.internal.LoginPerformer.doLogin(LoginPerformer.java:145)
	at com.openexchange.ajax.login.Login$1.doLogin(Login.java:109)
	at com.openexchange.ajax.login.AbstractLoginRequestHandler.loginOperation(AbstractLoginRequestHandler.java:226)
	at com.openexchange.ajax.login.AbstractLoginRequestHandler.loginOperation(AbstractLoginRequestHandler.java:184)
	at com.openexchange.ajax.login.Login.doLogin(Login.java:97)
	at com.openexchange.ajax.login.Login.handleRequest(Login.java:90)
	at com.openexchange.ajax.LoginServlet.doJSONAuth(LoginServlet.java:793)
	at com.openexchange.ajax.LoginServlet.doGet(LoginServlet.java:758)
	at com.openexchange.ajax.LoginServlet.doPost(LoginServlet.java:878)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:706)
	at com.openexchange.ajax.AJAXServlet.doService(AJAXServlet.java:566)
	at com.openexchange.ajax.LoginServlet.service(LoginServlet.java:738)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:791)
	at org.glassfish.grizzly.servlet.FilterChainImpl.doFilter(FilterChainImpl.java:147)
	at com.openexchange.http.grizzly.servletfilter.RequestReportingFilter.doFilter(RequestReportingFilter.java:138)
	at org.glassfish.grizzly.servlet.FilterChainImpl.doFilter(FilterChainImpl.java:137)
	at com.openexchange.http.grizzly.servletfilter.WrappingFilter.doFilter(WrappingFilter.java:222)
	at org.glassfish.grizzly.servlet.FilterChainImpl.doFilter(FilterChainImpl.java:137)
	at com.openexchange.http.grizzly.service.http.OSGiAuthFilter.doFilter(OSGiAuthFilter.java:139)
	at org.glassfish.grizzly.servlet.FilterChainImpl.doFilter(FilterChainImpl.java:137)
	at org.glassfish.grizzly.servlet.FilterChainImpl.invokeFilterChain(FilterChainImpl.java:106)
	at org.glassfish.grizzly.servlet.ServletHandler.doServletService(ServletHandler.java:226)
	at org.glassfish.grizzly.servlet.ServletHandler.service(ServletHandler.java:178)
	at com.openexchange.http.grizzly.service.http.OSGiMainHandler.service(OSGiMainHandler.java:301)
	at org.glassfish.grizzly.http.server.HttpHandler$1.run(HttpHandler.java:224)
	at com.openexchange.threadpool.internal.CustomThreadPoolExecutor$MDCProvidingRunnable.run(CustomThreadPoolExecutor.java:2575)
	at com.openexchange.threadpool.internal.CustomThreadPoolExecutor$Worker.runTask(CustomThreadPoolExecutor.java:841)
	at com.openexchange.threadpool.internal.CustomThreadPoolExecutor$Worker.run(CustomThreadPoolExecutor.java:868)
	at java.lang.Thread.run(Thread.java:748)
Caused by: javax.mail.MessagingException: STARTTLS failure
	at com.sun.mail.imap.IMAPStore.protocolConnect(IMAPStore.java:954)
	at javax.mail.Service.connect(Service.java:369)
	at com.openexchange.authentication.imap.impl.IMAPAuthentication.handleLoginInfo(IMAPAuthentication.java:357)
	... 34 common frames omitted

Looks like empty vars for SSL protocol and SSL ciphersuites are not working properly, if specify them everything works just fine.

# File /opt/open-xchange/etc/imap.properties

# Specifies the SSL protocols that will be enabled for SSL connections. The property value is a whitespace separated list of tokens.
# Default is empty
com.openexchange.imap.ssl.protocols=TLSv1 TLSv1.1 TLSv1.2

# Specifies the SSL cipher suites that will be enabled for SSL connections. The property value is a whitespace separated list of tokens.
#
# Check "http://<ox-grizzly-hostname>:<ox-grizzly-port>/stats/diagnostic?param=ciphersuites" to check available cipher suites.
#
# Default value is empty (fall-back to current JVM's default SSL cipher suite)
com.openexchange.imap.ssl.ciphersuites=SSL_DHE_DSS_WITH_3DES_EDE_CBC_SHA SSL_DHE_RSA_WITH_3DES_EDE_CBC_SHA SSL_RSA_WITH_3DES_EDE_CBC_SHA TLS_DHE_DSS_WITH_AES_128_CBC_SHA TLS_DHE_DSS_WITH_AES_128_CBC_SHA256 TLS_DHE_DSS_WITH_AES_256_CBC_SHA TLS_DHE_DSS_WITH_AES_256_CBC_SHA256 TLS_DHE_RSA_WITH_AES_128_CBC_SHA TLS_DHE_RSA_WITH_AES_128_CBC_SHA256 TLS_DHE_RSA_WITH_AES_256_CBC_SHA TLS_DHE_RSA_WITH_AES_256_CBC_SHA256 TLS_ECDHE_ECDSA_WITH_3DES_EDE_CBC_SHA TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256 TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384 TLS_ECDHE_RSA_WITH_3DES_EDE_CBC_SHA TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256 TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384 TLS_ECDH_ECDSA_WITH_3DES_EDE_CBC_SHA TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256 TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA384 TLS_ECDH_RSA_WITH_3DES_EDE_CBC_SHA TLS_ECDH_RSA_WITH_AES_128_CBC_SHA TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256 TLS_ECDH_RSA_WITH_AES_256_CBC_SHA TLS_ECDH_RSA_WITH_AES_256_CBC_SHA384 TLS_EMPTY_RENEGOTIATION_INFO_SCSV TLS_RSA_WITH_AES_128_CBC_SHA TLS_RSA_WITH_AES_128_CBC_SHA256 TLS_RSA_WITH_AES_256_CBC_SHA TLS_RSA_WITH_AES_256_CBC_SHA256

Hope that helps, thanks!

Paperkey: How to backup your GnuPG keys on paper

To create a backup of your GPG Key, you may use one of the following commands

img-alternative-text

paperkey --secret-key my-secret-key.gpg --output to-be-printed.txt

Or using this if you have exported (not armored) GPG Key in file:

paperkey --secret-key my-secret-key.gpg --output to-be-printed.txt

To restore it you will need a paperkey data in file and you public key. The following command will take public key from public-key.gpg file and paperkey data from secret-paperkey.gpg file and will import it to ~/.gnupg.

$ paperkey --pubring public-key.gpg --secrets secret-paperkey.gpg | gpg --import

If you have armored gpg public key, you will need to dearmor it first by doing this:

$ gpg --dearmor public-key.gpg

To install it on your mac, you may use brew:

$ brew install paperkey

Paperkey by David Shaw

Thanks!

 

Reset your YubiKey to factory defaults using gpg-connect-agent

I’m using MacOS for my work, so here is how to reset your YubiKey using gpg-connect-agent. This is not a mine solution, I found this a while ago on github, can’t remember who’s is author, so I saved to my notes, just wanted to share it with you.

# Install gnupg if it's not installed yet
VKAFEDZH-M-2R3C:~ vkafedzh$ brew install gnupg

Insert your YubiKey to USB and run the following command:

gpg-connect-agent <<EOF
/hex
scd serialno
scd apdu 00 20 00 81 08 40 40 40 40 40 40 40 40
scd apdu 00 20 00 81 08 40 40 40 40 40 40 40 40
scd apdu 00 20 00 81 08 40 40 40 40 40 40 40 40
scd apdu 00 20 00 81 08 40 40 40 40 40 40 40 40
scd apdu 00 20 00 83 08 40 40 40 40 40 40 40 40
scd apdu 00 20 00 83 08 40 40 40 40 40 40 40 40
scd apdu 00 20 00 83 08 40 40 40 40 40 40 40 40
scd apdu 00 20 00 83 08 40 40 40 40 40 40 40 40
scd apdu 00 e6 00 00
scd apdu 00 44 00 00
/echo Yubikey has been successfully reset.
/echo The factory default PINs are 123456 (user) and 12345678 (admin).
EOF

Here is what I have:

Continue reading “Reset your YubiKey to factory defaults using gpg-connect-agent”