Re: [HACKERS] Use samehost by default in pg_hba.conf?
Tom Lane wrote: Now that the samehost/samenet patch is in, I wonder if it wouldn't be a good idea to replace this part of the default pg_hba.conf file: You're probably not suggesting this, but I would be against a default setting of 'samehost' used with 'trust'. Essentially that would be the same as rlogin rsh, where if the user can spoof a TCP connection, he can connect to postgresql. Depending on the platform, an interface may have to be down for this to work. Cheers, Stef -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Use samehost by default in pg_hba.conf?
Tom Lane wrote: Having looked at the code, I think that samehost is pretty safe. I'm still worried about samenet picking up a bogusly broad netmask --- but samehost hard-wires the netmask at all-ones. Even if your network configuration is really screwed up, the kernel isn't going to send that traffic off-machine. So I think it will act as advertised. But will it accept traffic from off machine? If so, then essentially the only line of defense is the security of the TCP stack. Or am I missing something? Cheers, Stef -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Use samehost by default in pg_hba.conf?
Tom Lane wrote: Stef Walter stef-l...@memberwebs.com writes: Tom Lane wrote: Now that the samehost/samenet patch is in, I wonder if it wouldn't be a good idea to replace this part of the default pg_hba.conf file: You're probably not suggesting this, but I would be against a default setting of 'samehost' used with 'trust'. Essentially that would be the same as rlogin rsh, where if the user can spoof a TCP connection, he can connect to postgresql. Depending on the platform, an interface may have to be down for this to work. Is there any actual risk here that we aren't taking already just by allowing 127.0.0.1? Yes, RFC 1122 specifies that 127.0.0.0/8 must not appear outside of a single host. Checks for this are implemented. For example in FreeBSD: http://fxr.watson.org/fxr/source/netinet/ip_input.c#L440 http://fxr.watson.org/fxr/search?string=IN_LOOPBACK Obviously I can't guarantee this for any given OS. However it does seem like a pretty standard security feature of the IP stack. In my opinion, adding samehost into the default pg_hba.conf with the method set to md5 (attached patch) would be both usable and secure. Cheers, Stef diff --git a/src/backend/libpq/pg_hba.conf.sample b/src/backend/libpq/pg_hba.conf.sample index cfcd246..ea83d4d 100644 *** a/src/backend/libpq/pg_hba.conf.sample --- b/src/backend/libpq/pg_hba.conf.sample *** *** 75,77 --- 75,79 hostall all 127.0.0.1/32 @authmethod@ # IPv6 local connections: hostall all ::1/128 @authmethod@ + # Host's IPv4 and IPv6 connections: + hostall all samehost md5 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_hba.conf: samehost and samenet [REVIEW]
Tom Lane wrote: I was just poking at this. Thanks for trying it out. It seems to need rather a lot of editorialization (eg to fix the lack of consistency about whether nonstandard headers have configure tests, or bother to make use of the tests that did get added). I've now added tests for sys/ioctl.h and net/if.h even though these headers seemed to be common to all the unixes investigated. The test for ifaddrs.h is to allow the test for getifaddrs() later in configure.in to work. This is how other open source projects have handled this situation, but if you'd like me to do it differently for postgres I can. However, it does actually compile and appear to work on HPUX 10.20, which is my personal benchmark for hopeless obsolescence ;-). Good news. So modulo the issue about how much we trust the system-reported netmasks, it seems we could adopt this. FWIW, there are checks for various bad netmasks. I incorporated these techniques after seeing them in the corresponding postfix code. BTW, there's also fallback code. If none of the methods work on a given OS, then the ifaddrs code just lists 127.0.0.1/8 and ::1/128. Cheers, Stef diff --git a/configure.in b/configure.in index e545a1f..8b42684 100644 *** a/configure.in --- b/configure.in *** AC_SUBST(OSSP_UUID_LIBS) *** 969,975 ## dnl sys/socket.h is required by AC_FUNC_ACCEPT_ARGTYPES ! AC_CHECK_HEADERS([crypt.h dld.h fp_class.h getopt.h ieeefp.h langinfo.h poll.h pwd.h sys/ipc.h sys/poll.h sys/pstat.h sys/resource.h sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/tas.h sys/time.h sys/un.h termios.h ucred.h utime.h wchar.h wctype.h kernel/OS.h kernel/image.h SupportDefs.h]) # At least on IRIX, cpp test for netinet/tcp.h will fail unless # netinet/in.h is included first. --- 969,984 ## dnl sys/socket.h is required by AC_FUNC_ACCEPT_ARGTYPES ! AC_CHECK_HEADERS([crypt.h dld.h fp_class.h getopt.h ieeefp.h langinfo.h poll.h pwd.h sys/ipc.h sys/poll.h sys/pstat.h sys/resource.h sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/tas.h sys/time.h sys/un.h termios.h ucred.h utime.h wchar.h wctype.h kernel/OS.h kernel/image.h SupportDefs.h ifaddrs.h sys/ioctl.h sys/sockio.h]) ! ! # On BSD, cpp test for net/if.h will fail unless sys/socket.h ! # is included first, it's checked above. ! AC_CHECK_HEADERS(net/if.h, [], [], ! [AC_INCLUDES_DEFAULT ! #ifdef HAVE_SYS_SOCKET_H ! #include sys/socket.h ! #endif ! ]) # At least on IRIX, cpp test for netinet/tcp.h will fail unless # netinet/in.h is included first. *** PGAC_VAR_INT_TIMEZONE *** 1148,1154 AC_FUNC_ACCEPT_ARGTYPES PGAC_FUNC_GETTIMEOFDAY_1ARG ! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs]) # posix_fadvise() is a no-op on Solaris, so don't incur function overhead # by calling it, 2009-04-02 --- 1157,1163 AC_FUNC_ACCEPT_ARGTYPES PGAC_FUNC_GETTIMEOFDAY_1ARG ! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs getifaddrs]) # posix_fadvise() is a no-op on Solaris, so don't incur function overhead # by calling it, 2009-04-02 diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml index ad4d084..e5152f4 100644 *** a/doc/src/sgml/client-auth.sgml --- b/doc/src/sgml/client-auth.sgml *** hostnossl replaceabledatabase/replac *** 244,249 --- 244,255 support for IPv6 addresses. /para + paraInstead of a replaceableCIDR-address/replaceable, you can specify +literalsamehost/literal to match any of the server's own IP addresses, +or literalsamenet/literal to match any address in a subnet that the +server belongs to. + /para + para This field only applies to literalhost/literal, literalhostssl/literal, and literalhostnossl/ records. diff --git a/src/backend/libpq/hba.c b/src/backend/libpq/hba.c index e6f7db2..702971a 100644 *** a/src/backend/libpq/hba.c --- b/src/backend/libpq/hba.c *** check_db(const char *dbname, const char *** 512,517 --- 512,608 return false; } + /* + * Check to see if a connecting IP matches the address and netmask. + */ + static bool + check_ip(SockAddr *raddr, struct sockaddr *addr, struct sockaddr *mask) + { + if (raddr-addr.ss_family == addr-sa_family) + { + /* Same address family */ + if (!pg_range_sockaddr(raddr-addr, (struct sockaddr_storage*)addr, + (struct sockaddr_storage*)mask)) + return false; + } + #ifdef HAVE_IPV6 + else if (addr-sa_family == AF_INET + raddr-addr.ss_family == AF_INET6) + { + /* + * Wrong address family. We allow only one case: if the file + * has IPv4 and the port is IPv6
Re: [HACKERS] pg_hba.conf: samehost and samenet [REVIEW]
Dave Page wrote: On Mon, Sep 28, 2009 at 10:10 PM, Stef Walter stef-l...@memberwebs.com wrote: * Win32 using win_wsa2.dll I assume you mean ws2_32.dll? Yes. I get dyslexic around windows DLLs. :) Stef -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_hba.conf: samehost and samenet [REVIEW]
Robert Haas wrote: So is this one Ready for Committer? Here we go, I think this one is ready. In addition to previous patches, it does: * Use some techniques from postfix for getting interface addresses. Couldn't use code outright, due to license incompatibilities. * Tested on Solaris, FreeBSD, Linux and Windows. As far as I can tell this should also work on Mac OS, HPUX and AIX, and probably others. * Added src/tools/ifaddrs/test_ifaddrs tool for testing interface address code. Cheers, Stef diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml index ad4d084..e5152f4 100644 *** a/doc/src/sgml/client-auth.sgml --- b/doc/src/sgml/client-auth.sgml *** hostnossl replaceabledatabase/replac *** 244,249 --- 244,255 support for IPv6 addresses. /para + paraInstead of a replaceableCIDR-address/replaceable, you can specify +literalsamehost/literal to match any of the server's own IP addresses, +or literalsamenet/literal to match any address in a subnet that the +server belongs to. + /para + para This field only applies to literalhost/literal, literalhostssl/literal, and literalhostnossl/ records. diff --git a/src/backend/libpq/hba.c b/src/backend/libpq/hba.c index e6f7db2..702971a 100644 *** a/src/backend/libpq/hba.c --- b/src/backend/libpq/hba.c *** check_db(const char *dbname, const char *** 512,517 --- 512,608 return false; } + /* + * Check to see if a connecting IP matches the address and netmask. + */ + static bool + check_ip(SockAddr *raddr, struct sockaddr *addr, struct sockaddr *mask) + { + if (raddr-addr.ss_family == addr-sa_family) + { + /* Same address family */ + if (!pg_range_sockaddr(raddr-addr, (struct sockaddr_storage*)addr, + (struct sockaddr_storage*)mask)) + return false; + } + #ifdef HAVE_IPV6 + else if (addr-sa_family == AF_INET + raddr-addr.ss_family == AF_INET6) + { + /* + * Wrong address family. We allow only one case: if the file + * has IPv4 and the port is IPv6, promote the file address to + * IPv6 and try to match that way. + */ + struct sockaddr_storage addrcopy, + maskcopy; + + memcpy(addrcopy, addr, sizeof(addrcopy)); + memcpy(maskcopy, mask, sizeof(maskcopy)); + pg_promote_v4_to_v6_addr(addrcopy); + pg_promote_v4_to_v6_mask(maskcopy); + + if (!pg_range_sockaddr(raddr-addr, addrcopy, maskcopy)) + return false; + } + #endif /* HAVE_IPV6 */ + else + { + /* Wrong address family, no IPV6 */ + return false; + } + + return true; + } + + typedef struct CheckNetwork { + NetMethod method; + SockAddr *raddr; + bool result; + } CheckNetwork; + + static void + callback_check_network(struct sockaddr *addr, struct sockaddr *netmask, void *data) + { + CheckNetwork *cn = data; + struct sockaddr_storage mask; + + /* Already found a match */ + if (cn-result) + return; + + /* Make a fully 1's netmask of appropriate length */ + if (cn-method == nmSameHost) + { + pg_sockaddr_cidr_mask(mask, NULL, addr-sa_family); + cn-result = check_ip(cn-raddr, addr, (struct sockaddr*)mask); + } + + /* Use the netmask of the interface itself */ + else + { + cn-result = check_ip(cn-raddr, addr, netmask); + } + } + + static bool + check_same_host_or_net(SockAddr *raddr, NetMethod method) + { + CheckNetwork cn; + cn.method = method; + cn.raddr = raddr; + cn.result = false; + + if (pg_foreach_ifaddr(callback_check_network, cn) 0) + { + ereport(LOG, + (errcode(ERRCODE_WARNING), + errmsg(Error enumerating network interfaces))); + return false; + } + + return cn.result; + } /* * Macros used to check and report on invalid configuration options. *** parse_hba_line(List *line, int line_num, *** 658,756 line_num, HbaFileName))); return false; } - token = pstrdup(lfirst(line_item)); ! /* Check if it has a CIDR suffix and if so isolate it */ ! cidr_slash = strchr(token, '/'); ! if (cidr_slash) ! *cidr_slash = '\0'; ! ! /* Get the IP address either way */ ! hints.ai_flags = AI_NUMERICHOST; ! hints.ai_family = PF_UNSPEC; ! hints.ai_socktype = 0; ! hints.ai_protocol = 0; ! hints.ai_addrlen = 0; ! hints.ai_canonname = NULL; ! hints.ai_addr = NULL; ! hints.ai_next = NULL; ! ret = pg_getaddrinfo_all(token, NULL, hints, gai_result); ! if (ret || !gai_result) { ! ereport(LOG, ! (errcode(ERRCODE_CONFIG_FILE_ERROR), ! errmsg(invalid IP address \%s\: %s, ! token, gai_strerror(ret)), ! errcontext(line %d of configuration file \%s\, ! line_num, HbaFileName))); ! if (cidr_slash) ! *cidr_slash = '/'; ! if (gai_result) ! pg_freeaddrinfo_all(hints.ai_family, gai_result); ! return false; } ! if (cidr_slash) ! *cidr_slash = '/'; ! ! memcpy(parsedline-addr, gai_result
Re: [HACKERS] pg_hba.conf: samehost and samenet [REVIEW]
Whoops I missed this email... Robert Haas wrote: Rereading the thread, it seems that the main question is whether there are any platforms that we support that have neither getifaddrs or SIOCGIFCONF, or where they don't work properly. As far as I can tell, there are no non-ancient mainstream platforms that we're missing here. As Tom suggested, I've looked over postfix, bind and pcap and merged what I've learned into the (attached) samenet patch. I believe we're hitting all the majors here: * Win32 using win_wsa2.dll * Modern versions of: Linux, BSD, Mac OS X, AIX using getifaddrs * Modern Solaris and HPUX using ioctl/SIOCGLIFCONF * Older unixes (BSD, Linux, Solaris, AIX) using ioctl/SIOCGIFCONF SIOCGIFCONF doesn't return IPv6 information on certain platforms (such as modern Solaris, or older Linux). I believe we're covering every single Unix in use out there. I have however only verified this assertion on open source OS's. I've also verified that the SIOCGIFCONF method on Linux, BSD and Solaris, even though they use more modern methods by default. If a problem occurs with this code the src/tools/ifaddrs tool can be used to diagnose the problem, and send in debugging feedback. By the way, in foreach_ifaddr_ifconf, what happens if the number of addresses is too large to fit in the arbitrary-size buffer you've chosen here? The old approach was not a security vulnerability, and I find it hard to believe that anyone would have had more than 10K of addresses. However for the sake of completeness attached is a patch with dynamically sized buffers. This adds some code complexity, but maybe someone out there would have run into this (extremely) edge case. I believe this patch to be complete, and am looking forward to review. Cheers, Stef diff --git a/configure.in b/configure.in index e545a1f..5182714 100644 *** a/configure.in --- b/configure.in *** AC_SUBST(OSSP_UUID_LIBS) *** 969,975 ## dnl sys/socket.h is required by AC_FUNC_ACCEPT_ARGTYPES ! AC_CHECK_HEADERS([crypt.h dld.h fp_class.h getopt.h ieeefp.h langinfo.h poll.h pwd.h sys/ipc.h sys/poll.h sys/pstat.h sys/resource.h sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/tas.h sys/time.h sys/un.h termios.h ucred.h utime.h wchar.h wctype.h kernel/OS.h kernel/image.h SupportDefs.h]) # At least on IRIX, cpp test for netinet/tcp.h will fail unless # netinet/in.h is included first. --- 969,975 ## dnl sys/socket.h is required by AC_FUNC_ACCEPT_ARGTYPES ! AC_CHECK_HEADERS([crypt.h dld.h fp_class.h getopt.h ieeefp.h langinfo.h poll.h pwd.h sys/ipc.h sys/poll.h sys/pstat.h sys/resource.h sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/tas.h sys/time.h sys/un.h termios.h ucred.h utime.h wchar.h wctype.h kernel/OS.h kernel/image.h SupportDefs.h ifaddrs.h sys/sockio.h]) # At least on IRIX, cpp test for netinet/tcp.h will fail unless # netinet/in.h is included first. *** PGAC_VAR_INT_TIMEZONE *** 1148,1154 AC_FUNC_ACCEPT_ARGTYPES PGAC_FUNC_GETTIMEOFDAY_1ARG ! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs]) # posix_fadvise() is a no-op on Solaris, so don't incur function overhead # by calling it, 2009-04-02 --- 1148,1154 AC_FUNC_ACCEPT_ARGTYPES PGAC_FUNC_GETTIMEOFDAY_1ARG ! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs getifaddrs]) # posix_fadvise() is a no-op on Solaris, so don't incur function overhead # by calling it, 2009-04-02 diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml index ad4d084..e5152f4 100644 *** a/doc/src/sgml/client-auth.sgml --- b/doc/src/sgml/client-auth.sgml *** hostnossl replaceabledatabase/replac *** 244,249 --- 244,255 support for IPv6 addresses. /para + paraInstead of a replaceableCIDR-address/replaceable, you can specify +literalsamehost/literal to match any of the server's own IP addresses, +or literalsamenet/literal to match any address in a subnet that the +server belongs to. + /para + para This field only applies to literalhost/literal, literalhostssl/literal, and literalhostnossl/ records. diff --git a/src/backend/libpq/hba.c b/src/backend/libpq/hba.c index e6f7db2..702971a 100644 *** a/src/backend/libpq/hba.c --- b/src/backend/libpq/hba.c *** check_db(const char *dbname, const char *** 512,517 --- 512,608 return false; } + /* + * Check to see if a connecting IP matches the address and netmask. + */ + static bool + check_ip(SockAddr *raddr, struct sockaddr *addr, struct sockaddr *mask) + { + if (raddr-addr.ss_family == addr-sa_family) + { + /* Same address family
Re: [HACKERS] pg_hba.conf: samehost and samenet [REVIEW]
Robert Haas wrote: Attached patch contains a fix. So is this one Ready for Committer? Not yet. Two more things to do. Will work on them early next week: * On Solaris the ioctl used only returns IPv4 addresses. * Don't use hard coded buffers on win32 and ioctl. Cheers, Stef -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_hba.conf: samehost and samenet [REVIEW]
Magnus Hagander wrote: On Mon, Sep 21, 2009 at 20:12, Stef Walter stef-l...@memberwebs.com wrote: This patch does not build on Windows, the error is: ip.obj : error LNK2019: unresolved external symbol __imp__wsaio...@36 referenced in function _pg_foreach_ifaddr ip.obj : error LNK2019: unresolved external symbol __imp__wsasock...@24 referenc ed in function _pg_foreach_ifaddr .\Release\libpq\libpq.dll : fatal error LNK1120: 2 unresolved externals I don't have time to investigate this further right now, so if somebody else want to dig into why that is happening that would be helpful :) Seems there are two windows build systems. Once I discovered the MSVC one, and got it working, I added the required ws2 library (already used by other components of postgresql). Attached patch contains a fix. Cheers, Stef diff --git a/configure.in b/configure.in index e545a1f..b77ce2b 100644 *** a/configure.in --- b/configure.in *** AC_SUBST(OSSP_UUID_LIBS) *** 969,975 ## dnl sys/socket.h is required by AC_FUNC_ACCEPT_ARGTYPES ! AC_CHECK_HEADERS([crypt.h dld.h fp_class.h getopt.h ieeefp.h langinfo.h poll.h pwd.h sys/ipc.h sys/poll.h sys/pstat.h sys/resource.h sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/tas.h sys/time.h sys/un.h termios.h ucred.h utime.h wchar.h wctype.h kernel/OS.h kernel/image.h SupportDefs.h]) # At least on IRIX, cpp test for netinet/tcp.h will fail unless # netinet/in.h is included first. --- 969,975 ## dnl sys/socket.h is required by AC_FUNC_ACCEPT_ARGTYPES ! AC_CHECK_HEADERS([crypt.h dld.h fp_class.h getopt.h ieeefp.h langinfo.h poll.h pwd.h sys/ipc.h sys/poll.h sys/pstat.h sys/resource.h sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/tas.h sys/time.h sys/un.h termios.h ucred.h utime.h wchar.h wctype.h kernel/OS.h kernel/image.h SupportDefs.h ifaddrs.h]) # At least on IRIX, cpp test for netinet/tcp.h will fail unless # netinet/in.h is included first. *** PGAC_VAR_INT_TIMEZONE *** 1148,1154 AC_FUNC_ACCEPT_ARGTYPES PGAC_FUNC_GETTIMEOFDAY_1ARG ! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs]) # posix_fadvise() is a no-op on Solaris, so don't incur function overhead # by calling it, 2009-04-02 --- 1148,1154 AC_FUNC_ACCEPT_ARGTYPES PGAC_FUNC_GETTIMEOFDAY_1ARG ! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs getifaddrs]) # posix_fadvise() is a no-op on Solaris, so don't incur function overhead # by calling it, 2009-04-02 diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml index ad4d084..e5152f4 100644 *** a/doc/src/sgml/client-auth.sgml --- b/doc/src/sgml/client-auth.sgml *** hostnossl replaceabledatabase/replac *** 244,249 --- 244,255 support for IPv6 addresses. /para + paraInstead of a replaceableCIDR-address/replaceable, you can specify +literalsamehost/literal to match any of the server's own IP addresses, +or literalsamenet/literal to match any address in a subnet that the +server belongs to. + /para + para This field only applies to literalhost/literal, literalhostssl/literal, and literalhostnossl/ records. diff --git a/src/backend/libpq/hba.c b/src/backend/libpq/hba.c index e6f7db2..702971a 100644 *** a/src/backend/libpq/hba.c --- b/src/backend/libpq/hba.c *** check_db(const char *dbname, const char *** 512,517 --- 512,608 return false; } + /* + * Check to see if a connecting IP matches the address and netmask. + */ + static bool + check_ip(SockAddr *raddr, struct sockaddr *addr, struct sockaddr *mask) + { + if (raddr-addr.ss_family == addr-sa_family) + { + /* Same address family */ + if (!pg_range_sockaddr(raddr-addr, (struct sockaddr_storage*)addr, + (struct sockaddr_storage*)mask)) + return false; + } + #ifdef HAVE_IPV6 + else if (addr-sa_family == AF_INET + raddr-addr.ss_family == AF_INET6) + { + /* + * Wrong address family. We allow only one case: if the file + * has IPv4 and the port is IPv6, promote the file address to + * IPv6 and try to match that way. + */ + struct sockaddr_storage addrcopy, + maskcopy; + + memcpy(addrcopy, addr, sizeof(addrcopy)); + memcpy(maskcopy, mask, sizeof(maskcopy)); + pg_promote_v4_to_v6_addr(addrcopy); + pg_promote_v4_to_v6_mask(maskcopy); + + if (!pg_range_sockaddr(raddr-addr, addrcopy, maskcopy)) + return false; + } + #endif /* HAVE_IPV6 */ + else + { + /* Wrong address family, no IPV6 */ + return false; + } + + return true; + } + + typedef struct CheckNetwork { + NetMethod method; + SockAddr
Re: [HACKERS] pg_hba.conf: samehost and samenet [REVIEW]
Magnus Hagander wrote: On Mon, Sep 21, 2009 at 20:12, Stef Walter stef-l...@memberwebs.com wrote: snip Updated in attached patch. This patch does not build on Windows, the error is: ip.obj : error LNK2019: unresolved external symbol __imp__wsaio...@36 referenced in function _pg_foreach_ifaddr ip.obj : error LNK2019: unresolved external symbol __imp__wsasock...@24 referenc ed in function _pg_foreach_ifaddr .\Release\libpq\libpq.dll : fatal error LNK1120: 2 unresolved externals I don't have time to investigate this further right now, so if somebody else want to dig into why that is happening that would be helpful :) My windows VM is giving me problems, but I'll try look into it unless someone else beats me to do it. Also, one thought - with samenet we currently from what I can tell enumerate all interfaces. Not just those we bind to based on listen_addresses. Is that intentional, or should we restrict us to subnets reachable through the interfaces we're actually listening on? This would change the scope of the patch significantly. It seems that adding that limitation is unnecessary. In my opinion, if stricter hba security is required, and limiting to specific subnets are desired, those subnets should be entered directly into the pg_hba.conf file. Currently people are adding 0.0.0.0 to a default pg_hba.conf file in order to allow access from nearby machines, without running into the maintenance problems of hard coding IP addresses. However using 0.0.0.0 is clearly suboptimal from a security perspective. I've seen the samenet feature as a way to avoid the use of 0.0.0.0 in these cases. Obviously people who would like stricter postgres security can configure subnets manually, and would probably not be comfortable with 'automatic' decisions being made about the subnets allowed. Cheers, Stef -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_hba.conf: samehost and samenet [REVIEW]
Robert Haas wrote: On Wed, Sep 23, 2009 at 12:41 PM, Stef Walter stef-l...@memberwebs.com wrote: Currently people are adding 0.0.0.0 to a default pg_hba.conf file in order to allow access from nearby machines, without running into the maintenance problems of hard coding IP addresses. However using 0.0.0.0 is clearly suboptimal from a security perspective. If people aren't willing to take the time (5 minutes?) to create an hba.conf file that implements a reasonable security policy, I'm not sure anything we can do - and certainly not this - is going to help very much. I haven't really looked at this patch, but how confident are we that this is actually portable? It would be a shame to spend a lot of time and energy troubleshooting portability problems with a feature that - IMO - has a fairly marginal use case to begin with. Obviously this isn't the an authentication method. If you're using 'trust' authentication with anything but unix sockets you're pretty screwed anyway. This is used in conjuction with an authentication method. The core problem is with renumbering. Due to IPv4 addresses becoming more and more scarce, ISPs are regularly foisting renumbering on their customers. For example, it's in all the new contracts. Often renumbering takes place on networks where the original developers are long gone. Postgresql has always been very fragile when renumbering due to hard coded IP addresses in the pg_hba.conf file. This patch solves that problem for most of the cases, where hosts nearby on the network can talk to postgresql hosts without putting fragile rules into pg_hba.conf. Allowing host names in pg_hba.conf would also solve this problem, although the last person who tried to implement this it was a topic of contention. I asked if I should focus on reverse DNS host names in pg_hba.conf or portability for this samenet patch, and it was indicated that I should do the latter. If there is clear direction within the community to work on DNS based stuff in pg_hba.conf I'd be willing to contribute effort there. Cheers, Stef -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_hba.conf: samehost and samenet [REVIEW]
Tom Lane wrote: Mark Mielke m...@mark.mielke.cc writes: Postfix has this capability and it works fine. Hmm, have we looked at the Postfix code to see exactly how they do it? I'd be a *lot* more comfortable adopting logic that's been proven in the field than something written from scratch. Good idea. As far as I know postfix doesn't support win32. They use a similar approach with using ioctls on some systems, getifaddrs on others. I can take a look at the postfix code (src/util/inet_addr_local.c), check out licenses, add win32 support and adapt it to postgres uses. Cheers, Stef -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_hba.conf: samehost and samenet [REVIEW]
Tom Lane wrote: Stef Walter stef-l...@memberwebs.com writes: Allowing host names in pg_hba.conf would also solve this problem, although the last person who tried to implement this it was a topic of contention. I asked if I should focus on reverse DNS host names in pg_hba.conf or portability for this samenet patch, and it was indicated that I should do the latter. Agreed, a DNS-based solution would be a huge pain in the rear to do correctly. However, I think what Robert wanted to know was just how portable you believe this solution is. If it doesn't work, and work pretty much the same, on all our supported platforms then I'm afraid we can't use it. It does work the same on the platforms noted earlier. After work today, I'll put time into making sure that the winsock build problem noted earlier is sorted out. In this case what particularly scares me is the idea that 'samenet' might be interpreted to let in a larger subnet than the user expected, eg 10/8 instead of 10.0.0/24. You'd likely not notice the problem until after you'd been broken into ... As Mark noted in another email, ones networking wouldn't work at all with such a misconfiguration. But if you like I can add additional defensive checks in the code to ignore those obviously invalid netmasks like /0. Basically the OS would be giving postgres bad information. Does postgres generally try to guard against this? I'll follow the convention of the project. Cheers, Stef -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_hba.conf: samehost and samenet [REVIEW]
Thanks for your review! Abhijit Menon-Sen wrote: First, it needs to be reformatted to not use a space before the opening parentheses in (some) function calls and definitions. Fixed in the attached patch. *** a/doc/src/sgml/client-auth.sgml --- b/doc/src/sgml/client-auth.sgml [...] I'd suggest something like the following instead: paraInstead of a replaceableCIDR-address/replaceable, you can specify literalsamehost/literal to match any of the server's own IP addresses, or literalsamenet/literal to match any address in a subnet that the server belongs to. Updated in attached patch. *** a/src/backend/libpq/hba.c --- b/src/backend/libpq/hba.c [...] +else if (addr-sa_family == AF_INET + raddr-addr.ss_family == AF_INET6) +{ +/* + * Wrong address family. We allow only one case: if the file + * has IPv4 and the port is IPv6, promote the file address to + * IPv6 and try to match that way. + */ How about this instead: If we're listening on IPv6 but the file specifies an IPv4 address to match against, we promote the latter also to an IPv6 address before trying to match the client's address. As Magnus noted, this is a comment already present in the postgresql code. I simply moved it into a function. However, I've attached a second patch which fixes this issue, and can be committed at your discretion. You could just have each of the three #ifdef blocks define a function named pg_foreach_ifaddr() and be done with it. No need for a fourth function. Done. *** a/src/backend/libpq/pg_hba.conf.sample --- b/src/backend/libpq/pg_hba.conf.sample [...] + # You can also specify samehost to limit connections to those from addresses + # of the local machine. Or you can specify samenet to limit connections + # to addresses on the subnets of the local network. This should be reworded to match the documentation change suggested above. Done. Cheers, Stef diff --git a/configure.in b/configure.in index e545a1f..b77ce2b 100644 *** a/configure.in --- b/configure.in *** AC_SUBST(OSSP_UUID_LIBS) *** 969,975 ## dnl sys/socket.h is required by AC_FUNC_ACCEPT_ARGTYPES ! AC_CHECK_HEADERS([crypt.h dld.h fp_class.h getopt.h ieeefp.h langinfo.h poll.h pwd.h sys/ipc.h sys/poll.h sys/pstat.h sys/resource.h sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/tas.h sys/time.h sys/un.h termios.h ucred.h utime.h wchar.h wctype.h kernel/OS.h kernel/image.h SupportDefs.h]) # At least on IRIX, cpp test for netinet/tcp.h will fail unless # netinet/in.h is included first. --- 969,975 ## dnl sys/socket.h is required by AC_FUNC_ACCEPT_ARGTYPES ! AC_CHECK_HEADERS([crypt.h dld.h fp_class.h getopt.h ieeefp.h langinfo.h poll.h pwd.h sys/ipc.h sys/poll.h sys/pstat.h sys/resource.h sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/tas.h sys/time.h sys/un.h termios.h ucred.h utime.h wchar.h wctype.h kernel/OS.h kernel/image.h SupportDefs.h ifaddrs.h]) # At least on IRIX, cpp test for netinet/tcp.h will fail unless # netinet/in.h is included first. *** PGAC_VAR_INT_TIMEZONE *** 1148,1154 AC_FUNC_ACCEPT_ARGTYPES PGAC_FUNC_GETTIMEOFDAY_1ARG ! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs]) # posix_fadvise() is a no-op on Solaris, so don't incur function overhead # by calling it, 2009-04-02 --- 1148,1154 AC_FUNC_ACCEPT_ARGTYPES PGAC_FUNC_GETTIMEOFDAY_1ARG ! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs getifaddrs]) # posix_fadvise() is a no-op on Solaris, so don't incur function overhead # by calling it, 2009-04-02 diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml index ad4d084..e5152f4 100644 *** a/doc/src/sgml/client-auth.sgml --- b/doc/src/sgml/client-auth.sgml *** hostnossl replaceabledatabase/replac *** 244,249 --- 244,255 support for IPv6 addresses. /para + paraInstead of a replaceableCIDR-address/replaceable, you can specify +literalsamehost/literal to match any of the server's own IP addresses, +or literalsamenet/literal to match any address in a subnet that the +server belongs to. + /para + para This field only applies to literalhost/literal, literalhostssl/literal, and literalhostnossl/ records. diff --git a/src/backend/libpq/hba.c b/src/backend/libpq/hba.c index e6f7db2..702971a 100644 *** a/src/backend/libpq/hba.c --- b/src/backend/libpq/hba.c *** check_db(const char *dbname, const char *** 512,517 --- 512,608 return false
Re: [HACKERS] pg_hba.conf: samehost and samenet
[Thanks for the heads up about the MessageID missing when posting this previously. Was doing some mail filter development, and accidentally left it in place... ] Magnus Hagander wrote: 2009/8/25 Alvaro Herrera alvhe...@commandprompt.com: Something to keep in mind -- my getifaddrs(3) manpage says that on BSD it can return addresses that have ifa_addr set to NULL, which your code doesn't seem to check. Thanks for catching that. I've added a check, and attached a new patch. Eek. This is not defined by any standard, is it? I wonder how many different behaviours we can find there :( I've checked AIX, Linux, BSD and Mac OS and NULL ifa_addr's are documented in all of them. Cheers, Stef diff --git a/configure.in b/configure.in index e545a1f..b77ce2b 100644 *** a/configure.in --- b/configure.in *** AC_SUBST(OSSP_UUID_LIBS) *** 969,975 ## dnl sys/socket.h is required by AC_FUNC_ACCEPT_ARGTYPES ! AC_CHECK_HEADERS([crypt.h dld.h fp_class.h getopt.h ieeefp.h langinfo.h poll.h pwd.h sys/ipc.h sys/poll.h sys/pstat.h sys/resource.h sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/tas.h sys/time.h sys/un.h termios.h ucred.h utime.h wchar.h wctype.h kernel/OS.h kernel/image.h SupportDefs.h]) # At least on IRIX, cpp test for netinet/tcp.h will fail unless # netinet/in.h is included first. --- 969,975 ## dnl sys/socket.h is required by AC_FUNC_ACCEPT_ARGTYPES ! AC_CHECK_HEADERS([crypt.h dld.h fp_class.h getopt.h ieeefp.h langinfo.h poll.h pwd.h sys/ipc.h sys/poll.h sys/pstat.h sys/resource.h sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/tas.h sys/time.h sys/un.h termios.h ucred.h utime.h wchar.h wctype.h kernel/OS.h kernel/image.h SupportDefs.h ifaddrs.h]) # At least on IRIX, cpp test for netinet/tcp.h will fail unless # netinet/in.h is included first. *** PGAC_VAR_INT_TIMEZONE *** 1148,1154 AC_FUNC_ACCEPT_ARGTYPES PGAC_FUNC_GETTIMEOFDAY_1ARG ! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs]) # posix_fadvise() is a no-op on Solaris, so don't incur function overhead # by calling it, 2009-04-02 --- 1148,1154 AC_FUNC_ACCEPT_ARGTYPES PGAC_FUNC_GETTIMEOFDAY_1ARG ! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs getifaddrs]) # posix_fadvise() is a no-op on Solaris, so don't incur function overhead # by calling it, 2009-04-02 diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml index ad4d084..e88c796 100644 *** a/doc/src/sgml/client-auth.sgml --- b/doc/src/sgml/client-auth.sgml *** hostnossl replaceabledatabase/replac *** 244,249 --- 244,256 support for IPv6 addresses. /para + paraInstead of an replaceableCIDR-address/replaceable, you can specify +the values literalsamehost/literal or literalsamenet/literal. To +match any address on the subnets connected to the local machine, specify +literalsamenet/literal. By specifying literalsamehost/literal, any +addresses present on the network interfaces of local machine will match. + /para + para This field only applies to literalhost/literal, literalhostssl/literal, and literalhostnossl/ records. diff --git a/src/backend/libpq/hba.c b/src/backend/libpq/hba.c index e6f7db2..c2da3a0 100644 *** a/src/backend/libpq/hba.c --- b/src/backend/libpq/hba.c *** check_db(const char *dbname, const char *** 512,517 --- 512,608 return false; } + /* + * Check to see if a connecting IP matches the address and netmask. + */ + static bool + check_ip (SockAddr *raddr, struct sockaddr *addr, struct sockaddr *mask) + { + if (raddr-addr.ss_family == addr-sa_family) + { + /* Same address family */ + if (!pg_range_sockaddr(raddr-addr, (struct sockaddr_storage*)addr, + (struct sockaddr_storage*)mask)) + return false; + } + #ifdef HAVE_IPV6 + else if (addr-sa_family == AF_INET + raddr-addr.ss_family == AF_INET6) + { + /* + * Wrong address family. We allow only one case: if the file + * has IPv4 and the port is IPv6, promote the file address to + * IPv6 and try to match that way. + */ + struct sockaddr_storage addrcopy, + maskcopy; + + memcpy(addrcopy, addr, sizeof(addrcopy)); + memcpy(maskcopy, mask, sizeof(maskcopy)); + pg_promote_v4_to_v6_addr(addrcopy); + pg_promote_v4_to_v6_mask(maskcopy); + + if (!pg_range_sockaddr(raddr-addr, addrcopy, maskcopy)) + return false; + } + #endif /* HAVE_IPV6 */ + else + { + /* Wrong address family, no IPV6 */ + return false; + } + + return true; + } + + typedef struct CheckNetwork { + NetMethod method; + SockAddr
Re: [HACKERS] pg_hba.conf: samehost and samenet
Magnus Hagander wrote: On Wed, Aug 19, 2009 at 15:02, Stef Walterstef-l...@memberwebs.com wrote: Magnus Hagander wrote: On Wed, Aug 19, 2009 at 03:58, Stef Walterstef-l...@memberwebs.com wrote: Attached is a new patch, which I hope addresses all the concerns raised. I think you forgot to actually attach the patch Whoops. Here it is. Is there any actual advantage to using getifaddr() on Linux, It is in my opinion, it is the most modern and maintainable of the methods for obtaining network interface address information. Various unixes have added support for getifaddr() over the years, and (again my opinion) would probably continue to do so. and not just use SIOCGIFCONF for all Unixen? I do know that using SIOCGIFCONF on AIX comes with strange wrinkles and variable length data structures etc... getifaddrs() on AIX is a far more maintainable interface. That said, I'll adjust the patch as you feel is best for the long term inclusion in the postgresql source. Cheers, Stef -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_hba.conf: samehost and samenet
Magnus Hagander wrote: On Wed, Aug 19, 2009 at 03:58, Stef Walterstef-l...@memberwebs.com wrote: Attached is a new patch, which I hope addresses all the concerns raised. I think you forgot to actually attach the patch Whoops. Here it is. Stef diff --git a/configure.in b/configure.in index 505644a..bc37b1b 100644 *** a/configure.in --- b/configure.in *** AC_SUBST(OSSP_UUID_LIBS) *** 962,968 ## dnl sys/socket.h is required by AC_FUNC_ACCEPT_ARGTYPES ! AC_CHECK_HEADERS([crypt.h dld.h fp_class.h getopt.h ieeefp.h langinfo.h poll.h pwd.h sys/ipc.h sys/poll.h sys/pstat.h sys/resource.h sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/tas.h sys/time.h sys/un.h termios.h ucred.h utime.h wchar.h wctype.h kernel/OS.h kernel/image.h SupportDefs.h]) # At least on IRIX, cpp test for netinet/tcp.h will fail unless # netinet/in.h is included first. --- 962,968 ## dnl sys/socket.h is required by AC_FUNC_ACCEPT_ARGTYPES ! AC_CHECK_HEADERS([crypt.h dld.h fp_class.h getopt.h ieeefp.h langinfo.h poll.h pwd.h sys/ipc.h sys/poll.h sys/pstat.h sys/resource.h sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/tas.h sys/time.h sys/un.h termios.h ucred.h utime.h wchar.h wctype.h kernel/OS.h kernel/image.h SupportDefs.h ifaddrs.h]) # At least on IRIX, cpp test for netinet/tcp.h will fail unless # netinet/in.h is included first. *** PGAC_VAR_INT_TIMEZONE *** 1141,1147 AC_FUNC_ACCEPT_ARGTYPES PGAC_FUNC_GETTIMEOFDAY_1ARG ! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs]) # posix_fadvise() is a no-op on Solaris, so don't incur function overhead # by calling it, 2009-04-02 --- 1141,1147 AC_FUNC_ACCEPT_ARGTYPES PGAC_FUNC_GETTIMEOFDAY_1ARG ! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs getifaddrs]) # posix_fadvise() is a no-op on Solaris, so don't incur function overhead # by calling it, 2009-04-02 diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml index ad4d084..e88c796 100644 *** a/doc/src/sgml/client-auth.sgml --- b/doc/src/sgml/client-auth.sgml *** hostnossl replaceabledatabase/replac *** 244,249 --- 244,256 support for IPv6 addresses. /para + paraInstead of an replaceableCIDR-address/replaceable, you can specify +the values literalsamehost/literal or literalsamenet/literal. To +match any address on the subnets connected to the local machine, specify +literalsamenet/literal. By specifying literalsamehost/literal, any +addresses present on the network interfaces of local machine will match. + /para + para This field only applies to literalhost/literal, literalhostssl/literal, and literalhostnossl/ records. diff --git a/src/backend/libpq/hba.c b/src/backend/libpq/hba.c index b3df7ee..5d56603 100644 *** a/src/backend/libpq/hba.c --- b/src/backend/libpq/hba.c *** check_db(const char *dbname, const char *** 564,569 --- 564,660 return false; } + /* + * Check to see if a connecting IP matches the address and netmask. + */ + static bool + check_ip (SockAddr *raddr, struct sockaddr *addr, struct sockaddr *mask) + { + if (raddr-addr.ss_family == addr-sa_family) + { + /* Same address family */ + if (!pg_range_sockaddr(raddr-addr, (struct sockaddr_storage*)addr, + (struct sockaddr_storage*)mask)) + return false; + } + #ifdef HAVE_IPV6 + else if (addr-sa_family == AF_INET + raddr-addr.ss_family == AF_INET6) + { + /* + * Wrong address family. We allow only one case: if the file + * has IPv4 and the port is IPv6, promote the file address to + * IPv6 and try to match that way. + */ + struct sockaddr_storage addrcopy, + maskcopy; + + memcpy(addrcopy, addr, sizeof(addrcopy)); + memcpy(maskcopy, mask, sizeof(maskcopy)); + pg_promote_v4_to_v6_addr(addrcopy); + pg_promote_v4_to_v6_mask(maskcopy); + + if (!pg_range_sockaddr(raddr-addr, addrcopy, maskcopy)) + return false; + } + #endif /* HAVE_IPV6 */ + else + { + /* Wrong address family, no IPV6 */ + return false; + } + + return true; + } + + typedef struct CheckNetwork { + NetMethod method; + SockAddr *raddr; + bool result; + } CheckNetwork; + + static void + callback_check_network (struct sockaddr *addr, struct sockaddr *netmask, void *data) + { + CheckNetwork *cn = data; + struct sockaddr_storage mask; + + /* Already found a match */ + if (cn-result) + return; + + /* Make a fully 1's netmask of appropriate length */ + if (cn-method == nmSameHost) + { + pg_sockaddr_cidr_mask (mask, NULL, addr-sa_family); + cn-result
Re: [HACKERS] pg_hba.conf: samehost and samenet
Attached is a new patch, which I hope addresses all the concerns raised. Magnus Hagander wrote: I've attached an initial patch which implements samehost and samenet. The patch looks more invasive than it really is, due to necessary indentation change (ie: a if block), and moving some code into a separate function. A couple of comments on the patch: * In general, don't include configure in the patch. Just configure.in. Makes it easier to read, and configure is normally built by the committer anyway. Removed configure and pg_config.h.in from the patch. * How portable is this? For starters is clearly doesn't do Windows, which would need to be investigated for similar functionality, but how many others support getifaddr()? From what I can tell it's not in POSIX, at least. getifaddr() is at least supported on *BSD, Linux and AIX. In the new patch, I've added support for other unixes like Solaris, HPUX, IRIC, SCO Unix (using the SIOCGIFCONF ioctl). Also included is Win32 support (using winsock's SIO_GET_INTERFACE_LIST). Obviously I don't have all of the above proprietary unixes to test on, but I've studied documentation, and I believe that the code in the patch will run on those systems. * It needs to include documentation changes Done. Please add it to the open commitfest (https://commitfest.postgresql.org/action/commitfest_view/open). This will cause it to be reviewed during the next commitfest, and then you just need to be around to answer any questions that reviewers come up with :-) I need some sort of a login to add a patch to the commit fest. Is that something I can get? Or is there someone I should ask to add my patch to the commit fest? I hope I'm not being dense and missing something obvious. :) Cheers, Stef -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_hba.conf: samehost and samenet
Magnus Hagander wrote: A couple of comments on the patch: Thanks I'll keep these in mind, as things progress and for future patches. * In general, don't include configure in the patch. Just configure.in. Makes it easier to read, and configure is normally built by the committer anyway. * How portable is this? For starters is clearly doesn't do Windows, which would need to be investigated for similar functionality, but how many others support getifaddr()? From what I can tell it's not in POSIX, at least. I'll look further into this, and about compat shims. getifaddrs() is on the BSDs, Linux and Mac OS. Please add it to the open commitfest (https://commitfest.postgresql.org/action/commitfest_view/open). This will cause it to be reviewed during the next commitfest, and then you just need to be around to answer any questions that reviewers come up with :-) Cool, I'll do that once we've worked out the kinks here. Is the right way to go about it? Cheers, Stef -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_hba.conf: samehost and samenet
Tom Lane wrote: Magnus Hagander mag...@hagander.net writes: On Fri, Aug 14, 2009 at 00:50, Stef Walterstef-l...@memberwebs.com wrote: It would be great if, in the cidr-address field of pg_hba.conf, we could specify samehost and samenet. Seems like a reasonable feature - especially the samehost part. ISTM people have traditionally used 127.0.0.1 and ::1 for samehost behavior. Yes for sure. As noted in the original email 127.0.0.1 doesn't work as you would expect in BSD jails. As it currently stands, you have to put the local IP address to achieve similar access control. This causes major pains when renumbering or dealing with postgresql hosted in large amounts of jails. Another way we could sort of get around most of these renumbering problems, is by the ability to include host names in pg_hba.conf, rather than IP addresses. I first set out to implement this, but as advised in How to Contribute looked around the mailing lists for previous discussion on the topic and found this: http://archives.postgresql.org/pgsql-hackers/2008-06/msg00569.php There seems to be no consensus in the postgresql community about this feature, and its implementation. The last guy who tried to work on it got scared away, and so I decided to try an approach that might be more palatable. I'm willing to put in the work on either approach, and I could revive discussion about host names in pg_hba.conf if that's more desirable. What's being suggested here is a tad more flexible but hardly a huge advance. As for samenet, personally I'd be scared to death of something like that --- who knows how wide the OS will think your net is? (Think cable modem users on 10.x.x.x ...) Using samenet in a conf file that's being handed out to random users seems impossibly dangerous. I understand what you're saying. In this case it would be handed out to hosted clients and those sorts of users. ie: a controlled environment. Obviously this wouldn't go into the default postgresql pg_hba.conf. However, I wouldn't object too much if it weren't for this: * How portable is this? For starters is clearly doesn't do Windows, which would need to be investigated for similar functionality, but how many others support getifaddr()? From what I can tell it's not in POSIX, at least. I don't see it on HPUX, for one. Unless a portable solution can be found I don't think we can consider this. We're not in the habit of exposing significant functionality that's only available on some platforms. True. I could build compatibility getifaddrs for various systems, if the community thought this patch was worth it, and would otherwise accept the patch. Cheers, Stef -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_hba.conf: samehost and samenet
I love using postgresql, and have for a long time. I'm involved with almost a hundred postgresql installs. But this is the first time I've gotten into the code. Renumbering networks happens often, and will happen more frequently as IPv4 space runs low. The IP based restrictions in pg_hba.conf is one of the places where renumbering can break running installs. In addition when postgresql is run in BSD jails, 127.0.0.1 is not available for use in pg_hba.conf. It would be great if, in the cidr-address field of pg_hba.conf, we could specify samehost and samenet. These special values use the local hosts network interface addresses. samehost allows an IP assigned to the local machine. samenet allows any host on the subnets connected to the local machine. This is similar to the sameuser value that's allowed in the database field. A change like this would enable admins like myself to distribute postgresql with something like this in the default pg_hba.conf file: host all all samenet md5 hostssl all all 0.0.0.0/0 md5 I've attached an initial patch which implements samehost and samenet. The patch looks more invasive than it really is, due to necessary indentation change (ie: a if block), and moving some code into a separate function. Thanks for your time. How can I help get a feature like this into postgresql? Cheers, Stef diff --git a/configure b/configure index 61b3c72..7bcfcec 100755 *** a/configure --- b/configure *** done *** 9642,9648 ! for ac_header in crypt.h dld.h fp_class.h getopt.h ieeefp.h langinfo.h poll.h pwd.h sys/ipc.h sys/poll.h sys/pstat.h sys/resource.h sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/tas.h sys/time.h sys/un.h termios.h ucred.h utime.h wchar.h wctype.h kernel/OS.h kernel/image.h SupportDefs.h do as_ac_Header=`$as_echo ac_cv_header_$ac_header | $as_tr_sh` if { as_var=$as_ac_Header; eval test \\${$as_var+set}\ = set; }; then --- 9642,9649 ! ! for ac_header in crypt.h dld.h fp_class.h getopt.h ieeefp.h langinfo.h poll.h pwd.h sys/ipc.h sys/poll.h sys/pstat.h sys/resource.h sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/tas.h sys/time.h sys/un.h termios.h ucred.h utime.h wchar.h wctype.h kernel/OS.h kernel/image.h SupportDefs.h ifaddrs.h do as_ac_Header=`$as_echo ac_cv_header_$ac_header | $as_tr_sh` if { as_var=$as_ac_Header; eval test \\${$as_var+set}\ = set; }; then *** fi *** 17278,17284 ! for ac_func in cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs do as_ac_var=`$as_echo ac_cv_func_$ac_func | $as_tr_sh` { $as_echo $as_me:$LINENO: checking for $ac_func 5 --- 17279,17286 ! ! for ac_func in cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs getifaddrs do as_ac_var=`$as_echo ac_cv_func_$ac_func | $as_tr_sh` { $as_echo $as_me:$LINENO: checking for $ac_func 5 diff --git a/configure.in b/configure.in index 505644a..bc37b1b 100644 *** a/configure.in --- b/configure.in *** AC_SUBST(OSSP_UUID_LIBS) *** 962,968 ## dnl sys/socket.h is required by AC_FUNC_ACCEPT_ARGTYPES ! AC_CHECK_HEADERS([crypt.h dld.h fp_class.h getopt.h ieeefp.h langinfo.h poll.h pwd.h sys/ipc.h sys/poll.h sys/pstat.h sys/resource.h sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/tas.h sys/time.h sys/un.h termios.h ucred.h utime.h wchar.h wctype.h kernel/OS.h kernel/image.h SupportDefs.h]) # At least on IRIX, cpp test for netinet/tcp.h will fail unless # netinet/in.h is included first. --- 962,968 ## dnl sys/socket.h is required by AC_FUNC_ACCEPT_ARGTYPES ! AC_CHECK_HEADERS([crypt.h dld.h fp_class.h getopt.h ieeefp.h langinfo.h poll.h pwd.h sys/ipc.h sys/poll.h sys/pstat.h sys/resource.h sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/tas.h sys/time.h sys/un.h termios.h ucred.h utime.h wchar.h wctype.h kernel/OS.h kernel/image.h SupportDefs.h ifaddrs.h]) # At least on IRIX, cpp test for netinet/tcp.h will fail unless # netinet/in.h is included first. *** PGAC_VAR_INT_TIMEZONE *** 1141,1147 AC_FUNC_ACCEPT_ARGTYPES PGAC_FUNC_GETTIMEOFDAY_1ARG ! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs]) # posix_fadvise() is a no-op on Solaris, so don't incur function overhead # by calling it, 2009-04-02 --- 1141,1147 AC_FUNC_ACCEPT_ARGTYPES PGAC_FUNC_GETTIMEOFDAY_1ARG ! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs getifaddrs]) # posix_fadvise
Re: [HACKERS] ROLLBACK triggers?
Hello Jonah, Sorry for hi-jacking the thread (not my intent, I assure you) however, is there any chance Jonah of expanding your work to include 'on user login/logout' ? As an aside, a trigger on rollback seems... unlikely (at least to my mind). What is the functionality if your rollback trigger fails ? Ugh. Down that road I can see madness looming, however, this -is- monday so ... :) Regards Stef Jonah H. Harris wrote: Daisuke, A patch was done for replication hooks which implements global database-level triggers for connection startup and shutdown, and transaction begin, commit, and rollback; they may help you out in this situation. http://gorda.di.uminho.pt/community/pgsqlhooks/ -Jonah On 1/23/06, Daisuke Maki [EMAIL PROTECTED] wrote: Hi, First, apologies if my question is a bit off-course. Please feel free to direct me to a different mailing list if not appropriate. I'm currently trying to embed Senna full text search engine ( http://qwik.jp/senna/) into postgres. I'm trying to achieve this by using triggers (implemented in C) to cause an update to senna's index at various points. This seemed to work fine until I realized that while postgres' SQL commands could be rolled back, Senna's index remained already-changed. There are other potential issues with regards to transaction safety, but currently this seems to be a problem that I cannot fix by simply patching Senna. So I thought that if there was a rollback trigger, I could call whatever necessary to undo the changes that were made to the index. A quick scan of the docs and the source code tree seems to indicate that there is no such thing as a rollback trigger, short of hacking it. Now, I'm wondering: 1. Is there a rollback/commit trigger? If not, is it planned to be implemented at all? 2. Is there a way to undo changes to data external to postgres when a rollback occurs, OR, only update that external data when a commit occurs? Thanks in advance, --d ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://wwwpostgresql.org/docs/faq .
[HACKERS] Obtaining Firing Statement clause in (pl/perlu) Trigger Function
Hello Everyone, Apologies in advance, its time for another of my whacky 'can we do this' emails ;) I am trying to write a function/Trigger in pl/perl (although any other language which allows this is perfectly fine with me :) and I need to find the firing statement. I understand that if the trigger was fired in a long sequence, then of course, the calling statement will be the previous trigger. The scenario comes from that I am trying to setup a function which queries a remote Sybase server. I have the functionality such that this works; select * from test_func('where userid=2313423'); It then returns a SETOF the table in question. However, the syntax is, to put it politely, klunky (it smells of a kludge, which, it is :). Therefore, if I can get access to the where section of the calling statement in the Trigger itself, i can have a syntax which reads as follows; select * from test_func() where userid=2324142; The reason for needing the syntax inside the Trigger, is obviously with large tables, grabbing all the tuples and then parsing them down once obtained is, rather crazy. Some of the tables in question have at least 3 million records. Flinging them all around only to get a few (or most of the time one) seems rather crazy. Do any of the pl/ languages support getting at the calling trigger statement ? I am not adverse to trying to 'fix' pl/perl, however, if the trigger never even gets passed this information in the first place, well, things are going to start getting messy, and probably spiral outside of my control :) Regards and Thanks Stef pgpS2GmOnfQcM.pgp Description: PGP signature
[HACKERS] RFC: Query Planner making a distinction between Cross Database and Cross Schema ?
Hey there everyone. Sorry for what seems to be a rather strange thought but, could we change the seperator used to distinguish 'cross-database' vs 'cross-schema' ? For example, i would expect the following to work: CREATE OR REPLACE FUNCTION test_autohist() RETURNS trigger AS 'BEGIN INSERT INTO history.test2 VALUES (new.field1,history.test_hist.nextval(), new.field2, new.field3, new.field4, new.creation_id, new.creation_date, new.creation_id, new.creation_date); RETURN ; END;' LANGUAGE 'plpgsql'; CREATE TRIGGER test_autohist_trig AFTER INSERT OR UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE test_autohist(); However, when i try it, as far as i can tell, the planner parses it down, finds that the schema isnt in the current 'search_path' and thus thinks its a cross database call. I understand that it may take the planner a while to go through all the available schema's to then deduce that it isnt infact a schema at all and return the fact that 'cross-database queries are currently not implemented', therefore, in the realm of crazy idea, would it be possible to change the notation to reference another db ? I was thinking something along the lines of '@', but i guess any other non-important ascii character would make sense. That way, the planner could decide wether or not to attempt a schema resolution (history.table1) or database resolution ([EMAIL PROTECTED]). Please note, i am not asking for any sort of 'make cross-database work', merely asking if some sort of 'clarification' between cross-database and cross-schema would be beneficial. Ideas ? Comments ? regards Stef ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] RFC: Query Planner making a distinction between Cross Database and Cross Schema ?
U. Postgresql doesn't natively support cross database queries... I know, but it does schema's, and currently, the same notation is used to specify schema's as 'cross database'. So the planner often reports 'cross-database not allowed' in areas where it should at least report 'cross-schema support is unavailable for this' case in point, the example trigger. i would have expected deliberate schemaname.table during an insert to work, but instead the parser complains about cross-database. this is why i am saying that if the parser could identify schema vs database naming, it would help in clarification of areas where the parser/planner seems to get 'confused' about what the object is (schema vs database) currently: history.table1 -- schema history2.table1 -- database whereas what i am saying is: history.table1 -- schema [EMAIL PROTECTED] -- database readability and consistancy is what i am driving at here, although it would then be possible for the triggers to be able to insert/update into schema's that are specifically named instead of coming back with a 'cross-database not allowed' (when i am trying to do cross-schema :) regards Stef ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] RFC: Query Planner making a distinction between Cross Database and Cross Schema ?
case in point, the example trigger. i would have expected deliberate schemaname.table during an insert to work, but instead the parser complains about cross-database. I would think just changing the error message to no schema by the name of suchandsuch found would make it pretty clear. indeed, the only problem being, that this is a -deliberately- called schema and it does exist jst that its not in the search_path. surely the pl/pglsql or parser should be able to search the schemanames if i give it a -deliberate- name and if it isnt there say 'not found' ? so, either thats an error (improper parsing/expr forming) in the pl/pgsql trigger code, or, well, i dont know. thoughts ? comments ? barking mad ? Stef ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly