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

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.