Aug 24, 2009

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.


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

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

Aug 23, 2009

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.

Aug 22, 2009

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.