{"id":1805,"date":"2020-04-17T17:20:44","date_gmt":"2020-04-18T00:20:44","guid":{"rendered":"http:\/\/jim-zimmerman.com\/?p=1805"},"modified":"2020-05-19T16:44:53","modified_gmt":"2020-05-19T23:44:53","slug":"sqlite3-and-openvpn","status":"publish","type":"post","link":"https:\/\/jim-zimmerman.com\/?p=1805","title":{"rendered":"SQLite3 and OpenVPN"},"content":{"rendered":"

I needed to cleanup an issue with an OpenVPN user. I cleaned was using a multi-factor solution with OpenVPN. The multi-factor solution was case sensitive, while the VPN was not. To clean up the mess, I needed to remove the certificates from the certs SQLite database. <\/p>\n

Database location:<\/p>\n

# cd \/usr\/local\/openvpn_as\/etc\/db<\/p><\/blockquote>\n

Connect to the database:<\/p>\n

# sqlite3 certs.db<\/p><\/blockquote>\n

List the tables in the database:<\/p>\n

sqlite> .tables<\/p><\/blockquote>\n

List column names in the table:
\nsqlite> pragma table_info(certificates);<\/p>\n

List all the common_name records in the database:
\nsqlite> select common_name from certificates ;<\/p>\n

Delete records with the common name somename<\/em>:
\nsqlite> delete from certificates where common_name=’somename<\/em>‘;<\/p>\n

This worked well to clean up the database, and get the user working with a correct ovpn file. <\/p>\n

Note: You could also edit the ovpn with data from the database as well. <\/p>\n

Here is how you display a table (log in this instance) layout:<\/p>\n

sqlite> .schema log<\/p><\/blockquote>\n

I used the following to query the login activity. You have to convert the timestamp in the start_time column. Also, the duration is stored in seconds, so I converted it to minutes.<\/p>\n

sqlite> select username,strftime(‘%m-%d-%Y’, datetime(start_time, ‘unixepoch’)),duration\/60 from log;<\/p><\/blockquote>\n

To run it from a script, save your query in a file (zreport) and run the following:<\/p>\n

sqlite3 log.db < zreport<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

I needed to cleanup an issue with an OpenVPN user. I cleaned was using a multi-factor solution with OpenVPN. The multi-factor solution was case sensitive, while the VPN was not. To clean up the mess, I needed to remove the certificates from the certs SQLite database. Database location: # cd \/usr\/local\/openvpn_as\/etc\/db Connect to the database: [&hellip<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[48,495,393],"class_list":["post-1805","post","type-post","status-publish","format-standard","hentry","category-documentation","tag-linux","tag-openvpn","tag-sqlite"],"share_on_mastodon":{"url":"","error":""},"_links":{"self":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts\/1805","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1805"}],"version-history":[{"count":6,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts\/1805\/revisions"}],"predecessor-version":[{"id":1817,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts\/1805\/revisions\/1817"}],"wp:attachment":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1805"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1805"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1805"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}