Letter to FCC chairman Tom Wheeler

Been a while since I wrote anything. I suppose it is too easy to get distracted by other shiny things but I need a place to put these thoughts in public. Sorry for it not being "database" related but this is something that needs to be said and at least in some ways it is a unique point of view. Here's the letter I just sent to FCC chairman Tom Wheeler in response to the latest _suggested_ changes to how the internet should be setup.

As context I did a quick search on "fcc net neutrality" and found this article on a petition against the proposed changes.

And this one encouraging the POTUS to push the FCC to regulate the ISP's as "Common Carriers". 

Doing so will allow the FCC to regulate them the same way they do for phone, electricity, etc. The Internet is a utility now not a luxury.

===Begin Letter===
I'm a work from home programmer who enables others to work from home. A key factor in how the people who service our customers do their work is the ability to make and take VOIP calls and access our customer's support applications.
I regularly have to "bypass" the throttling that my ISP does by using a personally purchased Virtual Private Network (VPN) service. When I work using the VPN my bandwidth is fine, without it, I often can't do my work. This is the same connection in both cases but the traffic just looks different because they can't look inside it when I use the VPN.
I get similar behavior with movie streaming (fine with VPN, barely usable without), monitoring my nanny cams, etc. You can't tell me that your new ruling won't affect my service. These quality of service (QoS) throttles started only days after the Supreme Court ruled against the previous policy.
THAT ISN"T A COINCIDENCE!
If it were then my using VPN service (something our contractors likely can't afford) wouldn't improve things.
Making them "provide a base level of service" is a fine statement but without enforcement and oversight it won't happen. As an amateur radio operator (KF4MOS) I know that the FCC can't enforce the laws keeping rogue broadcasters in check. Keeping a large corporation in check who wants to hide their unfair business practices is basically a fox hunt for WMD's with lots of red herring about.
The Supreme Court said that the previous policy wasn't wrong, just done under the wrong umbrella. Go back and do it right. Please. If you don't then you _will_ be affecting many peoples ability to work from home and indeed make a living.
===End Letter===

I encourage you to reach out to:
Tom.Wheeler@fcc.gov
Chair Tom Wheeler Federal Communications Commission
445 12th Street, SW
Washington, DC 20554

We all have our stories so reach out by both email and a letter. Yeah I know it sucks to go to the post office but he needs to see our response in both ways.

doc..

OpenSSH using Cygwin with CHROOT support

Given how many sites I had to hit and how much experimentation I did to figure this one out (credits below) it seems like a good idea to document it somewhere. Making it available to the world is only right. :-)

The goal of this is to create an OpenSSH installation on Windows (using Cygwin) that forces each user into their own chroot "jail" where all they see is their own stuff. For an added bonus you can put this limit on select logins based on their group.

First cut is short on some details so I can get it out of my head and start the documentation.

1. Install Cygwin being sure to select the OpenSSH package
   
2. Create passwd and group with mkpasswd and mkgroup
   
3. Manually add a root user and group as follows:
   
   
   
   1. Root User
      
      
      
      1. Edit /etc/passwd and copy the "Administrator" user line.
         
      2. change "Administrator" to "root"
         
      3. change the uid and gid (usually 500 and 513 respectively) to 0 and 0
         
      
      
   2. Root Group
      
      
      
      1. Edit /etc/group and copy the "Administrators" group line
         
      2. change "Administrators" to "root"
         
      3. change the group id (usually 544) to 0, be sure to leave the second field alone
         
      4. move the "root" group above the "Administrators" group if it is not already
         
      
      
   
   
4. Create a chroot directory
   mkdir /jail
   
5. Change ownership of chroot to root:root
   chown root:root /jail
   
6. for each user to be chrooted
   
   
   
   1. create /jail/%user/home/%user
      
   2. make sure ownership of each directory in /jail/%user/home is root:root
      
   3. make sure ownership of /jail/%user/home/%user directory is %user:Users
      
   
   
7. Edit /etc/sshd_config and
   
   
   
   1. go to the bottom of the file
      
   2. Find Subsystem sftp and change it from "/usr/...." to "internal-sftp" (no path)
      

      
      Subsystem        sftp    internal-sftp
      

      
      
   3. Add (or edit existing) block at the bottom to look like
      

      
      # Example of overriding settings on a per-user basis
      Match Group your_sftp_only_group
       ChrootDirectory /jail/%u
       ForceCommand internal-sftp
       X11Forwarding no
       AllowTcpForwarding no
      

      
      
   
   

References:
To end up in writable directory


More windows setup help
Setting up pubkey auth
I include this reference but the ssh-host-config script that installs with Cygwin's OpenSSH should handle all this now.
http://www.blogger.com/img/blank.gif

Split a MySQL text column into single words

Well I needed to split a column with text in it into its constituent words. Didn't do much clean up on the source. Removing multiple spaces and special characters in a sane way would be nice. I just need a qad solution though so this will have to suffice. Love to see anyone liberal use of REPLACE() to make this better.

Unfortunately not handling multiple space white space means lots of "blank" words. DISTINCT is our friend though. At the bottom I note that use of the INSERT...ON DUPLICATE UPDATE would be a cool way of counting occurrences as we build the table. Guess that is an exercise for another day.

--Step 1
--Create a table with sequence numbers.  This Step can be skipped if you keep one around for this purpose
drop table if exists docsql_seq
go
--Could not find a way to create a table from a select with an auto_increment that
--did not have another column.
--The cartesian join on the information_schema.columns table should give us plenty of room to grow.
--change the LIMIT 100 if you have long blocks of text
CREATE TABLE docsql_seq
  (
      val INT NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (val)
  )
SELECT
  '1' AS Dummy
FROM
  INFORMATION_SCHEMA.COLUMNS C1,
  INFORMATION_SCHEMA.COLUMNS C2 LIMIT 100
go
--drop table is here for testing
drop table if exists docsql_word_xref
go
--With SUBSTRING_INDEX this was much easier than with the other DB
--the spaces CONCAT to the begining and end of the column handle columns with a single word
--   and ensure you do not miss the final word in the column
--Basically we will create the table on the fly with the select
create table docsql_word_xref
SELECT
  distinct T1.Word
FROM
  (SELECT
         SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(' ',mt.Words,' '),' ',t.val+1),' ',-1) AS Word
    FROM docsql_rt_raw mt,
         docsql_seq t
   WHERE t.val < (LENGTH(mt.Words) - LENGTH(REPLACE(mt.Words, ' ','')))     ) T1 WHERE     T1.Word <> ''
go
--List out your words
select * from docsql_word_xref order by Word
go

--It would be interesting to use MySQL's INSERT OR UPDATE functionality to count occurances
--Cleaning up the text with word separators would be a nice addition.
--DocGyver