Re: [HACKERS] [PATCH] add ssl_protocols configuration option

[Dag-Erling Smørgrav]

Alex Shulgin a...@commandprompt.com writes:
 OK, looks like I've come up with something workable: I've added
 sslprotocol connection string keyword similar to pre-existing
 sslcompression, etc.  Please see attached v2 of the original patch.
 I'm having doubts about the name of openssl.h header though,
 libpq-openssl.h?

Perhaps ssloptions.[ch], unless you plan to add non-option-related code
there later?

BTW, there is no Regent code in your openssl.c, so the copyright
statement is incorrect.

DES
-- 
Dag-Erling Smørgrav - d...@des.no


-- 
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] [PATCH] add ssl_protocols configuration option

[Dag-Erling Smørgrav]

Alex Shulgin a...@commandprompt.com writes:
 I can do that too, just need a hint where to look at in libpq/psql to
 add the option.

The place to *enforce* the option is src/interfaces/libpq/fe-secure.c
(look for SSLv23_method() and SSL_CTX_set_options()).  I haven't looked
into how to set it.

DES
-- 
Dag-Erling Smørgrav - d...@des.no


-- 
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] [PATCH] add ssl_protocols configuration option

[Dag-Erling Smørgrav]

Alex Shulgin a...@commandprompt.com writes:
 * The patch works as advertised, though the only way to verify that
   connections made with the protocol disabled by the GUC are indeed
   rejected is to edit fe-secure-openssl.c to only allow specific TLS
   versions.  Adding configuration on the libpq side as suggested in the
   original discussion might help here.

I can easily do that, but I won't have time until next week or so.

DES
-- 
Dag-Erling Smørgrav - d...@des.no


-- 
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] [PATCH] add ssl_protocols configuration option

[Dag-Erling Smørgrav]

Magnus Hagander mag...@hagander.net writes:
 Alex Shulgin a...@commandprompt.com writes:
  * The code allows specifying SSLv2 and SSLv3 in the GUC, but removes
them forcibly after parsing the complete string (a warning is issued).
Should we also add a note about this to the documentation?
 I see no reason to accept them at all, if we're going to reject them
 later anyway.

 We can argue (as was done earlier in this thread) if we can drop SSL
 3.0 completely -- but we can *definitely* drop SSLv2, and we should.
 But anything that we're going to reject at a later stage anyway, we
 should reject early.

It's not really early or late, but rather within the loop or at the
end of it.  From the users' perspective, the difference is that they
get (to paraphrase) SSLv2 is not allowed instead of syntax error and
that they can use constructs such as ALL:-SSLv2.

DES
-- 
Dag-Erling Smørgrav - d...@des.no


-- 
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] [PATCH] add ssl_protocols configuration option

[Dag-Erling Smørgrav]

Alvaro Herrera alvhe...@2ndquadrant.com writes:
 OpenSSL just announced a week or two ago that they're abandoning support
 for 0.9.8 by the end of next year[1], which means its replacements have
 been around for a really long time.

RHEL5 still has 0.9.8e with backported patches and will be supported
until 2017-03-31.

FreeBSD 8.4, 9.1, 9.2 and 9.3 all have 0.9.8y with backported patches.
8.4, 9.1 and 9.2 all expire before OpenSSL 0.9.8, but 9.3 will be
supported until 2016-12-31.

0.9.8 and 1.0.1 are not binary compatible, so upgrading is *not* an
option.  We (as in FreeBSD) will have to make do - either develop our
own patches or adapt RedHat's.

 OpenSSL 0.9.7 has already not gotten fixes for all the latest flurry of
 security issues, so anyone *is* using SSL but not at least the 0.9.8
 branch, they are in trouble.

The latest 0.9.8 still only has TLS 1.0, unless they're planning to
backport 1.1 and 1.2 (which I seriously doubt).

DES
-- 
Dag-Erling Smørgrav - d...@des.no


-- 
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] [PATCH] add ssl_protocols configuration option

[Dag-Erling Smørgrav]

Tom Lane t...@sss.pgh.pa.us writes:
 Anyone who is feeling paranoid about shutting off SSLv3 despite (1)
 can do so via the existing ssl_ciphers GUC parameter [...] the ciphers
 string includes categories corresponding to protocol versions, so you
 can shut off an old protocol version there if you need to.

