Limiting SSH access to certain users

There are multiple ways to limit SSH access to a machine. The one I’ve found most straight forward is to use PAM access rules. First, edit /etc/pam.d/sshd and uncomment the line:

account required pam_access.so

Next, edit /etc/security/access.conf

The following rules allow root from a local connection and deny all but users in the SSH group.

+:root:LOCAL
-:ALL EXCEPT ssh:ALL

With this in place, managing SSH access is a matter of tweaking the ssh group.

Getting sizes of tables in a Postgres database

When disk space is getting tight, it’s nice to be able to figure out what tables are the culprits. Below is how to get a size-ordered list from PostgreSQL:

webapp=# SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS size FROM pg_class WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public') ORDER BY pg_total_relation_size(oid);

This will return something like this:


index_alert_history_archive_on_launch_id_and_entity_id | 1101 MB
index_alert_history_on_entity_id_and_code | 1328 MB
index_alert_history_archive_on_entity_id_and_code | 1374 MB
alert_history | 7959 MB
alert_history_archive | 10168 MB

References:

http://www.postgresql.org/docs/current/static/functions-admin.html
http://www.thegeekstuff.com/2009/05/15-advanced-postgresql-commands-with-examples/

Grabbing [and removing] remote files

While SSH’d into a remote machine, I find that I need to grab a file to use it locally. But, I’m behind a firewall … and so is the server, which means I can’t scp the file to or from my machine. I resort to temporarily dropping the file on an intermediate machine accessible by both.

After doing this for a while I ended up with a lot of junk piled on the intermediate machine because I never clean up files after copying them to my lappie.

Using a recently discovered rsync option, --remove-source-files, I created an alias which does the cleanup for me:

alias grabrm='rsync --remove-source-files'

I now after copying the file to the intermediate machine:

remote_machine$ scp foo.txt intermediate:

I run the following command, which copies the file and removes it from the intermediate machine:

local_machine$ grabrm intermediate:foo.txt .

Caveat Emptor: Taps for Easy Database Transfers

I found this post on a project called Taps. It’s a database proxy to slurp data out of one database and dump it into another, which can be from a different vendor (e.g. it can handle MySQL -> PostgreSQL migrations).

Whenever possible, I’d rather leave serialization to that which knows it best, in my case pg_dump.

The blog post states:

Migrating databases from one server to another is a pain: mysqldump on old server -> gzip -> scp big dump file -> gunzip -> mysql. It takes a long time, and is very manual and (and thus error-prone), and generally has the stink of “lame” hanging about it.

My solution: UNIX. The example below uses PostgreSQL, but the general structure holds true for MySQL. It comes in two flavors; the pull method:

ssh -C current_db_server 'pg_dump current_database' | psql new_database

or the push method:

pg_dump current_database | ssh -C new_db_server 'psql new_database'

The mechanics are strikingly similar to the, ‘stink of “lame”,’ but no babysitting required. I get compression (-C), encryption (SSH), and data integrity (pg_dump) from battle-hardened tools; all nicely wrapped into a single command.

It generally “just works”, but if your connection is interrupted, you’ll have to drop the new db and start again. Remember to use screen!

Taps doesn’t require SSH, but I suppose it is a given in any environment. Worst case scenario is establishing a tunnel using SSH’s -L or -R flags.

Taps can also migrate between database vendors, which is interesting. We went from MySQL to PostgreSQL and had to tweak an existing tool to do so.

Watch out for issues like, “Foreign Keys get lost in the schema transfer,” ouch. Eh, whatever, RI is for stuffy folks.