Обсуждение: 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@svana.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 Sat, Nov 16, 2013 at 09:26:33PM +0100, Alexey Klyukin wrote:Looks interesting, though I wonder if you could use fnmatch(3) here. Or
> Hi,
>
> Attached is the patch that improves usage of '*' wildcard in .pgpass,
> particularly in the host part. The use case is below.
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@hintbits.com> wrote: > Hi Martijn, > > On Sun, Nov 17, 2013 at 7:56 PM, Martijn van Oosterhout <kleptog@svana.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.