The overlap between SSL 3.0 and TLS 1.0 is 100%:

% openssl ciphers SSLv2 | md5  
fe5ff23432f119364a1126ca0776c5db
% openssl ciphers SSLv3 | md5 
bde4e4a10b9c3f323c0632ad067e293a
% openssl ciphers TLSv1 | md5
bde4e4a10b9c3f323c0632ad067e293a
% openssl ciphers TLSv1.2 | md5
26c375b6bdefb018b9dd7df463658320

Thus, if you disable all SSL 3.0 ciphers, you also disable TLS 1.0.

DES
-- 
Dag-Erling Smørgrav - d...@des.no


-- 
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] [PATCH] add ssl_protocols configuration option

[Dag-Erling Smørgrav]

Tom Lane t...@sss.pgh.pa.us writes:
 This looks to me like re-fighting the last war.  Such a GUC has zero value
 *unless* some situation exactly like the POODLE bug comes up again, and
 the odds of that are not high.

Many people would have said the exact same thing before POODLE, and
BEAST, and CRIME, and Heartbleed.  You never know what sort of bugs or
weaknesses will show up or when; all you know is that there are a lot of
people working very hard to find these things and exploit them, and that
they *will* succeeded, again and again and again.  You can gamble that
PostgreSQL will not be vulnerable due to specific details of its
protocol or how it uses TLS, but that's a gamble which you will
eventually lose.

 Moreover, the GUC could easily be misused to decrease rather than increase
 one's security, if it's carelessly set.

That's the user's responsibility.

DES
-- 
Dag-Erling Smørgrav - d...@des.no


-- 
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] [PATCH] add ssl_protocols configuration option

[Dag-Erling Smørgrav]

Magnus Hagander mag...@hagander.net writes:
 If anything, I think the default should be default, and then we have
 that map out to something. Because once you've initdb'ed, the config
 file wil be stuck with a default and we can't change that in a minor
 release *if* something like POODLE shows up again.

Actually, I had that in an earlier version of the patch, but I thought
it was too obscure / circular.  I can easily re-add it.

 In a case like POODLE we probably wouldn't have done it anyway, as it
 doesn't affect our connections (we're not http)

If I understand correctly, imaps has been shown to be vulnerable as
well, so I wouldn't be so sure.

 Having the guc could certainly be useful in some cases. If we do, we
 should of course *also* have a corresponding configuration option in
 libpq, so I'd say this patch is incomplete if we do want to do it.

I can update the patch to include the client side.

DES
-- 
Dag-Erling Smørgrav - d...@des.no


-- 
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] [PATCH] add ssl_protocols configuration option

[Dag-Erling Smørgrav]

Tom Lane t...@sss.pgh.pa.us writes:
 And in the end, if we set values like this from PG --- whether
 hard-wired or via a GUC --- the SSL library people will have exactly
 the same perspective with regards to *our* values.  And not without
 reason; we were forcing very obsolete settings up till recently,
 because nobody had looked at the issue for a decade.  I see no reason
 to expect that that history won't repeat itself.

I'm not sure what you're saying here, but - I'm not sure how familiar
you are with the OpenSSL API, but it's insecure by default.  There is
*no other choice* for an application than to explicitly select which
protocols it wants to use (or at least which protocols it wants to
avoid).  And you can't change OpenSSL, because a ton of old crappy
software is going to break.

DES
-- 
Dag-Erling Smørgrav - d...@des.no


-- 
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] [PATCH] add ssl_protocols configuration option

[Dag-Erling Smørgrav]

Tom Lane t...@sss.pgh.pa.us writes:
 As far as protocol version goes, I think our existing coding basically
 says prefer newest available version, but at least TLS 1.0.  I think
 that's probably a reasonable approach.

The client side forces TLS 1.0:

SSL_context = SSL_CTX_new(TLSv1_method());

In typical OpenSSL fashion, this does *not* mean 1.0 or higher.  It
means 1.0 exactly.

 If the patch exposed a GUC that set a minimum version, rather than
 calling out specific acceptable protocols, it might be less risky.

Not necessarily.  Someone might find a weakness in TLS 1.1 which is not
present in 1.0 because it involves a specific algorithm or mode that 1.0
does not support.

DES
-- 
Dag-Erling Smørgrav - d...@des.no


-- 
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] [PATCH] add ssl_protocols configuration option

