[chef] Re: Re: database cookbook suggestion: Shell out for peer authentication?

[James Le Cuirot < >]

On Tue, 17 Jun 2014 15:08:28 -0700
Noah Kantrowitz 
<
 >
 wrote:
> 
> On Jun 17, 2014, at 2:46 PM, James Le Cuirot
> <
 >
>  wrote:
> > 
> > MySQL users have traditionally used password authentication but
> > PostgreSQL has put equal emphasis on peer authentication, where the
> > user is given access without a password on the basis that they are
> > already connected via the operating system user of the same name.
> > Obviously this only works for local socket connections.
> > 
> > I have always liked this concept. Why waste time and effort trying
> > to hide a password when one isn't even necessary? If an intruder has
> > already gained shell access to the account then it's probably game
> > over anyway.
> > 
> > Unfortunately the database cookbook doesn't cater for this concept
> > at all. It connects using the pg gem, meaning that the operating
> > system user is (almost?) always root. I've been thinking about
> > whether it would be possible for the "db" method to shell out to
> > psql via su, allowing peer authentication from any local user.
> 
> The primary drawback is that unless your application is a toy or
> experiment, you will have more than one server (usually at least 4,
> 2x web 2x DB for reliability). In this situation peer auth can't be
> used and because it is by far the more common case, complicating the
> code to support peer auth for single-server situations just isn't
> worthwhile.

I feel that's a tad dismissive. My company might not be Google but
we've become successful enough to warrant the need for Chef. Although
we do have some dedicated database servers, we also have more
standalone systems with their own databases and we have clones of these
for failover because it works for us. I'm sure we're not alone in doing
this.

As for it complicating the code, I decided to rise to that challenge
and took a first stab at it. It works and there's actually less code
than there was before.

https://github.com/chewi/database/commit/pg-peer-auth?w=0

A few concerns arose during this work but no show-stoppers. I need to
ensure psql can be found as it's not always in the PATH. Using
a .pgpass file would be safer than setting PGPASSWORD in the
environment. Finally, I wanted to preserve the existing behaviour in
terms of transactions as best I could. Passing the SQL using the -c
option seems to be the best way to achieve this but I fear this might
hit the command line limit on some systems; probably not Linux but
possibly Windows. Unfortunately using stdin results in slightly
different behaviour.

Regards,
James