empty() function for postgresql in sql

In PostgreSQL the ASCII NULL or empty string \0 is seen as NOT NULL. This is because postgres developers consider any characters data. I know there are better discussions on it but I can't find them right now. Unfortunately the programming language you are using probably doesn't see it the same way. There's a good chance that initialized variables are set to \0 and so when you try to insert from your language to a NOT NULL field with variables that are seen as undefined in your language, postgres accepts it, and now you have fields that you probably consider to have no data but are NOT NULL.

My function possibly would be better called empty_or_whitespace but in my mind any text field that contains only whitespace is empty, and I'd rather have 1 function and 1 regex deal with both than have 2 functions have to be called on every insert/update.

Here is the gist

It returns true if an empty string is found. It really requires a knowledge of SQL, PostgreSQL's CREATE FUNCTION, and Regular Expressions, to understand. Thanks to the wonderfully helpful PostgreSQL community with perfecting it.

In order to use it to keep ASCII NULLS and fields that someone has just entered whitespace into you need to add it to your table as a constraint.

Here's an example create table gist using it.

remember it returns true on null's and whitespace so you have to say NOT emtpy( field ).

PostgreSQL initial setup (authentication) Part 2

I ran into so problems and lack of information with my last post on this topic. Firstly my syntax for local all all to local all all ident devel
doesn't seem to work in my current setup. It's possible that it has something to do with the configuration of the Debian/Ubuntu server I was basing that against, and now my targets are Arch Linux and Slackware.

So our goal here will be to provide an alternate user that can log in as postgres via ident. why would you want to do this? maybe you're tired of su - to the postgres unix user after logging in as your administrative user (root?).

First we need to open our pg_ident.conf and add the following line.

admin root postgres

So as I said in the last post on this: "admin" is an arbitrary identifier, root is the system user, and postgres is the database user. Now let's go ahead and make sure the postgres system user can still login.

admin postgres postgres

ok that's all we need to do in the pg_ident.conf. Go ahead and open pg_hba.conf and add this line above the default 3 lines in the file.

local all postgres ident map=admin

as a breakdown: local is for socket connections, all means all databases, ident means it's checking for local users, and map=admin says look at the users in the admin identifier in pg_ident.conf.

Now you should be able to run psql dname postgres as root. You will not be able to use the db postgres user as any system user not in the admin map.

Bypassing disabled accounts with KDM

So the most common way of disabling an account in a unix system is changing the users shell in /etc/passwd to /bin/false or /sbin/nologin. However, I've discovered on Arch Linux that if I do this only shell login's are disabled, I was still able to log the user in with gui via kdm. I also tried using usermod --expiredate 1. this was not effective either however. the only way I found to lock the account from kdm login was to do a passwd -l accountname, which only locks password authentication. This means key and token authentication should still work. My real concern is that if the user was set to login without a password that it would still be bypass-able. My personal opinion is that I shouldn't have to do more than 1 thing to disable a user account in a 100% effective manner. Currently Arch Linux (and maybe more) fails at this. I'll post a fix later as I investigate further.

Bugs I filed are at kde and Arch Linux

EDIT: expire date worked... just not immediately, very odd.

EDIT: Here's a proper /etc/pam.d/kde

#%PAM-1.0
auth        required    pam_nologin.so
auth        required    pam_unix.so nullok
auth        required    pam_shells.so
auth required pam_tally.so onerr=succeedfile=/var/log/faillog
account     required    pam_access.so
account     required    pam_time.so
account     required    pam_unix.so
password    required    pam_unix.so
session     required    pam_unix.so
session     required    pam_env.so
session     required    pam_limits.so

Difference Between a Database and a Database Instance

I was prowling StackOverflow today and the question was asked when you would use 2 databases. I'm amazed at the number of answers of replication and sharding (some under the guise of some other description). The only answer I can think of to be correct is Different Applications/Services (shared hosting probably falls under those 2 in a weird way). I'm going to explain why a replicated database and/or a sharded database are not multiple databases.

Firstly, we must understand what a database is and is not. A database is not a computer program that runs and stores data, this is DBMS (Database Management System). A database simply is a collection of data. A database is an organized collection of data. Many Definitions of a Database include computers, however not all do, in fact Joe Celko argues that first database were precursors to written language in the Middle East (see Data and Databases Chapter 1). It may be true that database are organized for quick retrieval of information in a certain way, however they are not necessarily designed for quick retrieval of all information, in any way.

So what are some example's of a Non Computer Database? A phone book is a good example of a database. It's really fast to look someone up if you know there last name, but not if all you have is their phone number and you want their address. You'll Also note that it's got various ways of searching if you're looking for businesses. It is not a good example of a normalized database, but I'm not sure that's possible with a paper database that allows you to find the same information in multiple ways. A phone book is simply a collection of Names, Addresses, and Phone Numbers, and Categories (tags).