[Dag-Erling Smørgrav]

Magnus Hagander mag...@hagander.net writes:
 Yes, it does that. Though it only does it on 9.4,but with the facts we
 know now, what 9.4+ does is perfectly safe.

Agreed.

DES
-- 
Dag-Erling Smørgrav - d...@des.no


-- 
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] [PATCH] add ssl_protocols configuration option

[Dag-Erling Smørgrav]

Martijn van Oosterhout klep...@svana.org writes:
 Dag-Erling Smørgrav d...@des.no writes:
  If I understand correctly, imaps has been shown to be vulnerable as
  well, so I wouldn't be so sure.
 Reference?

Sorry, no reference.  I was told that Thunderbird was vulnerable to
POODLE when talking imaps.

 Since you can already specify the cipher list, couldn't you just add
 -SSLv3 to the cipher list and be done?

I didn't want to change the existing behavior; all I wanted was to give
users a way to do so if they wish.

DES
-- 
Dag-Erling Smørgrav - d...@des.no


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] [PATCH] add ssl_protocols configuration option

[Dag-Erling Smørgrav]

 != ':'; ++q)
+			/* nothing */ ;
+		if (*p == '-' || *p == '+')
+			action = *p++;
+		else
+			action = '=';
+		if (str_is_token(p, ALL, q - p))
+			current = SSL_PROTO_ALL;
+		else if (str_is_token(p, NONE, q - p))
+			current = SSL_PROTO_NONE;
+		else if (str_is_token(p, SSL_TXT_SSLV2, q - p))
+			current = SSL_PROTO_SSLv2;
+		else if (str_is_token(p, SSL_TXT_SSLV3, q - p))
+			current = SSL_PROTO_SSLv3;
+		else if (str_is_token(p, SSL, q - p))
+			current = SSL_PROTO_SSL;
+		else if (str_is_token(p, SSL_TXT_TLSV1, q - p))
+			current = SSL_PROTO_TLSv1;
+#ifdef SSL_OP_NO_TLSv1_1
+		else if (str_is_token(p, SSL_TXT_TLSV1_1, q - p))
+			current = SSL_PROTO_TLSv1_1;
+#endif
+#ifdef SSL_OP_NO_TLSv1_2
+		else if (str_is_token(p, SSL_TXT_TLSV1_2, q - p))
+			current = SSL_PROTO_TLSv1_2;
+#endif
+		else if (str_is_token(p, TLS, q - p))
+			current = SSL_PROTO_TLS;
+		else
+			elog(FATAL, invalid SSL protocol list);
+		switch (action) {
+		case '+':
+			result |= current;
+			break;
+		case '-':
+			result = ~current;
+			break;
+		default:
+			result = current;
+			break;
+		}
+	}
+	/* forcibly disallow SSLv2 */
+	if (result  SSL_PROTO_SSLv2) {
+		elog(WARNING, removing SSLv2 from SSL protocol list);
+		result = ~SSL_PROTO_SSLv2;
+	}
+	/* check the result */
+	if (result == 0)
+		elog(FATAL, could not set the protocol list (no valid protocols available));
+	elog(DEBUG2, enabling SSL protocols: %lx, result);
+	/* return the inverse */
+	return (SSL_PROTO_ALL  ~result);
+}
+
 #endif   /* USE_SSL */
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 718de95..563267e 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -130,6 +130,7 @@ extern bool optimize_bounded_sort;
 #endif
 
 #ifdef USE_SSL
+extern char *SSLProtocols;
 extern char *SSLCipherSuites;
 #endif
 
