Re: Wildcard usage enhancements in .pgpass

Hi,

Attached is the patch that improves usage of '*' wildcard in .pgpass,
particularly in the host part. The use case is below.

I work with multiple environments (like staging, production, performance
and so on), each one containing tens of different database clusters, each
cluster has its own subdomain in DNS, i.e. foo.test.db, bar.test.db and so
on. Each user has the same credentials on every database of a single
domain, i.e. .test.db databases, but different ones in other domains. For
those databases, each line in pgpass currently corresponds to a single
database, i.e.

foo1.test.db:5432:postgres:postgres:keep!tester
foo2.test.db:5432:postgres:postgres:keep!tsecret
...
foo99.test.db:5432:postgres:postgres:keep!tsecret
....
bar1.another.db.:5432:postgres:postgres:trustno1
bar2.another.db.:5432:postgres:postgres:trustno1
...

The problem I'm having is that there are just too many lines like those
(tens or even hundreds) and the new databases are added very frequently,
making it hard to keep .pgpass up-to-date.

What I'd like to have is an ability to specify a pattern in the hostname of
.pgpass, to replace the plenty of lines with one:

*.test.db:5432:postgres:postgres:keep!secret
bar*.*.db:5432:postgres:postgres:trustno1

The patch in attachment implements exactly this, by allowing '*' in the
hostname to substitute arbitrary number of characters until the dot. The
pattern is only matched when there is a '.' or ':' after the * and only in
the hostname, otherwise, '*' is treated like a normal character. It appears
that it can only be used for IPv4 addresses, i.e. instead of 255 hosts for
192.168.1.0/24 one can just specify 192.168.1.*.

I do understand that it might be a very limited use case in terms of
community plans for improving .pgpass, but I doubt that I'm the only one to
stumble upon the current limitation; the patch is quite small and might be
the first step into extending the functionality of .pgpass

It's currently missing the documentation, which I will add in case there
will be an interest in making this patch a part of the core.

Your feedback is greatly appreciated.

--
Regards,
Alexey Klyukin

On Sat, Nov 16, 2013 at 09:26:33PM +0100, Alexey Klyukin wrote:
> Hi,
>
> Attached is the patch that improves usage of '*' wildcard in .pgpass,
> particularly in the host part. The use case is below.

Looks interesting, though I wonder if you could use fnmatch(3) here. Or
woud that match more than you expect? For example, it would allow
'foo*bar' to match 'foo.bar' which your code doesn't.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer

Hi Martijn,

On Sun, Nov 17, 2013 at 7:56 PM, Martijn van Oosterhout
<kleptog(at)svana(dot)org>wrote:

> On Sat, Nov 16, 2013 at 09:26:33PM +0100, Alexey Klyukin wrote:
> > Hi,
> >
> > Attached is the patch that improves usage of '*' wildcard in .pgpass,
> > particularly in the host part. The use case is below.
>
> Looks interesting, though I wonder if you could use fnmatch(3) here. Or
> woud that match more than you expect? For example, it would allow
> 'foo*bar' to match 'foo.bar' which your code doesn't.
>

fnmatch(3) looks like a good deal and I'd certainly consider it if we go
the road of matching regular expressions, although for simpler use cases
it's an overkill, since it forces us to do an extra pass over the string to
be matched and introduces some performance penalties of using a regexp
matching engine.

--
Regards,
Alexey Klyukin

On Mon, Nov 18, 2013 at 1:57 AM, Alexey Klyukin <alexk(at)hintbits(dot)com> wrote:
> Hi Martijn,
>
> On Sun, Nov 17, 2013 at 7:56 PM, Martijn van Oosterhout <kleptog(at)svana(dot)org>
> wrote:
>>
>> On Sat, Nov 16, 2013 at 09:26:33PM +0100, Alexey Klyukin wrote:
>> > Hi,
>> >
>> > Attached is the patch that improves usage of '*' wildcard in .pgpass,
>> > particularly in the host part. The use case is below.
>>
>> Looks interesting, though I wonder if you could use fnmatch(3) here. Or
>> woud that match more than you expect? For example, it would allow
>> 'foo*bar' to match 'foo.bar' which your code doesn't.
>
> fnmatch(3) looks like a good deal and I'd certainly consider it if we go the
> road of matching regular expressions, although for simpler use cases it's an
> overkill, since it forces us to do an extra pass over the string to be
> matched and introduces some performance penalties of using a regexp matching
> engine.

Also, it seems likely that it's not supported on every platform (e.g.
Windows), possibly then requiring a src/port implementation. I'd
rather avoid introducing new platform dependencies unless they do
something really key.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 11/17/13, 1:56 PM, Martijn van Oosterhout wrote:
> Looks interesting, though I wonder if you could use fnmatch(3) here. Or
> woud that match more than you expect? For example, it would allow
> 'foo*bar' to match 'foo.bar' which your code doesn't.

The question is whether you'd want that.

We had previously considered using fnmatch() for wildcard matching of
host names in certificates and ended up deciding against that. It would
be worth checking that old thread. It would also be good if the pgpass
wildcard matching were somehow consistent with certificates, since they
are both somewhat related security features.