Brad and Kim

Granting select privileges in PostgreSQL only works on tables so there are a few steps involved in adding a user with read only privileges to the entire database.

First add the user:

createuser -U postgres <newuser>

Now give the user connect privileges on the database

psql -U postgres

GRANT CONNECT ON <db> TO <newuser>

Next, generate a list of grant commands for all of the tables with this set of queries:

psql -U postgres

\t

\a

\o /tmp/mytempfile

select ‘GRANT SELECT ON ‘ || schemaname||’.'||tablename|| ‘ TO <newuser>;’ from pg_tables where tableowner = ‘<dbuser>’;

\o

\i

This will set some psql environment variables and generate a file (/tmp/mytempfile) that contains a list of the commands you need to run.  It then turns off the output redirection (\o) and executes the file (\i).

Finally, you need to give select privileges to the sequences in the database.  Use the following script called ro_user_seq.sh:

#!/bin/bash
for i in `cat table_list`
do
psql -U postgres isat -c “GRANT SELECT ON SEQUENCE $i TO <newuser>”
done

Where table list is a list of one sequence name per line.  I generate this file by connecting to the target databse and running \ds, copying and pasting that output to a file called table_list, and then running these commands:

cat table_list |awk -F”|” ‘{print $2}’ > table_list2

mv table_list2 table_list

Brad Crotchett

I have been busy reinstalling all of my servers with CentOS 5 and I made a decision to learn and live with SELinux instead of disabling it as I always have. On a freshly installed server I mounted the storage array containing a postgresql db on /var/lib/pgsql and started postgres but received errors. Running this command changed the context of all the files in that directory and allowed me to start postgres:

chcon -R system_u:object_r:postgresql_db_t /var/lib/pgsql

Later I had a developer running a perl script that connects to the database start getting permission denied errors. Here is a handy little trick to make new selinux modules from the messages logged in /var/log/audit/audit.log

cd /etc/selinux

mkdir localmodules

cd localmodules

tail -n 10 /var/log/audit/audit.log | audit2allow -M <module_name>

This will create the module <modulename> with a humand readable <module_name>.te and selinux formatted <module_name> .pp

Once the module is created you can insert it with:

semodule -i <module_name>.pp

That should do it.

Brad Crotchett

A cool cpan tool can be used to make a backup or snapshot of the current list of perl modules installed, either for reinstalling them, or for installing the same modules on another server.   On the server you want to backup, run:

perl -MCPAN -e autobundle

This creates a file called /root/.cpan/Bundle/Snapshot_<year>_<month>_<day>.pm.  You can move that file to another server, cd to the directory of the file, and run:

perl -MCPAN -e ‘install Bundle::Snapshot_<year>_<month>_<day>

cpan will then install all of the same modules.

Brad Crotchett

I discovered that my cable company provided the local network channels in HD for free within my expanded basic service (I think it is required by law now), so I traded in my old set-top-box for an HD version. They were out of the regular HD STB’s so they provided me with a Motorola DCT3412 which is actually an HD DVR. I needed only the tuner function though as Mythtv is my DVR. My old STB was controlled through an IR blaster using lirc, but this did not work on the new box. I finally switched to the firewire method and it was very straight forward. Here is what I did.

Brad Crotchett

I am running RHCS on several servers and am building the rpms from source. Instead of building them and then going to each server and running 8 or 10 commands to install them, I decided to throw them in a yum repo and then create a “suite” package that could be installed and would subsequently install all the other rpms. It turns out this is very simple to do. I looked at the mythtv-suite spec file and used it as a template. Basically you are just setting up a bunch of requires in the spec file and nothing else is built. Here is a copy of my spec file. After that I throw all the rpms into a web accessible directory, run ‘createrepo .’ in that directory and set up the clients’ with a yum.repos.d/myrepo.repo file.

Brad Crotchett

I just returned from a 3 day course on Postgresql Performance Tuning.  It was a very good course with lots of information and was well presented by Chander Ganesan of the Open Technology Group.  Topics covered included from query optimization, query optimizer tuning, table inheritance, pg_pool, pgmemcached, postgresql.conf parameters, and various replication techniques.  I would reccomend the course.

Brad Crotchett

You can alter pam’s behavior in RHEL4 to enforce strict by passwords by editing /etc/pam.d/system-auth pam_cracklib line to look like the following:

password requisite /lib/security/$ISA/pam_cracklib.so retry=3 minlen=8 lcredit=-1 ucrdeit=-1 dcredit=-1

This will require that passwords be at least 8 characters long, contain at least one upper case letter, at least one lower case letter, and at least one number.Brad Crotchett

So it’s been a while since I have posted anything. I have been pretty busy with work, Keaton, and mountain biking and running. I am also on the 3rd week of a 30 day all raw food diet. I wanted to see what it is like, so I am eating nothing that is cooked and no meat or dairy. So far it hasn’t been too bad, but I really do miss bread.

Brad Crotchett

Our 4 1/2 month old son Keaton is doing great.  He is now eating cereal every day, has been swimming at the pool (he loves water),  and sleeps through the night.  We recently started moving him from our bed to a co-sleeper next to our bed and while it was a little rough at first, he is taking to it well now.  It has been easier now that our pediatrician cleared him to sleep on his stomach.  He is much more restful that way.  Last night he slept until 5:30 this morning in his co-sleeper and only then started getting restless.  He laughs and giggles a lot, is more independent, and spends much less time eating.  He is also on the verge of crawling.  Last night he did achieve what I describe as ‘a crawl’ but then I am a proud father with a bit of bias.

Brad Crotchett

I have been testing the RHEL Cluster Suite a bit lately and am liking what I see so far. There is a bit of a learning curve and it can be a little temperamental, but once you understand the workings of it the benefits are pretty nice. I have set up a 3 node cluster and a postgresql service. Within seconds of shutting down the network on the node running postgres, a second node detects the failure, fecnes the primary node (reboots it), and brings up postgresql on itself. I am now testing doing this with the postgresql data stored on a SAN partition thus necessitating the mounting on this partition before starting postgres. So far it seems to work but more testing is needed.

Brad Crotchett