@@ -2638,6 +2639,21 @@ static struct config_string ConfigureNamesString[] =
 
 #ifdef USE_SSL
 	{
+		{ssl_protocols, PGC_POSTMASTER, CONN_AUTH_SECURITY,
+			gettext_noop(Sets the list of allowed SSL protocols.),
+			NULL,
+			GUC_SUPERUSER_ONLY
+		},
+		SSLProtocols,
+#ifdef USE_SSL
+		ALL:-SSLv2,
+#else
+		none,
+#endif
+		NULL, NULL, NULL
+	},
+
+	{
 		{ssl_ciphers, PGC_POSTMASTER, CONN_AUTH_SECURITY,
 			gettext_noop(Sets the list of allowed SSL ciphers.),
 			NULL,
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 155af1c..b2e1023 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -78,6 +78,7 @@
 
 #authentication_timeout = 1min		# 1s-600s
 #ssl = off# (change requires restart)
+#ssl_protocols = 'ALL:-SSLv2'		# allowed SSL protocols
 #ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH'	# allowed SSL ciphers
 	# (change requires restart)
 #ssl_renegotiation_limit = 512MB	# amount of data between renegotiations

DES
-- 
Dag-Erling Smørgrav - d...@des.no

-- 
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] [PATCH] add ssl_protocols configuration option

[Dag-Erling Smørgrav]

Michael Paquier michael.paqu...@gmail.com writes:
 Please note that new features can only be added to the version
 currently in development, aka 9.5.

I understand this policy.  However, this new feature a) has absolutely
no impact unless the admin makes a conscious decision to use it and b)
will make life much easier for everyone if a POODLE-like vulnerability
is discovered in TLS.

 You should as well register your patch to the current commit fest, I
 think you are still in time:
 https://commitfest.postgresql.org/action/commitfest_view?id=24

Thanks for reminding me.

DES
-- 
Dag-Erling Smørgrav - d...@des.no


-- 
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] [patch] build issues on Win32

[Dag-Erling Smørgrav]

Tom Lane t...@sss.pgh.pa.us writes:
 Any such platform would already be contending with plpgsql not working,
 encoding conversion not working, etc etc.  It's barely conceivable that
 a client-only installation would be useful.

Uh, I don't understand why it's so hard to conceive that someone might
need the client library to access a database running on one machine from
an application running on another.

 AFAICT the only case where anyone tries to do this is they have a
 personal preference to avoid shared libraries, for generally-pretty-
 darn-dubious reasons.

Well, statically linked binaries are much easier to distribute, for one;
and there are platforms where shared libraries simply do not exist, or
where the entire system runs in a single namespace.  Netware is still
alive and kicking.

DES
-- 
Dag-Erling Smørgrav - d...@des.no

-- 
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] [patch] build issues on Win32

[Dag-Erling Smørgrav]

Greg Stark gsst...@mit.edu writes:
 I would be sad about this. It seems likely there are platforms where
 it's important. But I'm not really about to spend the effort to fix it
 up myself and I agree it wouldn't be worth hacking the source to get
 it to work. I'm a bit puzzled why the symbol conflicts occur only with
 static linking though -- it seems like static linking would give more
 opportunity to isolate symbols than dynamic linking, not less. Perhaps
 our static linking rules are broken?

A dynamic library is a single entity with a certain degree of isolation.
You can hide symbols so they are only visible within that library.

A static library is basically just a tarball of individual relocatable
objects.

The GNU toolchain (and probably others too) allows you to combine
several relocatable objects together into one and hide some of the
symbols, as if the entire thing had been a single C file with some of
the functions declared as static.  However, when you do that, you lose
an important advantage of static libraries: the ability to link only
what you need.

DES
-- 
Dag-Erling Smørgrav - d...@des.no

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] [patch] build issues on Win32

[Dag-Erling Smørgrav]

I've run across a couple of stumbling blocks when building on Win32
(specifically, XP + MinGW):

 1. PostgreSQL's private versions of inet_aton etc. can conflict with
