Thread: superuser authentication?
I'm setting up postgresql (8.1) on what I hope to be a very secure server (SUSE Linux 10.1). Only authentication allowed by anyone is 'local' (unix-domain sockets). Most users I plan on authenticating by PASSWORD (web connections are made to an apache webserver over SSL; the actual postgresql connections are themselves all local via pg_connect). What I'm not sure about is how to authenticate the postgresql superuser (user 'postgres' on my system). I'm considering: 1. Using ident (supposedly secure because of the SO_PEERCRED mechanism; and I've made a lot of effort to secure the server at the OS level) 2. Using password (_not_ stored on disk in e.g. pgpass) 3. Using reject My questions: * Is 3 overly paranoid in the context of a production server? * Would 2 or 3 hobble some kind of daemons? (A cursory search led me to think that maybe pg_autovacuum wouldn't work, and I'm not sure if there are other such daemons.) * If the choice came down to 1 vs 2, is there much argument for one over the other in terms of security? (I realize that there might not be a clear answer to that.) TIA
"woger151" <woger151@jqpx37.cotse.net> writes:
> What I'm not sure about is how to authenticate the postgresql superuser
> (user 'postgres' on my system). I'm considering:
> 1. Using ident (supposedly secure because of the SO_PEERCRED mechanism; and
> I've made a lot of effort to secure the server at the OS level)
> 2. Using password (_not_ stored on disk in e.g. pgpass)
> 3. Using reject
How are you going to do backups?
regards, tom lane
In response to Tom Lane <tgl@sss.pgh.pa.us>: > "woger151" <woger151@jqpx37.cotse.net> writes: > > What I'm not sure about is how to authenticate the postgresql superuser > > (user 'postgres' on my system). I'm considering: > > > 1. Using ident (supposedly secure because of the SO_PEERCRED mechanism; and > > I've made a lot of effort to secure the server at the OS level) > > 2. Using password (_not_ stored on disk in e.g. pgpass) > > 3. Using reject > > How are you going to do backups? Additionally ... While I would never caution someone _against_ more security, keep some things in mind. There's a user on your system that PostgreSQL runs under (probably called "postgres"). That user owns all the files where Postgres stores the tables and everything else. None of that data is encrypted by Postgres (except passwords) so any user who can su to the postgres user can bypass the database to access the data, corrupt it, and even (if they're very clever) modify it. My point being, that if an attacker gets a shell on your system, they're already very close to being able to access your PostgreSQL data. Personally, I'd set auth to password, then keep the password in a file in root's home directory and set it readable by root only. If an attacker can read that file, he already doesn't need to. This does mean that you'll have to carefully secure the script you use to make backups, since they'll need to have the password in them. But you'll need to carefully secure your backups anyway or all the other security is rather pointless. -- Bill Moran Collaborative Fusion Inc.
Bill Moran wrote: > > Personally, I'd set auth to password, then keep the password in a file in > root's home directory and set it readable by root only. If an attacker can > read that file, he already doesn't need to. > > This does mean that you'll have to carefully secure the script you use to > make backups, since they'll need to have the password in them. But you'll > need to carefully secure your backups anyway or all the other security is > rather pointless. I'd run it as a non-root backup-specific user. That way if someone compromises the backup process they're limited in the amount of damage they can do (since the user will only have write access to a few directories). Also makes auditing easier if you're that way inclined. -- Richard Huxton Archonet Ltd
----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "woger151" <woger151@jqpx37.cotse.net> Cc: <pgsql-general@postgresql.org> Sent: Wednesday, January 03, 2007 9:52 AM Subject: Re: [GENERAL] superuser authentication? > "woger151" <woger151@jqpx37.cotse.net> writes: >> What I'm not sure about is how to authenticate the postgresql superuser >> (user 'postgres' on my system). I'm considering: > >> 1. Using ident (supposedly secure because of the SO_PEERCRED mechanism; >> and >> I've made a lot of effort to secure the server at the OS level) >> 2. Using password (_not_ stored on disk in e.g. pgpass) >> 3. Using reject > > How are you going to do backups? Hadn't thought about that yet, though I know that periodic backups are mandatory. Easy to switch the authentication method back to something like password or ident if one is doing things manually anyway, but it _would_ make it hard to script things. I'll have to think more about that... > > regards, tom lane
----- Original Message ----- From: "Bill Moran" <wmoran@collaborativefusion.com> To: "woger151" <woger151@jqpx37.cotse.net> Cc: <pgsql-general@postgresql.org> Sent: Wednesday, January 03, 2007 10:09 AM Subject: Re: [GENERAL] superuser authentication? > In response to Tom Lane <tgl@sss.pgh.pa.us>: > >> "woger151" <woger151@jqpx37.cotse.net> writes: >> > What I'm not sure about is how to authenticate the postgresql superuser >> > (user 'postgres' on my system). I'm considering: >> >> > 1. Using ident (supposedly secure because of the SO_PEERCRED >> > mechanism; and >> > I've made a lot of effort to secure the server at the OS level) >> > 2. Using password (_not_ stored on disk in e.g. pgpass) >> > 3. Using reject >> >> How are you going to do backups? > > Additionally ... > > While I would never caution someone _against_ more security, keep some > things in mind. > > There's a user on your system that PostgreSQL runs under (probably called > "postgres"). That user owns all the files where Postgres stores the > tables > and everything else. None of that data is encrypted by Postgres (except > passwords) so any user who can su to the postgres user can bypass the > database to access the data, corrupt it, and even (if they're very clever) > modify it. > > My point being, that if an attacker gets a shell on your system, they're > already very close to being able to access your PostgreSQL data. Right, which is why "ident" seems pretty secure. The only reason I don't just go ahead with "ident" is that one can always wonder, "what if there's a security hole in the implementation of SO_PEERCRED?" > Personally, I'd set auth to password, then keep the password in a file in > root's home directory and set it readable by root only. If an attacker > can > read that file, he already doesn't need to. > > This does mean that you'll have to carefully secure the script you use to > make backups, since they'll need to have the password in them. But you'll > need to carefully secure your backups anyway or all the other security is > rather pointless. Right. > > -- > Bill Moran > Collaborative Fusion Inc.