PostgreSQL Database psql Command Line Query

Sometime psql command line query can be useful when it comes to Bash script processing. There are data stored in PostgreSQL database and I would like to write a bash script to massage the data into report. Example below show query to PostgreSQL database by using the psql command.


/usr/local/bin/psql -c "SELECT email_address FROM members WHERE active is TRUE;" -U someuser -t -A -o member-email-address.txt accounting

psql is connecting to accounting database, query from members table and out the result into a txt file name member-email-address.txt.

-t – Return result only, do not result with table name.
-A – By default, returned result will have white space, this is to remove the white space.
-o – Output the result into a file.

Modify or Adjust File Date Time on Unix/Linux

You have folders or files in your server last modified date/time was 3 months ago, sometime for some reason we would like to change files or folders last modified date/time to older or recent date. You can modify the date/time of a folder or file with Unix/Linux touch command.

For example I have a file name wireless.pcap, last update or created was in year 2009. I can change the last modify time by the touch command touch -t yyyymmddhhmm filename

shell> touch -t 201007101540 wireless.pcap

This will change the file last updated date and time to 10th July 2010 15:40pm.

How to Create User with useradd in FreeBSD

You can invoke “adduser” command in FreeBSD in order to create new user. Adduser will prompt you for user info like name, uid, gid, shell environment and etc. But sometime we would like to create user access through Bash script. “useradd” command is the good way to do it.

In Linux, using “useradd” is pretty common for creating new user access. You can also use “useradd” in FreeBSD, but not directly invoke “useradd” command. Below is the sample of creating new user in FreeBSD with “useradd”.


shell> pw useradd cheryl -c 'Cheryl Windows Admin' -d /home/cheryl -s /usr/local/bin/bash

New user account is created, but don’t forgot to set password for the account


shell> passwd cheryl

You can run other command like “userdel”, “usermod”, “usershow”, “groupadd”, “groupdel”, “groupmod”, “lock”, “unlock” on FreeBSD by using the “pw” command.

Install and Configure mod_suphp or suphp on Plesk Server

Plesk control panel comes with Media Temple DV package doesn’t support suphp/mod_suphp. I like suphp, it gives you the user/group permission as the control panel created for you, so that you will have the permission to read/write on your own files/folders.

I mentioned before that I got Dedicated Virtual (DV) VPS package from Media Temple previously. It has been a while since I subscribed with Media Temple. So far I am happy with their services and I have moved my blog over to Media Temple 2 months ago. Since then I never bother to patch my WordPress until Version 3 released, mainly was because I am too lazy to download, backup, untar, copy over for upgrade. I prefer the One Click WordPress Upgrade option, yes.. That’s how Lazy I am!

I just had mod_suphp installed on my Plesk, and first thing is I did was upgraded my WordPress to Version 3, well also all my plugins. Here is the how-to of suphp installation and configuration on Media Temple Dedicated Virtual package.

First, install mod_suphp from yum


shell> yum install mod_suphp

After installation, you should see suphp configuration files located at
/etc/httpd/conf.d/mod_suphp.conf
/etc/suphp.conf

On mod_suphp.conf, you can refer to the configuration I have below;


LoadModule suphp_module modules/mod_suphp.so
suPHP_AddHandler php5-script
suPHP_Engine on

You will notice that the Handler is php5-script, it’s the php’s handler configured on Plesk, so we will not disturb that. You need to make the handler recognize on suphp, and follow the configuration for /etc/suphp.conf


[global]
;Path to logfile
logfile=/var/log/suphp.log

;Loglevel
loglevel=info

;User Apache is running as
webserver_user=apache

;Path all scripts have to be in
docroot=/var/www/

; Security options
allow_file_group_writeable=false
allow_file_others_writeable=false
allow_directory_group_writeable=false
allow_directory_others_writeable=false

;Check wheter script is within DOCUMENT_ROOT
check_vhost_docroot=true

;Send minor error messages to browser
errors_to_browser=true

;PATH environment variable
env_path=/bin:/usr/bin

;Umask to set, specify in octal notation
umask=0022

; Minimum UID
min_uid=500

; Minimum GID
min_gid=500

; Use correct permissions for mod_userdir sites
; handle_userdir=false

[handlers]
;Handler for php-scripts
x-httpd-php="php:/usr/bin/php-cgi"
php5-script="php:/usr/bin/php-cgi"

;Handler for CGI-scripts
x-suphp-cgi=execute:!self

After restart apache services, all your website hosted on the server will have suphp support, you might want to check all the permission of your files and folders before restart apache. Make sure the folder is mod is 755 and file mod is 644.

You can do the quick mod change with find command


shell> find /var/www/vhost/domain-name/httpdocs/* -type f -exec chmod 644 {} \;
shell> find /var/www/vhost/domain-name/httpdocs/* -type d -exec chmod 755 {} \;

That’s it! mod_suphp on Plesk. Nice and Clean.

Razor2 Error: No such file or directory report requires authentication

While doing Spam Report on SpamAssassin, I came across this error message,


reporter: razor2 report failed: No such file or directory report requires authentication at /usr/local/lib/perl5/site_perl/5.x.x/Mail/SpamAssassin/Plugin/Razor2.pm line 180. at /usr/local/lib/perl5/site_perl/5.x.x/Mail/SpamAssassin/Plugin/Razor2.pm line 330.

I checked the file existed, so It’s something to do with Razor authentication. This is what you need to do fix the error message from SpamAssassin.


shell> razor-admin -create
shell> razor-admin -register