similar functions in other libraries (in my case, PostgreSQL's
inet_aton conflicts with libavformat's).

 2. On Win32, PostgreSQL uses the SSPI authentication interface.  This
interface is implemented in secur32.dll, which needs to be added to
the linker command line.  This is apparently only an issue when
building a static libpq.

 3. src/template/win32 sets LDFLAGS unconditionally, overriding anything
the user may have specified in the environment or on the command
line (such as -L/path/to/my/lib/dir).

The attached patch addresses these issues by:

 1. #defining the function names as appropriate

 2. adding -lsecur32 to LIBS in src/Makefile.global.in when PORTNAME is
win32.

 3. adding ${LDFLAGS} at the front of the LDFLAGS redefinition in
src/template/win32.

The patch was developed and tested on 8.3.9, because that's what my
customer uses.  I have verified that it applies cleanly (albeit with
offsets) to 8.4.2.

BTW, IWBNI there were a quick and easy way to build and install only
libpq.  I use this sequence of commands (after configure):

$ make -C src/port all
$ make -C src/backend utils/fmgroids.h
$ make -C src/backend ../../src/include/utils/fmgroids.h
$ make -C src/include all install
$ make -C src/interfaces/libpq all install
$ make -C src/bin/pg_config all install

DES
-- 
Dag-Erling Smørgrav - d...@des.no

--- src/include/port.h.orig	2009-11-14 16:39:41.0 +0100
+++ src/include/port.h	2010-03-10 13:17:27.0 +0100
@@ -337,6 +337,7 @@
  * When necessary, these routines are provided by files in src/port/.
  */
 #ifndef HAVE_CRYPT
+#define crypt pq_crypt
 extern char *crypt(const char *key, const char *setting);
 #endif
 
@@ -351,44 +352,60 @@
 #endif
 
 #ifndef HAVE_GETOPT
+#define getopt pq_getopt
 extern int	getopt(int nargc, char *const * nargv, const char *ostr);
 #endif
 
 #ifndef HAVE_ISINF
+#define isinf pq_isinf
 extern int	isinf(double x);
 #endif
 
 #ifndef HAVE_RINT
+#define rint pq_rint
 extern double rint(double x);
 #endif
 
 #ifndef HAVE_INET_ATON
 #include netinet/in.h
 #include arpa/inet.h
+#define inet_aton pq_inet_aton
 extern int	inet_aton(const char *cp, struct in_addr * addr);
 #endif
 
 #ifndef HAVE_STRDUP
+#define strdup pq_strdup
 extern char *strdup(const char *str);
 #endif
 
+#ifndef HAVE_STRLCAT
+#define strlcat pq_strlcat
+#endif
+
 #if !HAVE_DECL_STRLCAT
 extern size_t strlcat(char *dst, const char *src, size_t siz);
 #endif
 
+#ifndef HAVE_STRLCPY
+#define strlcpy pq_strlcpy
+#endif
+
 #if !HAVE_DECL_STRLCPY
 extern size_t strlcpy(char *dst, const char *src, size_t siz);
 #endif
 
 #if !defined(HAVE_RANDOM)  !defined(__BORLANDC__)
+#define random pq_random
 extern long random(void);
 #endif
 
 #ifndef HAVE_UNSETENV
+#define unsetenv pq_unsetenv
 extern void unsetenv(const char *name);
 #endif
 
 #ifndef HAVE_SRANDOM
+#define srandom pq_srandom
 extern void srandom(unsigned int seed);
 #endif
 
--- src/Makefile.global.in.orig	2007-11-13 01:13:19.0 +0100
+++ src/Makefile.global.in	2010-03-10 13:42:04.0 +0100
@@ -435,9 +435,10 @@
 endif
 
 # to make ws2_32.lib the last library, and always link with shfolder,
-# so SHGetFolderName isn't picked up from shell32.dll
+# so SHGetFolderName isn't picked up from shell32.dll.  Also link
+# with secur32 for SSPI.
 ifeq ($(PORTNAME),win32)
-LIBS += -lws2_32 -lshfolder
+LIBS += -lws2_32 -lshfolder -lsecur32
 endif
 
 # Not really standard libc functions, used by the backend.
--- src/template/win32.orig	2004-11-08 06:23:26.0 +0100
+++ src/template/win32	2010-03-10 13:40:59.0 +0100
@@ -1,4 +1,4 @@
 # This is required to link pg_dump because it finds pg_toupper() in
 # libpq and pgport
-LDFLAGS=-Wl,--allow-multiple-definition
+LDFLAGS=${LDFLAGS} -Wl,--allow-multiple-definition
 

-- 
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] [patch] build issues on Win32

[Dag-Erling Smørgrav]

Tom Lane t...@sss.pgh.pa.us writes:
 Dag-Erling Smørgrav d...@des.no writes:
   1. PostgreSQL's private versions of inet_aton etc. can conflict with
  similar functions in other libraries (in my case, PostgreSQL's
  inet_aton conflicts with libavformat's).
 So what?  We don't link with those libraries.

Your users might need to link with both.  I'm working on an application
that generates animations (specifically, animated weather forecasts)
based on data retrieved from a PostgreSQL database.

 The proposed #defines seem like a completely bad idea, especially
 since as-presented they would affect every platform not only yours.

Yes.  That's the idea.  This is a common idiom - the canonical way, if
you will, of solving this problem, which is not exclusive to PostgreSQL.
There are even cases in which you have no other choice, e.g. when the
function you want to use is available but does not work properly or does
not implement a particular feature that you need.

 We don't need the maintenance/debugging headaches of routines that
 don't have the names they appear to have.

Honestly, I don't see the problem.

   ifeq ($(PORTNAME),win32)
  -LIBS += -lws2_32 -lshfolder
  +LIBS += -lws2_32 -lshfolder -lsecur32
   endif
 Surely this bit would need to be conditional on whether libsecur32
 is available?

It's just as much part of Win32 as ws2_32 (winsock).

DES
-- 
Dag-Erling Smørgrav - d...@des.no

-- 
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] [patch] build issues on Win32