So wouldn't two phone books be two databases? well if they are published by different vendors, or at different times, then yes. However, if you have two copies of the same phone book, then you have two instances of that database, that have been 'replicated' from a 'master' copy. You can't update one of these 'slave' copy's and expect them to replicate, you must update the 'master' and publish the updates. Unlike computer Phone books are extremely slow to push updates. Let's say you want to call a friend, Steven, over for pizza, and your roommate, John, wants to try a new pizza place. John can look at one copy of the phone book to look up the addresses and phone numbers of to the new pizza places you haven't tried, while you look up Steven's number in your copy of the book. This is exactly what happens with replication, different requesters doing lookups from different databases.

Now let's talk sharding, phone books aren't sharded right? wrong! they are phone books are sharded by location. The entire phone book database for even 1 state would be too big for them to distribute, so they split it up into smaller regions. Only the phone book company has the entire list. So that makes it kind of a bad example. Let's talk about encyclopedia's instead. They are a much better example since you don't generally buy one encyclopedia, you buy the whole set (which is also replicated and you can think of it's replication in the same way as phone books). Each encyclopedia volume tells on the side which letters it contains, so when you open it you know you're in the right vicinity. This is how a shard works, your database got too big an unmanageable as a whole so you split it up into 'shards' or 'partitions' to make it faster to search and easier to handle and store.

Now to clarify my answer to that original question. You wouldn't put a phone number (phone entry not included) in an encyclopedia even though I'm sure Bill Clinton has one, you might list it in the phone book though. So now you see when you would use a different database. Very clearly you would use one when the type of data is different enough, when it's for different organizations, or when it's for kind of shared service.

I hope these analogies to computer databases have helped explain the concepts.

Port forwarding in OpenWRT

