How not to ask for help

This is the story of my friends problem today.

I got one for ya.

Field like field --friend

so of course I'm like: ? is that a question? he had given me no prelude, no premise. response

I tried field like '%' + field2 + '%'

No good though
--friend

So I explained that % is a wildcard. He still hadn't explained what his problem is or what he was trying to do.

it giving you a syntax error? --me

So here I am trying to guess at what his problem is. I've never used sql server but a quick glance at postgres docs suggested the syntax was wrong. I know don't query other docs than the software being used... but I wanted to run a test locally to see if I could figure out what he was doing.

Doesn't work --friend

Oh! that's very helpful... way to give more information. I think I now know why he never asks questions in IRC or forums. He never learned how to ask a question and they won't put up with him.

so it's not a syntax error it just doesn't do what you want? --me

Right. The like command isn't finding it --friend

So finally I'm able to determine... his problem is he's got a ... WHERE ... LIKE ... that isn't returning what he expects. This is still completely worthless because I still haven't been able to figure out what he's trying to do... or more correctly I have but not why it isn't working because his code is right. which means he's fallen prey to the assumption that he knows what I need to know to solve his problem. He hadn't given me enough info. After I tried to explain to him how to do what I thought he was trying to do and giving him a sample query and some data... he gives me this

Field1 = "4369" field2 = "4311", "4369" --friend

Now anyone who's familiar with sets will probably realize the notation here is bad and so you can't tell if these are sample columns or records. a more proper way to write this would have been field1 = { '"4369"'} field2 = {'"4311", "4369"'} I probably still would have been a little confused just because I wouldn't have been sure he was writing it right. See the problem here is that field 2 is an array and therefore not stored properly in a relational manner, it's why he has to do a much slower LIKE in the first place. After bantering to figure out that this was in fact text data these were single records I eventually figured out that what he was trying to do was find field2 if it contaned field1. However, his code was asking for field1 if it contained field2. basically he had his parameters reversed.

It took me 20 minutes of getting that data to realize they were reversed, which is shameful for me... but I was trying to figure out things like VARCHAR or INTEGER... he of course said it's not an array, and I suppose it's not an ARRAY type in sql.. but I guarantee that's why it looks like that... it's really a foreign key stored as an array in a varchar. After I told him he lied and said it didn't work only to come back 10 minutes later and say... you were right.

so the final should have been

WHERE field2 LIKE '%' + field1 + '%' or in postgres WHERE field2 LIKE '%' || field1 || '%' because we use the standard || for string concatenation instead of the +

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.

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.

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.

postgresql initial setup (users and databases)

This guide assumes that you've managed to install and start the postgres server. I'm ignoring these because they are well documented in several places, probably including your distribution, however, what to do next isn't.

first you need to connect to the postgres database with psql as postgres, on gentoo* distributions you must be in the postgres group to do this. In the event this doesn't work you may have to su to the postgres unix user account somehow and run psql from there.

psql postgres postgres

you now have connected to what is essentially the 'root' user for postgres. as with all 'super user' accounts be careful and know what you are doing. also, as with all 'super user' accounts you don't normally want to run as them, so we need to create another user. I recommend creating the user with the same name as whatever shell account you may be connecting to it from (if applicable) because then you don't have to specify the username when typing psql.

CREATE USER xenoterracide WITH PASSWORD 'YeahLikeIdTellYou';

next you need to give the user a database. like the user account if you make it the same name as the shell account you won't have to pass it to psql.

CREATE DATABASE xenoterracide WITH OWNER xenoterracide;

now you're done with the setting up your users account so quit psql by tying \q at the prompt. now you can simply log in from your users shell by typing psql and SQL-ing away.

PostgreSQL \edit and Syntax Highlighting

If you've ever used \edit with PostgreSQL you've probably noticed that it's not using syntax highlighting that your favorite editor most likely supports. I've wondered why this is for a while, Postgres uses vim on my system which supports robust highlighting among other things.

I had a few things suggested to me, such as setting putting settings in my .vimrc, most of which would have forced filetype=sql on files that weren't sql, and would have been active outside of psql.

When you have a question what's the first thing you should do? RTFM. The answer was in the man page all along.

Tip: psql searches the environment variables PSQL_EDITOR,
EDITOR, and VISUAL (in that order) for an editor to use.
If all of them are unset, vi is used on Unix systems,
notepad.exe on Windows systems.

so how does this solve the problem? well the only program that uses PSQL_EDITOR is Postgres, and most editors allow you to specify options at startup. In the case of vim, if you want to have use filetype=sql for the default filetype for \edit all you have to do is


export PSQL_EDITOR='vim -c "set ft=sql"'


of course if you don't use vim or you want to set some different options you'll have to read the documentation for your editor, or operating system.