[Dag-Erling Smørgrav]

Magnus Hagander mag...@hagander.net writes:
  Dag-Erling Smørgrav d...@des.no writes:
  Your users might need to link with both.  I'm working on an
  application that generates animations (specifically, animated
  weather forecasts) based on data retrieved from a PostgreSQL
  database.
 This shows up only with static links of libpq, correct?

Yes.

 But the fix seems wrong. If you are using a static libpq, the library
 should be added whenever you link that library into your client
 application. Not for every single EXE and DLL that postgres produces.

Without this patch, pg_ctl fails to build...  and I don't see the logic
of including ws2_32 in LIBS but not secur32.  remember that LIBS
directly affects what pg_config reports, so if you don't add secur32 to
LIBS, consumers (applications) don't know that they need it.

DES
-- 
Dag-Erling Smørgrav - d...@des.no

-- 
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] [patch] build issues on Win32

[Dag-Erling Smørgrav]

Tom Lane t...@sss.pgh.pa.us writes:
 We don't support linking the backend into other applications.

libpq uses this as well.

 If you're complaining about libpq, the right thing for that is to use
 a platform that can suppress non-exported symbols from a shared
 library.  Maybe what we need to do is teach the mingw build path how
 to respect the exports list for libpq?

If that works, I'm all for it.  I have no idea how to do it, though.

DES
-- 
Dag-Erling Smørgrav - d...@des.no

-- 
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] [patch] build issues on Win32

[Dag-Erling Smørgrav]

Tom Lane t...@sss.pgh.pa.us writes:
 Dag-Erling Smørgrav d...@des.no writes:
  Without this patch, pg_ctl fails to build...
 It builds for everybody else (and we do have multiple mingw machines in
 the buildfarm, so it's not like this doesn't get tested).  I think there
 is some other factor involved here, and you need to identify what that
 is.

--disable-shared, as previously mentioned.

DES
-- 
Dag-Erling Smørgrav - d...@des.no

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Regression tests

[Dag-Erling Smørgrav]

Are there any regression tests or unit tests beyond 'make check', or
possibly benchmarks which not only measure performance but also verify
that the results are correct?  I have patches which I want to test
under high load from multiple concurrent clients, so 'make check'
isn't enough.  Google has tons of hits for articles and RDBMS reviews
that mention SuperSmack, but no hits for the actual software.

DES
-- 
Dag-Erling Smørgrav - [EMAIL PROTECTED]

---(end of broadcast)---
TIP 8: explain analyze is your friend

[HACKERS] SQL2003 GENERATED ... AS ... syntax

[Dag-Erling Smørgrav]

As previously mentioned, I'm working on implementing [subject].  I
think I've mostly worked it out, but I'm having trouble with the
GENERATED ALWAYS case.

My changes (see attached patch) can be summarized as follows:

 - in backend/utils/adt/misc.c:

   - added a force_default_value() function which takes a string
 argument (the name of the column to force to default) and
 currently does nothing.

 - in backend/parser/gram.y:

   - when GENERATED ... AS ... is encountered in a column definition,
 it adds a node of the new T_Generated type to the constraint
 list.  This node contains a bool that differentiates between BY
 DEFAULT and ALWAYS, and a pointer to a CreateSeqStmt (for
 IDENTITY '(' OptSeqList ')') or a List constructed by the a_expr
 production (for '(' a_expr ')')

 - in backend/parser/analyze.c:

   - factored out the code from transformColumnDefinition() that
 creates the sequence and the DEFAULT constraint into a separate
 CreateSerialColumn() function which takes as one of its arguments
 is a List of sequence options.  The SERIAL code passes in a NIL
 list, while the GENERATED AS IDENTITY code passes in the option
 list from the CreateSeqStmt.

   - added a CreateAlwaysDefaultColumn() function which synthesizes a
 CreateTrigStmt equivalent to CREATE TRIGGER foo BEFORE INSERT ON
 bar FOR EACH ROW EXECUTE PROCEDURE force_default_value ('baz')
 and adds it to the work list.  This function is called by
 transformColumnDefinition() if a Generated node with always set
 to true is encountered.