So you need the outside world to have access to some box's on your internal network. first you should use Static DHCP to tell static the IP's of the computer you're routing to. Then you of course need to know the inbound from the outside world and the port on the computer you are forwarding too. For this example we'll forward WAN (Wide Area Network (or (probably) the Internet)) port 8080 to a local dev box running Apache on httpd (we assume you know how to set apache up and make sure it's working on the LAN(Local Area Network)).

edit /etc/config/firewall. note: /etc/firewall.user is for manual iptables commands and is basically a shell script. I only recommend this if /etc/config/firewall doesn't do what you want (like multiport).

config redirect
        option src              wan
        option src_dport        8080
        option dest             lan
        option dest_ip          192.168.1.3
        option dest_port        80
        option proto            tcp

So options src and dest merely define the too and from interfaces (to be honest I'm not sure they actually do anything. as I'm not seeing any changes to what's in iptables). src_dport is what external port you want to listen on. dest_port is what you want it to go to. dest_ip is what ip you want it to go to and proto is the protocol it listens on (yes you have to specify separately for udp if what you're forwarding uses both tcp and udp).

After you've edited and saved the file to fit your case run /etc/init.d/firewall restart and it should work

IMPORTANT: there's a bug in 7.x and pre 8.09.2 with 2.4-bcrm kernels and netfilter code... it 'causes port drift. if the bug is affecting you. (it did me) reboot the system. you will be able to see it with a -j LOG iptables rule. the output was thus for me
IN=eth0.1 OUT=br-lan SRC=66.98.131.131 DST=192.168.1.3 LEN=44 TOS=0x00 PREC=0x20 TTL=47 ID=63450 DF PROTO=TCP SPT=54402 DPT=82 WINDOW=5840 RES=0x00 SYN URGP=0 as you can see the destination port(DPT) is 82 instead of 80 like it's supposed to be. 8.09.2 should be out in a few weeks it's currently at rc2.

Quick sha1sum with Crypto++

I've been working on Korama. I was originally planning on hashing each music file and using the sha1 for the track primary key. However, after benchmarking an actual implementation. I've decided that it's too slow, 20 minutes for 3k+ tracks, also my program was about 5 minutes slower than find ... -exec sha1sum not sure on the reason why. However, since I had problems figuring out how to do it I figure I'll post a basic sha1sum program that I made with the help of people on the Crypto++ Mailing List.

Include's are mangled due to blogger filtering

#include < cryptopp/sha.h >
#include < cryptopp/hex.h >
#include < cryptopp/files.h >
#include < string >
#include < iostream >

using namespace std;

int main(int argc, char *argv[])
{
    char *file = argv[1];
    string result;
    CryptoPP::SHA1 hash;

    CryptoPP::FileSource( ( file ),true,
        new CryptoPP::HashFilter(
            hash, new CryptoPP::HexEncoder(
                new CryptoPP::StringSink(result), false)
        )
    );
    cout << result << endl;
    return 0;
}

it can be built with.

g++ sha1.cpp -lcryptopp -o sha1sum

and tested with

./sha1sum filename
It's not meant to be a exactly compatible implementation of sha1sum. it doesn't output the filename, it isn't capable of handling more than one file argument, and if no argument is provided it crashes. I know I haven't actually explained how the crypo++ code works, but I hope just posting this here will help someone in the future.

FSDaily security fail (see image)

Hint, Hint! FSDaily don't access your db with the root account. and definitely make sure that, that error page doesn't show that to users.

PostgreSQL initial setup (authentication)

If you followed my last initial setup post on creating users and are running a default install of postgres you currently should be able to access the database on your cli. However, many distributions do not install postgres in a default manner. A great many distributions make the default authentication for sockets ident sameuser. This is technically much more secure than trust which is the default. However, you may find yourself locked out of your database on the local cli if you make the database name different. I personally was confused about this after first encountering this setting.

So, we don't want trust but we want to use a db username other than our shell login, most likely because we have more than one database. There are several options but I personally think ident is a good one. However, since we don't want to use the same shell login name we have to modify pg_ident.conf and pg_hba.conf, locations vary by distribution.

In pg_ident.conf you have to create a line with the following formatting.

# MAPNAME IDENT-USERNAME PG-USERNAME

I think it's mostly straightforward. In case it isn't, MAPNAME is an arbitrary identifier, sameuser is actually the mapname in ident sameuser A quick example from mine would be

devel xenoterracide webdevwhere my unix username is xenoterracide but I created the database user webdev. If you wanted you could add another devel mapname with another user or the same unix account different db account, or even a different unix account same db account, etc.

After you add all the various mappings you need to add or change the ident in pg_hba.conf. You can only have one method per type/database/user/address combination. so in pg_hba.conf you want to change

local all all to local all all ident devel

If you want postgres to ask for a password use md5 instead of ident further information can be found at http://www.postgresql.org/docs/current/interactive/auth-methods.html.

if you have any problems you might want to see part 2.

Adding a new group to an open shell without logging in and out

Most Unix users believe it's impossible to add a group that was recently added to your user account without logging in and logging out. If you're running X and need to give your gui new permissions (like dolphin/konqueoror) this may be true, for the most part. I don't know how to change it for those processes. However, if you need to change it for a shell prompt it's easy. You merely execute the command newgrp in an open shell prompt and that prompt will now be loaded with the new group. I believe it also changes the default group that shell is running as. So if you run newgrp http; touch test.txt your file will be created with the group http instead of whatever your default is. You can change your group back to the default by using the sg command.

More Security = Better. Wrong!

So I just had a discussion on #ubuntu-server on freenode about why my not having a password to connect to postgresql via a socket (read local cli) is insecure. So I asked them, how exactly is it that someone is going to get this access? The answer "there are bad people on the Internet". I'm sure many people right now are agreeing with them and thinking I'm crazy. Let's discuss my setup though shall we.

Postgres: I'm using Ident, and not just sameuser ident. no I had to set a custom rule in the pg_ident.conf file for this user to access because the systemuser != the db user. so just typing psql at the command line should you get access still won't get you into the db. you have to know which user/database to connect to. But that's not that hard right? in fact it's trivial.In addition you have to be a certain systemuser, only one works.

User Access: There is only one user account on the system that can be logged into (it's not root or a generic name). Only 2 people have the credentials to get shell access. One is my host, who happens to have physical and kvm access. The other is me who has ssh access.

SSH: I have ssh on a high port with no password authentication, or root, allowed.

So in order to passwordlessly access the database you would have to ssh into the system from a remote location or find an exploitable bug in apache (the only other service listening, or the kernel) that allows you to switch to my non apache user (meaning root access).

In addition, this system user had rw access to the entire website which includes a file containing the password to the database in plain text. So let's say I created a .pgpass file or variables? what exactly is it that would keep anyone who has access to this account from gaining access to the database? in fact wouldn't it just make it easier, since pgpass has a known location and contains all connection info including the username/dbname string?

But why do I need passwordless access anyways? I wrote a script that dumps the db every hour to a git repo and then pushes that to a remote.

Could I secure it further? yeah I could. I could make the script run as a user who can't log in at all and then put a pgpass in that users account. I'd have to properly ACL the webroot to give the correct write and read access. But is all this really necessary? maybe and I might do it, but at this point it's not nearly as important as it was for me to get backups up and running, because regardless of all the security I implement, if I have no backup and someone finds that loophole in one of the pieces of software and uses it, I might just be screwed. At least now I can restore the site if it gets attacked.

P.S. I was having a problem with my backup not being run by cron, never did figure out why.