Now I must be doing something wrong in CreateAlwaysDefaultColumn(),
because the CreateTrigStmt fails to execute:

| des=# create table test ( id int generated always as identity ( minvalue 1000 ), 
word text );
| NOTICE:  CREATE TABLE will create implicit sequence test_id_seq for SERIAL column 
test.id
| NOTICE:  CREATE TABLE will create implicit trigger test_id_always_default for 
column test.id
| ERROR:  relation public.test does not exist

GENERATED BY DEFAULT AS IDENTITY works fine though, so I must have
done *something* right:

| des=# create table test ( id int generated by default as identity ( minvalue 1000 ), 
word text );
| NOTICE:  CREATE TABLE will create implicit sequence test_id_seq for SERIAL column 
test.id
| CREATE TABLE
| des=# select sequence_name, last_value, min_value, max_value from test_id_seq;
|  sequence_name | last_value | min_value |  max_value
| ---++---+-
|  test_id_seq   |   1000 |  1000 | 9223372036854775807
| (1 row)
| 

On the other hand, I seem to have botched the definition of
force_default_value() in include/catalog/pg_proc.h, because adding the
trigger manually doesn't seem to work either:

| des=# \df force_default_value
|  List of functions
|  Result data type |   Schema   |Name | Argument data types
| --++-+-
|  trigger| pg_catalog | force_default_value | text
| (1 row)
| 
| des=# create trigger test_id_always_default before insert on test for each row 
execute procedure force_default_value ('id');
| ERROR:  function force_default_value() does not exist

Any suggestions?

DES
-- 
Dag-Erling Smørgrav - [EMAIL PROTECTED]

Index: src/backend/parser/analyze.c
===
RCS file: /home/pqcvs/pgsql-server/src/backend/parser/analyze.c,v
retrieving revision 1.283
diff -u -u -r1.283 analyze.c
--- src/backend/parser/analyze.c	1 Aug 2003 00:15:22 -	1.283
+++ src/backend/parser/analyze.c	3 Aug 2003 11:39:12 -
@@ -917,13 +917,116 @@
 }
 
 static void
+CreateSerialColumn(ParseState *pstate, CreateStmtContext *cxt,
+   ColumnDef *column, List *seqopts)
+{
+	Constraint *constraint;
+	char	   *sname;
+	char	   *snamespace;
+	char	   *qstring;
+	A_Const*snamenode;
+	FuncCall   *funccallnode;
+	CreateSeqStmt *seqstmt;
+
+	/*
+	 * Determine name and namespace to use for the sequence.
+	 */
+	sname = makeObjectName(cxt-relation-relname, column-colname, seq);
+	snamespace = get_namespace_name(RangeVarGetCreationNamespace(cxt-relation));
+
+	ereport(NOTICE,
+			(errmsg(%s will create implicit sequence \%s\ for SERIAL column \%s.%s\,
+	cxt-stmtType, sname,
+	cxt-relation-relname, column-colname)));
+
+	/*
+	 * Build a CREATE SEQUENCE command to create the sequence object,
+	 * and add it to the list of things to be done before this
+	 * CREATE/ALTER TABLE.
+	 */
+	seqstmt = makeNode(CreateSeqStmt);
+	seqstmt-sequence = makeRangeVar(snamespace, sname);
+	seqstmt-options = seqopts;
+
+	cxt-blist = lappend(cxt-blist, seqstmt);
+
+	/*
+	 * Mark the ColumnDef so that during execution, an appropriate
+	 * dependency will be added from the sequence to the column.
+	 */
+	column-support = makeRangeVar(snamespace, sname);
+
+	/*
+	 * Create appropriate constraints

Re: [HACKERS] SQL2003 GENERATED ... AS ... syntax

[Dag-Erling Smørgrav]

Rod Taylor [EMAIL PROTECTED] writes:
 I think a longer term solution would be to add a type to pg_attrdef and
 a bool for ALWAYS. (Tom?)

I thought about it, but won't that change the on-disk format?  Since
minor version upgrades aren't supposed to require a dump / restore,
and I understand 7.4 is already in feature freeze, the earliest
opportunity for something like this would be 7.5.

DES
-- 
Dag-Erling Smørgrav - [EMAIL PROTECTED]

---(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] AUTO_INCREMENT patch

[Dag-Erling Smørgrav]

Rod Taylor [EMAIL PROTECTED] writes:
 Yeah.. JBoss is very annoying in this regard. A temporary solution seems
 to be to use BEFORE triggers to force the sequence to be used for the
 default value.  You could also do this with an INSTEAD rule (something
 like the below):

 CREATE OR REPLACE RULE rulename AS ON INSERT
   TO tablename
   DO INSTEAD
INSERT INTO tablename
  ( id, col1, ...)
   VALUES ( DEFAULT, NEW.col1, ...);

That's a good workaround for 3.2.1.  As regards the upcoming 3.2.2, I
just found out that it has PostgreSQL-specific code to handle this,
though it's an incomplete fix.  To summarize:

 - add entity-commandpostgresql-fetch-seq/entity-command to each
   affected entity (or the defaults section) in jbosscmp-jdbc.xml;
   this resolves the failed to insert null problem and also makes
   sure JBoss knows the id of the newly created row.

 - apply my auto_increment patch

 - replace the bogus auto-increment template in the PostgreSQL and
   PostgreSQL 7.2 mappings in standardjbosscmp-jdbc.xml with the
   following:

  auto-increment-template?1 AUTO_INCREMENT/auto-increment-template

   the current template (?1) works if you set the corresponding
   cmp-field's sql-type to SERIAL - but if you define a relation
   that involves that field, the other endpoint will also be defined
   as SERIAL!  Obviously that's not a recipe for success.

 AUTO_INCREMENT is non-standard (MySQL only?), however the SQL200X
 proposals do have support for the more common IDENTITY syntax which can
 accomplish the same job as well as many others. (PostgreSQL does NOT
 have the general identity implementation yet)

the IDENTITY syntax is very similar to AUTO_INCREMENT, in fact you can
apply s/auto_increment/identity/ to my patch and have a useful subset
of it :) JBoss already knows about IDENTITY, since Hypersonic SQL and
MS SQL both support it.

(interestingly, JBoss doesn't seem to know that DB/2 also supports it)

 What you're looking for is the ability to force the column to use the
 IDENTITY even when the client provides a specific value:

 CREATE TABLE test(col integer GENERATED ALWAYS AS IDENTITY);

as mentioned above, that's no longer a problem with JBoss 3.2.2.

 See sections 10.22, 10.23, 11.3, and 11.4 of the SQL200X working draft
 for full details.

 ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-02-Foundation-2002-01.pdf

I believe this is more up to date:

ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FDIS/4FDIS1-02-Foundation-2003.pdf

DES
-- 
Dag-Erling Smørgrav - [EMAIL PROTECTED]

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] AUTO_INCREMENT patch

[Dag-Erling Smørgrav]

Rod Taylor [EMAIL PROTECTED] writes:
 CREATE OR REPLACE RULE rulename AS ON INSERT
   TO tablename
   DO INSTEAD
INSERT INTO tablename
  ( id, col1, ...)
   VALUES ( DEFAULT, NEW.col1, ...);


I now have a patch that adds support for the GENERATED ... AS ...
syntax and implements the GENERATED BY DEFAULT AS IDENTITY case.
I'm trying to figure out how to implement the other two cases
(GENERATED ALWAYS AS IDENTITY and GENERATED ALWAYS AS ( expr )).
I thought I'd try your trick:

des=# create table test ( id serial, word text );
NOTICE:  CREATE TABLE will create implicit sequence test_id_seq for SERIAL column 
test.id
CREATE TABLE
des=# create rule test_id_generate as
des-# on insert to test do instead
des-# insert into test ( id, word ) values ( default, new.word );
CREATE RULE
des=# insert into test ( id, word ) values ( 42, 'hello' );
ERROR:  infinite recursion detected in rules for relation test
des=# insert into test ( word ) values ( 'hello' );
ERROR:  infinite recursion detected in rules for relation test

any suggestions?

DES
-- 
Dag-Erling Smørgrav - [EMAIL PROTECTED]

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]