Re: [HACKERS] postgres 8.3.8 and Solaris 10_x86 64 bit problems?
u235sentinel píše v ne 18. 10. 2009 v 17:50 -0600: Are you sure about this? When I try to build and don't have openssl in the lib/include path it claims it needs it. As I'm building 64 bit I can now build postgres in 64 bit with openssl 98k just fine. However when I run it I'm getting the same error message. If you want to link against to builtin OpenSSL you need following setup: ./configure ... --with-openssl --with-includes=/usr/sfw/include --with-libs=/usr/lib/amd64:/usr/sfw/lib/amd64 and important is: LD_OPTIONS=-R/usr/sfw/lib/amd64 -L/usr/sfw/lib/amd64 Or if you don't need own compilation, you can use built-in PostgreSQL 8.3. It is located in /usr/postgres/8.3/bin or /usr/postgres/8.3/bin/64. See man postgres_83 for details. Also you need to apply last patch 138827-05: http://sunsolve.sun.com/search/document.do?assetkey=1-21-138827-05-1 Or if you still needs own compilation try to compile openssl 98k with Sun Studio. Or if you cannot compile it with Sun Studio, you can try -mimpure-text gcc switch to compile OpenSSL. It is workaround for some kind of linking issues. Let me know it it helps Zdenek I'm curious if this is a lost hope. My boss is recommending we flatten the Sun box and install redhat linux (which I'm fine with). I'd rather not as threading in Solaris is better. Thoughts? thanks Zdenek Kotala wrote: You can look on http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=ghost_mothdt=2009-10-07%2021:06:00 How it is built. You also does not needed own version of Openssl. All security fixes are backported. It is located in /usr/sfw/lib or /usr/sfw/lib/64 Sometimes are problem with gcc and solaris linker. IIRC, I had problem with PLPerl compilation. Zdenek Dne 8.10.09 03:48, u235sentinel napsal(a): So I compiled postgres with Solaris 10 and have problems running it. # ./pg_ctl ld.so.1: pg_ctl: fatal: relocation error: R_AMD64_32: file /usr/local/postgres64/lib/libpq.so.5: symbol (unknown): value 0xfd7fff1cf210 does not fit Killed # ldd pg_ctl libpq.so.5 =/usr/local/postgres64/lib/libpq.so.5 libm.so.2 = /usr/lib/64/libm.so.2 libxml2.so.2 = /usr/lib/64/libxml2.so.2 libz.so.1 = /usr/lib/64/libz.so.1 libreadline.so.6 = /usr/local/lib/libreadline.so.6 libcurses.so.1 =/usr/lib/64/libcurses.so.1 librt.so.1 =/usr/lib/64/librt.so.1 libsocket.so.1 =/usr/lib/64/libsocket.so.1 libc.so.1 = /usr/lib/64/libc.so.1 libpthread.so.1 = /usr/lib/64/libpthread.so.1 libnsl.so.1 = /lib/64/libnsl.so.1 libgcc_s.so.1 = /usr/sfw/lib/amd64/libgcc_s.so.1 libaio.so.1 = /lib/64/libaio.so.1 libmd.so.1 =/lib/64/libmd.so.1 libmp.so.2 =/lib/64/libmp.so.2 libscf.so.1 = /lib/64/libscf.so.1 libdoor.so.1 = /lib/64/libdoor.so.1 libuutil.so.1 = /lib/64/libuutil.so.1 libgen.so.1 = /lib/64/libgen.so.1 # file /usr/local/postgres64/lib/libpq.so.5 /usr/local/postgres64/lib/libpq.so.5: ELF 64-bit LSB dynamic lib AMD64 Version 1 [SSE CMOV], dynamically linked, not stripped What am I missing??? Here's my environment. Solaris 10 x86_64 with postgres 8.3.8 and openssl 98k using gcc version 3.4.3 (csl-sol210-3_4-branch+sol_rpath) , sunstudio12.1 and GNU Make 3.80 I've even monkied with LD_LIBRARY_PATH but getting the same issues. Seems when I don't compile in openssl everything is fine. Thanks! -- 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] postgres 8.3.8 and Solaris 10_x86 64 bit problems?
Andrew Chernow píše v ne 18. 10. 2009 v 21:09 -0400: I'm curious if this is a lost hope. My boss is recommending we flatten the Sun box and install redhat linux (which I'm fine with). I'd rather not as threading in Solaris is better. Maybe solaris threads were better 10-15 years ago, but I'm not convinced that is still the case. Any data supporting that argument, solaris 10 threads vs. linux 2.6.11+ kernel (p)threads? I can point on this article: http://tweakers.net/reviews/649/all/database-test-sun-ultrasparc-t1-vs-punt-amd-opteron.html Zdenek -- 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] Reworks for Access Control facilities (r2363)
KaiGai Kohei wrote: When we create a new object, we can provide an explicit security context to be assigned on the new object, instead of the default one. To get started, do we really need that feature? It would make for a significantly smaller patch if there was no explicit security labels on objects. On the other hand, the default PG model allows to bypass checks on certain objects. For example, column-level privileges are only checked when a user does not have enough permissions on the target table. If SELECT a,b FROM t is given, pg_attribute_aclcheck() may not invoked when user has needed privileges on the table t. Hmm, I see. Yes, it does seem like we'd need to change such permission checks to accommodate both models. I'm not clear why we need to rework the permission checks here. DAC and MAC perform orthogonally and independently. DAC allows to override column-level privileges by table-level privileges according to the default PG's model. It seems to me fine. On the other hand, MAC checks both of permissions. It is also fine. I meant we need to refactor the code doing the permission checks. The existing checks are doing the right thing for DAC, but as you point out, if the MAC checks are within pg_*_aclcheck() functions, pg_attribute_aclcheck() needs to be called even if you have privilege on the table. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Rejecting weak passwords
Bruce Momjian wrote: Great, added to TODO: Allow server-side enforcement of password policies Password checks might include password complexity or non-reuse of passwords. This facility will require the client to send the password to the server in plain-text, so SSL and 'password' authentication is necessary to use this features. I don't get why you need 'password' authentication for that. The point where the password should be checked is not when the user uses it to logon, but when he or she changes it. So in my opinion that should be: This facility will require to send new and changed password to the server in plain-text, so it will require SSL, and the use of encrypted passwords in CREATE/ALTER ROLE will have to be disabled. Yours, Laurenz Albe -- 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] Reworks for Access Control facilities (r2363)
Heikki Linnakangas wrote: KaiGai Kohei wrote: When we create a new object, we can provide an explicit security context to be assigned on the new object, instead of the default one. To get started, do we really need that feature? It would make for a significantly smaller patch if there was no explicit security labels on objects. The importance of the feature is relatively minor than MAC itself. So, I can agree to omit code corresponding to statement support from the first patch. (IIRC, about 300-400 lines can be reduced.) But it will be necessary feature at the next step, because DBA cannot create a special purpose table without statement support. For example, if security policy allows DBA to create read-writable table (in default) and read-only table. He cannot set up read-only table without explicit security label support. On the other hand, the default PG model allows to bypass checks on certain objects. For example, column-level privileges are only checked when a user does not have enough permissions on the target table. If SELECT a,b FROM t is given, pg_attribute_aclcheck() may not invoked when user has needed privileges on the table t. Hmm, I see. Yes, it does seem like we'd need to change such permission checks to accommodate both models. I'm not clear why we need to rework the permission checks here. DAC and MAC perform orthogonally and independently. DAC allows to override column-level privileges by table-level privileges according to the default PG's model. It seems to me fine. On the other hand, MAC checks both of permissions. It is also fine. I meant we need to refactor the code doing the permission checks. The existing checks are doing the right thing for DAC, but as you point out, if the MAC checks are within pg_*_aclcheck() functions, pg_attribute_aclcheck() needs to be called even if you have privilege on the table. I think we already learned refactoring DAC checks need widespread code changes and pushes a burden to reviewers. In this case, I think the point just after invocation of ExecCheckRTEPerms() in ExecCheckRTPerms() is the best point to put SE-PgSQL's checks. Needless to say, its specification should be clearly documented. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com -- 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] Application name patch - v2
On Fri, 2009-10-16 at 12:58 +0100, Dave Page wrote: I think that covers all the suggestions discussed over the last couple of days, with the exception of the rejection of \n and similar characters which I'm still not entirely convinced is worth the effort. Any other opinions on that? Anything else that should be added/changed? So this would effectively allow any minimally authorized user to write whatever they want into the log file whenever they want? Doesn't sound very safe to me. -- 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] Application name patch - v2
On Mon, Oct 19, 2009 at 8:37 AM, Peter Eisentraut pete...@gmx.net wrote: On Fri, 2009-10-16 at 12:58 +0100, Dave Page wrote: I think that covers all the suggestions discussed over the last couple of days, with the exception of the rejection of \n and similar characters which I'm still not entirely convinced is worth the effort. Any other opinions on that? Anything else that should be added/changed? So this would effectively allow any minimally authorized user to write whatever they want into the log file whenever they want? Doesn't sound very safe to me. A user can do that anyway if query logging is turned on, but anyway, what would you suggest - accept a-zA-Z0-9 and a few other choice characters only, or just reject a handful (and if so, what)? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Application name patch - v2
2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 8:37 AM, Peter Eisentraut pete...@gmx.net wrote: On Fri, 2009-10-16 at 12:58 +0100, Dave Page wrote: I think that covers all the suggestions discussed over the last couple of days, with the exception of the rejection of \n and similar characters which I'm still not entirely convinced is worth the effort. Any other opinions on that? Anything else that should be added/changed? So this would effectively allow any minimally authorized user to write whatever they want into the log file whenever they want? Doesn't sound very safe to me. A user can do that anyway if query logging is turned on, but anyway, what would you suggest - accept a-zA-Z0-9 and a few other choice characters only, or just reject a handful (and if so, what)? I dislike write access to app name guc for user too. It's not safe. Maybe only super user can do it? Regards Pavel Stehule -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Application name patch - v2
On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I dislike write access to app name guc for user too. It's not safe. Maybe only super user can do it? That'll render it pretty useless, as most applications wouldn't then be able to set/reset it when it makes sense to do so. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Application name patch - v2
Pavel Stehule wrote: 2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 8:37 AM, Peter Eisentraut pete...@gmx.net wrote: So this would effectively allow any minimally authorized user to write whatever they want into the log file whenever they want? Doesn't sound very safe to me. A user can do that anyway if query logging is turned on, but anyway, what would you suggest - accept a-zA-Z0-9 and a few other choice characters only, or just reject a handful (and if so, what)? I dislike write access to app name guc for user too. It's not safe. Maybe only super user can do it? The application name is provided by the client. In the server, we have no control over what the client put there. We can limit it to certain characters, but other than that we just have to take it at face value. Or are you saying that it should not be possible for the client to change the value after connecting? That limits the usefulness with connection pools. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Application name patch - v2
2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I dislike write access to app name guc for user too. It's not safe. Maybe only super user can do it? That'll render it pretty useless, as most applications wouldn't then be able to set/reset it when it makes sense to do so. But application can do it simply via connection string, no? Mostly applications has connection string in configuration, so I don't see problem there. And if I would to allow access, then I could to wrap setting to security definer function. I see this as security hole. It allows special SQL injection. Regards Pavel Stehule -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Application name patch - v2
2009/10/19 Heikki Linnakangas heikki.linnakan...@enterprisedb.com: Pavel Stehule wrote: 2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 8:37 AM, Peter Eisentraut pete...@gmx.net wrote: So this would effectively allow any minimally authorized user to write whatever they want into the log file whenever they want? Doesn't sound very safe to me. A user can do that anyway if query logging is turned on, but anyway, what would you suggest - accept a-zA-Z0-9 and a few other choice characters only, or just reject a handful (and if so, what)? I dislike write access to app name guc for user too. It's not safe. Maybe only super user can do it? The application name is provided by the client. In the server, we have no control over what the client put there. We can limit it to certain characters, but other than that we just have to take it at face value. Or are you saying that it should not be possible for the client to change the value after connecting? That limits the usefulness with connection pools. What I know, connections from connection pool without reset are shared by one application. But I am not against some possibility to change this value from application. I am against to possibility an change by normal user. When we allow it, then this value has not any wight, because any broken appliaction (via SQL injection) can change it. Regards Pavel Stehule -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Application name patch - v2
On Mon, Oct 19, 2009 at 9:23 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I dislike write access to app name guc for user too. It's not safe. Maybe only super user can do it? That'll render it pretty useless, as most applications wouldn't then be able to set/reset it when it makes sense to do so. But application can do it simply via connection string, no? Mostly applications has connection string in configuration, so I don't see problem there. And if I would to allow access, then I could to wrap setting to security definer function. It will prevent an application changing the value before running a long operation which may warrant special identification. It will also prevent applications changing the setting if you're running through a pooler. I see this as security hole. It allows special SQL injection. How so? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Application name patch - v2
2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 9:23 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I dislike write access to app name guc for user too. It's not safe. Maybe only super user can do it? That'll render it pretty useless, as most applications wouldn't then be able to set/reset it when it makes sense to do so. But application can do it simply via connection string, no? Mostly applications has connection string in configuration, so I don't see problem there. And if I would to allow access, then I could to wrap setting to security definer function. It will prevent an application changing the value before running a long operation which may warrant special identification. It will also prevent applications changing the setting if you're running through a pooler. Then we have to divide this value to two independent values like application_name and application_state. I see this as security hole. It allows special SQL injection. How so? You change identity. If any application is vulnerable to SQL injection, then this value is nice goal. Pavel -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Application name patch - v2
Pavel Stehule wrote: 2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I dislike write access to app name guc for user too. It's not safe. Maybe only super user can do it? That'll render it pretty useless, as most applications wouldn't then be able to set/reset it when it makes sense to do so. But application can do it simply via connection string, no? Mostly applications has connection string in configuration, so I don't see problem there. And if I would to allow access, then I could to wrap setting to security definer function. I see this as security hole. It allows special SQL injection. How is it any more a security hole than any other setting that the user can alter with an arbitrary string value (e.g. custom options)? cheers andrew -- 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] Application name patch - v2
On Mon, Oct 19, 2009 at 9:36 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Then we have to divide this value to two independent values like application_name and application_state. How does that make any difference? That just means we have two values, at least one of which is still userset, and means an additional field in the logs and stats view etc. I see this as security hole. It allows special SQL injection. How so? You change identity. If any application is vulnerable to SQL injection, then this value is nice goal. Are you saying that if your application is vulnerable, then the user may be able to masquerade as something else? If that's the case (and it's a problem for you), then there's a good chance you've got far bigger problems to worry about. This is not intended as a security mechanism, merely as a convenient way to identify what a backend is being used for. It doesn't remove any of the existing properties of the connection that the user cannot change (PID, current query, current user, host IP etc). -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Application name patch - v2
2009/10/19 Andrew Dunstan and...@dunslane.net: Pavel Stehule wrote: 2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I dislike write access to app name guc for user too. It's not safe. Maybe only super user can do it? That'll render it pretty useless, as most applications wouldn't then be able to set/reset it when it makes sense to do so. But application can do it simply via connection string, no? Mostly applications has connection string in configuration, so I don't see problem there. And if I would to allow access, then I could to wrap setting to security definer function. I see this as security hole. It allows special SQL injection. How is it any more a security hole than any other setting that the user can alter with an arbitrary string value (e.g. custom options)? Others GUC has not important role in logs. It's similar as possibility to change client IP address. cheers andrew -- 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] Application name patch - v2
2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 9:36 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Then we have to divide this value to two independent values like application_name and application_state. How does that make any difference? That just means we have two values, at least one of which is still userset, and means an additional field in the logs and stats view etc. I see this as security hole. It allows special SQL injection. How so? You change identity. If any application is vulnerable to SQL injection, then this value is nice goal. Are you saying that if your application is vulnerable, then the user may be able to masquerade as something else? If that's the case (and it's a problem for you), then there's a good chance you've got far bigger problems to worry about. This is not intended as a security mechanism, merely as a convenient way to identify what a backend is being used for. It doesn't remove any of the existing properties of the connection that the user cannot change (PID, current query, current user, host IP etc). There are some log parser's and analysers. So people use reduced log often. The reductions rules should be based on application name. Why not? And when somebody modifies to appliacation name, then these logs finish in '/dev/null. regards Pavel Stehule -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Application name patch - v2
On Mon, Oct 19, 2009 at 10:01 AM, Pavel Stehule pavel.steh...@gmail.com wrote: There are some log parser's and analysers. So people use reduced log often. The reductions rules should be based on application name. Why not? And when somebody modifies to appliacation name, then these logs finish in '/dev/null. So if your insecure app worries you, just don't use %a in the log prefix, or ignore the column in the CSV logs. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Application name patch - v2
Pavel Stehule wrote: 2009/10/19 Andrew Dunstan and...@dunslane.net: Pavel Stehule wrote: 2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I dislike write access to app name guc for user too. It's not safe. Maybe only super user can do it? That'll render it pretty useless, as most applications wouldn't then be able to set/reset it when it makes sense to do so. But application can do it simply via connection string, no? Mostly applications has connection string in configuration, so I don't see problem there. And if I would to allow access, then I could to wrap setting to security definer function. I see this as security hole. It allows special SQL injection. How is it any more a security hole than any other setting that the user can alter with an arbitrary string value (e.g. custom options)? Others GUC has not important role in logs. It's similar as possibility to change client IP address. That doesn't even remotely answer the question. How is such a thing a vector for an SQL injection attack, that does not apply to other GUCs? If your answer is that log parsers will try to inject the values, then it those programs that need to be fixed, rather than restricting this facility in a way that will make it close to pointless. And no, it is not at all the same as changing the client's IP address. cheers andrew -- 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] Application name patch - v2
2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 10:01 AM, Pavel Stehule pavel.steh...@gmail.com wrote: There are some log parser's and analysers. So people use reduced log often. The reductions rules should be based on application name. Why not? And when somebody modifies to appliacation name, then these logs finish in '/dev/null. So if your insecure app worries you, just don't use %a in the log prefix, or ignore the column in the CSV logs. I'll know so %a is insecure, but what other users? Every live application is potencially insecure. I agree, so this value is useful for debuging, but with proposed features the value is diskutable. Pavel -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Application name patch - v2
2009/10/19 Andrew Dunstan and...@dunslane.net: Pavel Stehule wrote: 2009/10/19 Andrew Dunstan and...@dunslane.net: Pavel Stehule wrote: 2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I dislike write access to app name guc for user too. It's not safe. Maybe only super user can do it? That'll render it pretty useless, as most applications wouldn't then be able to set/reset it when it makes sense to do so. But application can do it simply via connection string, no? Mostly applications has connection string in configuration, so I don't see problem there. And if I would to allow access, then I could to wrap setting to security definer function. I see this as security hole. It allows special SQL injection. How is it any more a security hole than any other setting that the user can alter with an arbitrary string value (e.g. custom options)? Others GUC has not important role in logs. It's similar as possibility to change client IP address. That doesn't even remotely answer the question. How is such a thing a vector for an SQL injection attack, that does not apply to other GUCs? If your answer is that log parsers will try to inject the values, then it those programs that need to be fixed, rather than restricting this facility in a way that will make it close to pointless. good designed parsers will not have a problem. But lot of parser is based in custom rules. And these rules should be not 100% safe. This proposal increase risks. Pavel And no, it is not at all the same as changing the client's IP address. cheers andrew -- 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] Application name patch - v2
On Mon, Oct 19, 2009 at 10:22 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 10:01 AM, Pavel Stehule pavel.steh...@gmail.com wrote: There are some log parser's and analysers. So people use reduced log often. The reductions rules should be based on application name. Why not? And when somebody modifies to appliacation name, then these logs finish in '/dev/null. So if your insecure app worries you, just don't use %a in the log prefix, or ignore the column in the CSV logs. I'll know so %a is insecure, but what other users? Every live application is potencially insecure. I agree, so this value is useful for debuging, but with proposed features the value is diskutable. %a is not 'insecure'. It's user-configurable. There's a difference. If you don't trust your application or your users not to change the application name, then don't rely on it in your logs or stats. For other users that do trust their app and don't expect their users to be going out of their way to mislead the DBA, this can be a useful feature, as it's proven to be for others that have used the equivalent facilities in other DBMSs. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Application name patch - v2
2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 10:22 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 10:01 AM, Pavel Stehule pavel.steh...@gmail.com wrote: There are some log parser's and analysers. So people use reduced log often. The reductions rules should be based on application name. Why not? And when somebody modifies to appliacation name, then these logs finish in '/dev/null. So if your insecure app worries you, just don't use %a in the log prefix, or ignore the column in the CSV logs. I'll know so %a is insecure, but what other users? Every live application is potencially insecure. I agree, so this value is useful for debuging, but with proposed features the value is diskutable. %a is not 'insecure'. It's user-configurable. There's a difference. If you don't trust your application or your users not to change the application name, then don't rely on it in your logs or stats. For other users that do trust their app and don't expect their users to be going out of their way to mislead the DBA, this can be a useful feature, as it's proven to be for others that have used the equivalent facilities in other DBMSs. I thing, so it should be more useful for DBA - mostly databases are used in web sphere, if write access should be configurable. I understand, so in local application nobody have to be paranoic and restricted access looks unuseful, but on web sphere you have to be paranoic and there the application name should be immutable in session. I like to use this value too, really. But I am working mostly with web applications, and I see risks. Pavel -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Application name patch - v2
On Mon, Oct 19, 2009 at 10:45 AM, Pavel Stehule pavel.steh...@gmail.com wrote: sure, you have to fix fulnerable application. But with some unsophisticated using %a and using wrong tools, the people can be blind and don't register an SQL injection attack. If they're logging the statements (which they presumably are if looking for unusual activity), then they'll see the attack: dp...@myapp: LOG: connection authorized: user=dpage database=postgres dp...@myapp: LOG: statement: set application_name='hax0red'; dp...@hax0red: LOG: disconnection: session time: 0:00:20.152 user=dpage database=postgres host=[local] -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Application name patch - v2
Updated patch attached, fixing a silly thinko. On Fri, Oct 16, 2009 at 12:58 PM, Dave Page dp...@pgadmin.org wrote: I believe the attached patch is ready for review at the next commitfest. It does the following: - Adds a userset GUC called application_name. - Allows application_name to be reported in log_line_prefix using %a. - Includes application_name in CSV logs. - Displays application_name in the output of pg_stat_activity and pg_stat_get_activity(). - Adds a PQconnectdb connection string option called application_name and corresponding envvar PGAPPLICATIONNAME to allow application_name to be set during connection. - Adds a PQconnectdb connection string option called fallback_application_name to allow applications to specify a default application_name value that can be overriden by PGAPPLICATIONNAME. - Ensures that the application_name value is processed early in the connection phase to ensure it can be used in connection log messages. - Includes docs I think that covers all the suggestions discussed over the last couple of days, with the exception of the rejection of \n and similar characters which I'm still not entirely convinced is worth the effort. Any other opinions on that? Anything else that should be added/changed? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com appname-v3.diff Description: Binary data -- 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] Application name patch - v2
On Mon, 2009-10-19 at 08:47 +0100, Dave Page wrote: On Mon, Oct 19, 2009 at 8:37 AM, Peter Eisentraut pete...@gmx.net wrote: On Fri, 2009-10-16 at 12:58 +0100, Dave Page wrote: I think that covers all the suggestions discussed over the last couple of days, with the exception of the rejection of \n and similar characters which I'm still not entirely convinced is worth the effort. Any other opinions on that? Anything else that should be added/changed? So this would effectively allow any minimally authorized user to write whatever they want into the log file whenever they want? Doesn't sound very safe to me. A user can do that anyway if query logging is turned on, but anyway, what would you suggest - accept a-zA-Z0-9 and a few other choice characters only, or just reject a handful (and if so, what)? Well, either you make the thing wide open and thus pretty insecure and unreliable, or you put in arbitrary limits which will possibly upset many users, or you design some fairly complex rules about what is allowed or not in what context. At which point you might realize that you can pretty much do all of this already in a much better way: Create a user account for each application or group of applications and assign them the roles that you are currently using as login users. The user names already show up in all the places that people want: ps, log, activity tables. And moreover, the admin can control exactly who is allowed to use what user name in what context, so there is no log spamming or confusing one's identity. -- 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] Application name patch - v2
On Mon, Oct 19, 2009 at 11:21 AM, Peter Eisentraut pete...@gmx.net wrote: A user can do that anyway if query logging is turned on, but anyway, what would you suggest - accept a-zA-Z0-9 and a few other choice characters only, or just reject a handful (and if so, what)? Well, either you make the thing wide open and thus pretty insecure and unreliable, or you put in arbitrary limits which will possibly upset many users, or you design some fairly complex rules about what is allowed or not in what context. At which point you might realize that you can pretty much do all of this already in a much better way: Create a user account for each application or group of applications and assign them the roles that you are currently using as login users. The user names already show up in all the places that people want: ps, log, activity tables. And moreover, the admin can control exactly who is allowed to use what user name in what context, so there is no log spamming or confusing one's identity. Excuse me one moment whilst I pick myself up from the floor :-) Can you imagine what a maintenance nightmare that would soon become? I might need a role for running the nightly backup, one for a weekly backup, one for each of a dozen data import/export tasks. What about a system supporting multiple applications? I used to have a dozen or more running on one server, with a hundred plus users, many of whom used 2 or 3 applications, some of who would also use reporting tools such as Crystal Reports in addition to the primary application. I'd need to give those users half a dozen or more roles each (which probably won't work nicely in my SSO environment). Please bear in mind that this feature is based on similar features in other DBMSs (and in fact, a feature in the JDBC spec) that people have asked for on a number of occasions. It's not a random idea I've come up with - my aim is to create a comparable feature to that which people may be accustomed to, in a secure and PostgreSQL-applicable way. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Application name patch - v2
2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 10:45 AM, Pavel Stehule pavel.steh...@gmail.com wrote: sure, you have to fix fulnerable application. But with some unsophisticated using %a and using wrong tools, the people can be blind and don't register an SQL injection attack. If they're logging the statements (which they presumably are if looking for unusual activity), then they'll see the attack: dp...@myapp: LOG: connection authorized: user=dpage database=postgres dp...@myapp: LOG: statement: set application_name='hax0red'; dp...@hax0red: LOG: disconnection: session time: 0:00:20.152 user=dpage database=postgres host=[local] this is bad solution. yes, I can found probmlematics rows, but I'll get ten or more larger log. This is available only when loging of application name changes depend on own configuration setting. Regards Pavel -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Rejecting weak passwords
On Thu, 2009-10-15 at 13:19 -0400, Robert Haas wrote: But I don't understand why everyone is so worked up about having an *optional* *flag* to force plaintext instead of MD5. It would be pretty bad usability. Users would be faced with the choice: you can have secure authentication or good passwords, but not both. (For some values of secure and good.) I think most people would want both. -- 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] Application name patch - v2
2009/10/19 Pavel Stehule pavel.steh...@gmail.com: 2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 10:45 AM, Pavel Stehule pavel.steh...@gmail.com wrote: sure, you have to fix fulnerable application. But with some unsophisticated using %a and using wrong tools, the people can be blind and don't register an SQL injection attack. If they're logging the statements (which they presumably are if looking for unusual activity), then they'll see the attack: dp...@myapp: LOG: connection authorized: user=dpage database=postgres dp...@myapp: LOG: statement: set application_name='hax0red'; dp...@hax0red: LOG: disconnection: session time: 0:00:20.152 user=dpage database=postgres host=[local] this is bad solution. yes, I can found probmlematics rows, but I'll get ten or more larger log. This is available only when loging of application name changes depend on own configuration setting. what is +/- same as GUC for write access to application name. Pavel Regards Pavel -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Application name patch - v2
On Mon, Oct 19, 2009 at 12:33 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 10:45 AM, Pavel Stehule pavel.steh...@gmail.com wrote: sure, you have to fix fulnerable application. But with some unsophisticated using %a and using wrong tools, the people can be blind and don't register an SQL injection attack. If they're logging the statements (which they presumably are if looking for unusual activity), then they'll see the attack: dp...@myapp: LOG: connection authorized: user=dpage database=postgres dp...@myapp: LOG: statement: set application_name='hax0red'; dp...@hax0red: LOG: disconnection: session time: 0:00:20.152 user=dpage database=postgres host=[local] this is bad solution. yes, I can found probmlematics rows, but I'll get ten or more larger log. This is available only when loging of application name changes depend on own configuration setting. Why will you get 'ten or more larger log'? If you're looking for suspicious queries from SQL injection attacks, then you'll be logging queries anyway. The only additional log lines will be the hacker... My point is, that the query to change the app name is logged using the *original* app name, thus it will not be discarded by the log analysis tools in your scenario. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Rejecting weak passwords
On Mon, 2009-10-19 at 09:14 +0200, Albe Laurenz wrote: Bruce Momjian wrote: Great, added to TODO: Allow server-side enforcement of password policies Password checks might include password complexity or non-reuse of passwords. This facility will require the client to send the password to the server in plain-text, so SSL and 'password' authentication is necessary to use this features. I don't get why you need 'password' authentication for that. The point where the password should be checked is not when the user uses it to logon, but when he or she changes it. So in my opinion that should be: This facility will require to send new and changed password to the server in plain-text, so it will require SSL, and the use of encrypted passwords in CREATE/ALTER ROLE will have to be disabled. Note that this solution will still not satisfy the original checkbox requirement. -- 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] Application name patch - v2
2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 12:33 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 2009/10/19 Dave Page dp...@pgadmin.org: On Mon, Oct 19, 2009 at 10:45 AM, Pavel Stehule pavel.steh...@gmail.com wrote: sure, you have to fix fulnerable application. But with some unsophisticated using %a and using wrong tools, the people can be blind and don't register an SQL injection attack. If they're logging the statements (which they presumably are if looking for unusual activity), then they'll see the attack: dp...@myapp: LOG: connection authorized: user=dpage database=postgres dp...@myapp: LOG: statement: set application_name='hax0red'; dp...@hax0red: LOG: disconnection: session time: 0:00:20.152 user=dpage database=postgres host=[local] this is bad solution. yes, I can found probmlematics rows, but I'll get ten or more larger log. This is available only when loging of application name changes depend on own configuration setting. Why will you get 'ten or more larger log'? If you're looking for suspicious queries from SQL injection attacks, then you'll be logging queries anyway. The only additional log lines will be the hacker... It is not practical. I'll log errors. Usually SQL injection generates lot of errors. Loging all statements has not sense. What is difference bad and good SQL statement.? Maybe multistatements are good candidates for log as possible attackers statements. On highly load databases loging all statements significantly increase load :( My point is, that the query to change the app name is logged using the *original* app name, thus it will not be discarded by the log analysis tools in your scenario. I thing, so change of original name should generate warning. Pavel -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Application name patch - v2
On Mon, Oct 19, 2009 at 12:57 PM, Pavel Stehule pavel.steh...@gmail.com wrote: It is not practical. I'll log errors. Usually SQL injection generates lot of errors. Loging all statements has not sense. What is difference bad and good SQL statement.? Maybe multistatements are good candidates for log as possible attackers statements. On highly load databases loging all statements significantly increase load :( Ahh, I see. My point is, that the query to change the app name is logged using the *original* app name, thus it will not be discarded by the log analysis tools in your scenario. I thing, so change of original name should generate warning. Well, if other people think that's necessary, it's certainly possible. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Deprecation
On Sat, Oct 17, 2009 at 03:01:27PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Sounds like a good reason to remove add_missing_from in 8.5. Seems like the general consensus is that it's okay to do that. I will go make it happen unless somebody squawks pretty soon... regards, tom lane +1 -dg -- David Gould da...@sonic.net 510 536 1443510 282 0869 If simplicity worked, the world would be overrun with insects. -- 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] Application name patch - v2
On Mon, Oct 19, 2009 at 01:00:28PM +0100, Dave Page wrote: On Mon, Oct 19, 2009 at 12:57 PM, Pavel Stehule pavel.steh...@gmail.com wrote: It is not practical. I'll log errors. Usually SQL injection generates lot of errors. Loging all statements has not sense. What is difference bad and good SQL statement.? Maybe multistatements are good candidates for log as possible attackers statements. On highly load databases loging all statements significantly increase load :( Ahh, I see. My point is, that the query to change the app name is logged using the *original* app name, thus it will not be discarded by the log analysis tools in your scenario. I thing, so change of original name should generate warning. Well, if other people think that's necessary, it's certainly possible. I have clients working around the lack of this feature by simply prepending a single line comment to their sql in the application to supply the app name. eg: -- monthly_report monthly_process.py:524 select wev from foo; This feature would be very handy, but not if it requires special permission to use it. -dg -- David Gould da...@sonic.net 510 536 1443510 282 0869 If simplicity worked, the world would be overrun with insects. -- 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] Application name patch - v2
Andrew Dunstan and...@dunslane.net writes: Pavel Stehule wrote: Others GUC has not important role in logs. It's similar as possibility to change client IP address. That doesn't even remotely answer the question. How is such a thing a vector for an SQL injection attack, that does not apply to other GUCs? If your answer is that log parsers will try to inject the values, then it those programs that need to be fixed, rather than restricting this facility in a way that will make it close to pointless. That's not how I parse Pavel's worries. I think what's he telling here is that seeing how the new GUC will get used (filtering logs), it happens that if you're vulnerable to SQL injection it could be worse with the application name setting than without, because attacker would hide its injections under a filtered-out application name. Not sure my saying is easier to parse than Pavel's, btw... And no, it is not at all the same as changing the client's IP address. If you filter logs by IP to detect attackers, and will filter by application name in the future, I can see how it compares. Now, I don't think Pavel's worries have much weight here because if you're vulnerable to SQL injection you want to first fix this. And you will want to give different (sub-)application names from within the same connection, and the easier way to provide that is to change the GUC value. +1 for user settable GUC for setting application name. -- dim -- 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] Application name patch - v2
2009/10/19 Dimitri Fontaine dfonta...@hi-media.com: Andrew Dunstan and...@dunslane.net writes: Pavel Stehule wrote: Others GUC has not important role in logs. It's similar as possibility to change client IP address. That doesn't even remotely answer the question. How is such a thing a vector for an SQL injection attack, that does not apply to other GUCs? If your answer is that log parsers will try to inject the values, then it those programs that need to be fixed, rather than restricting this facility in a way that will make it close to pointless. That's not how I parse Pavel's worries. I think what's he telling here is that seeing how the new GUC will get used (filtering logs), it happens that if you're vulnerable to SQL injection it could be worse with the application name setting than without, because attacker would hide its injections under a filtered-out application name. Not sure my saying is easier to parse than Pavel's, btw... And no, it is not at all the same as changing the client's IP address. If you filter logs by IP to detect attackers, and will filter by application name in the future, I can see how it compares. Now, I don't think Pavel's worries have much weight here because if you're vulnerable to SQL injection you want to first fix this. And you will want to give different (sub-)application names from within the same connection, and the easier way to provide that is to change the GUC value. sure, you have to fix fulnerable application. But with some unsophisticated using %a and using wrong tools, the people can be blind and don't register an SQL injection attack. +1 for user settable GUC for setting application name. -- dim -- 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] Rejecting weak passwords
Peter Eisentraut wrote: I don't get why you need 'password' authentication for that. The point where the password should be checked is not when the user uses it to logon, but when he or she changes it. So in my opinion that should be: This facility will require to send new and changed password to the server in plain-text, so it will require SSL, and the use of encrypted passwords in CREATE/ALTER ROLE will have to be disabled. Note that this solution will still not satisfy the original checkbox requirement. I guess I misunderstood something there, but I had assumed that the checkbox item read something like: Does the product offer password policy enforcement? (to quote Dave Page). I understood that to mean does the server check if a new password complies with a certain set of rules. Yours, Laurenz Albe -- 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] foreign-key inference join removal
On Oct 19, 2009, at 03:44 , Robert Haas wrote: Suppose we define a new join type called inner_or_left_join. This means that we've proven that every outer row has at least one join partner, so that we'll get the same results whichever way we implement it. We can prove this for either inner joins or left joins, or for right joins with the sides reversed, by checking that: (1) The inner rel is a baserel with no restriction clauses. (2) All the join clauses are merge-joinable. (3) There is a table on the outer side of the join with a foreign key constraint referencing the inner table, such that the columns of the foreign key constraint and the chosen equality operators exactly match up with the join clauses (no extra columns, no extra join clauses). (4) All the relevant columns of the outer table are NOT NULL. While considering this, have you given any thought to the points in http://archives.postgresql.org/pgsql-hackers/2009-07/msg01555.php ? (In short, there are other properties --- e.g. that there is *exactly* one row in B for each in A, uniqueness is kept, etc --- you can deduce from foreign key relationships, which is useful for more than join ordering. The example I gave involved removing Distinct and pushing Limit through a join) -- Alex Brasetvik -- 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] Application name patch - v2
-- monthly_report monthly_process.py:524 select wev from foo; This feature would be very handy, but not if it requires special permission to use it. Superuser permission could not be a problem. Simple security definer function can do it. Regards Pavel -dg -- David Gould da...@sonic.net 510 536 1443 510 282 0869 If simplicity worked, the world would be overrun with insects. -- 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] postgres 8.3.8 and Solaris 10_x86 64 bit problems?
Zdenek Kotala wrote: Andrew Chernow píše v ne 18. 10. 2009 v 21:09 -0400: I'm curious if this is a lost hope. My boss is recommending we flatten the Sun box and install redhat linux (which I'm fine with). I'd rather not as threading in Solaris is better. Maybe solaris threads were better 10-15 years ago, but I'm not convinced that is still the case. Any data supporting that argument, solaris 10 threads vs. linux 2.6.11+ kernel (p)threads? I can point on this article: http://tweakers.net/reviews/649/all/database-test-sun-ultrasparc-t1-vs-punt-amd-opteron.html Zdenek For starters, the original poster is using AMD64, so whether an ultrasparc improves thread performance is immaterial here. OP said: Solaris 10 x86_64 with postgres 8.3.8 and openssl 98k using gcc version 3.4.3 Although the article is interesting, the data came from (or passed through) Sun employees. I'm not saying the article's claims are not true or intentionally misleading, but rather that I am skeptical about the findings; especially since it reads more like a marketing piece than a technical analysis. BTW, I have nothing against Sun or Solaris (spent a few years on Solaris 7 8 sparc servers a while back and found them quite stable). I'm just a hard sell do to endless exaggerated claims by all the top vendors and techy outlets. I find myself weeding through all the hype with a machete :) -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- 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] foreign-key inference join removal
On Mon, Oct 19, 2009 at 8:54 AM, Alex Brasetvik a...@brasetvik.com wrote: On Oct 19, 2009, at 03:44 , Robert Haas wrote: Suppose we define a new join type called inner_or_left_join. This means that we've proven that every outer row has at least one join partner, so that we'll get the same results whichever way we implement it. We can prove this for either inner joins or left joins, or for right joins with the sides reversed, by checking that: (1) The inner rel is a baserel with no restriction clauses. (2) All the join clauses are merge-joinable. (3) There is a table on the outer side of the join with a foreign key constraint referencing the inner table, such that the columns of the foreign key constraint and the chosen equality operators exactly match up with the join clauses (no extra columns, no extra join clauses). (4) All the relevant columns of the outer table are NOT NULL. While considering this, have you given any thought to the points in http://archives.postgresql.org/pgsql-hackers/2009-07/msg01555.php ? (In short, there are other properties --- e.g. that there is *exactly* one row in B for each in A, uniqueness is kept, etc --- you can deduce from foreign key relationships, which is useful for more than join ordering. The example I gave involved removing Distinct and pushing Limit through a join) It's in the back of my mind, but I think join removal join reordering are the biggest wins here. Pushing a LIMIT through a join doesn't really help by itself because, under the pull model PostgreSQL uses, the lower nodes will only be evaluated to the extent necessary to satisfy the LIMIT. Getting rid of DISTINCT ON could be very useful, but I think it's probably something of a corner case, since normally you won't bother to include DISTINCT ON in the first place if it's not doing anything. ...Robert -- 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] Rejecting weak passwords
On Mon, Oct 19, 2009 at 7:34 AM, Peter Eisentraut pete...@gmx.net wrote: On Thu, 2009-10-15 at 13:19 -0400, Robert Haas wrote: But I don't understand why everyone is so worked up about having an *optional* *flag* to force plaintext instead of MD5. It would be pretty bad usability. Users would be faced with the choice: you can have secure authentication or good passwords, but not both. (For some values of secure and good.) I think most people would want both. Unless you have the ability to entirely control the software that users use to access PostgreSQL, which is probably only true in super-high-security environments and is certainly false anywhere I've ever worked, you can only have one of those things. SSH keys or SSL certificates are great for defeating network attacks, but I know a lot of people who keep SSL certificates unencrypted on their laptops because there's no easy way to stop them. Those very same people can EASILY be forced to pick relatively good Windows logon passwords because AD can enforce password complexity requirements. Of course, they can't be forced not to write their Windows logon password on a napkin, but they also can't be forced not to run an unsecured FTP server on their laptop that provides access to their unencrypted SSH keys/SSL certificates. Now, we can argue all day about probabilities, but I don't see any reason to believe that we know for sure what the best trade-off is in every environment, which is why I favor providing options, documenting the trade-offs, and letting users make the final decision. ...Robert -- 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] Application name patch - v2
On Mon, Oct 19, 2009 at 11:39:58AM +0100, Dave Page wrote: Excuse me one moment whilst I pick myself up from the floor :-) Heh! Can you imagine what a maintenance nightmare that would soon become? Only vaguely, and that's enough. Please bear in mind that this feature is based on similar features in other DBMSs (and in fact, a feature in the JDBC spec) Could you point to a reference for this? It could help the rest of us see what you're aiming for even better :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Writeable CTEs and side effects
Tom Lane wrote: Merlin Moncure mmonc...@gmail.com writes: Is the above form: with x as (delete .. returning *) insert into y select * from x going to be allowed? I was informed on irc that it wasn't...it would have to be written as: insert into y with x as (delete .. returning *) select * from x I would think that we would require the former and forbid the latter. One of the basic limitations of the feature is going to be that you can only have WITH (something RETURNING) at the top level, and the latter syntax doesn't look like that to me. I'm looking at this, and if I understood correctly, you're suggesting we'd add a WithClause to InsertStmt. Would we also allow this? WITH t1 AS (DELETE FROM foo RETURNING *) INSERT INTO bar WITH t2 AS (VALUES(0)) SELECT * FROM t1 UNION ALL SELECT * FROM t2; I could also see use for adding this for UDPATE and DELETE too, i.e. WITH t AS (DELETE FROM foo RETURNING id) UPDATE bar SET foo_id = NULL FROM t WHERE t.id = bar.foo_id; Did I misunderstand something here? Regards, Marko Tiikkaja -- 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] Rejecting weak passwords
Albe Laurenz laurenz.a...@wien.gv.at writes: Bruce Momjian wrote: Password checks might include password complexity or non-reuse of passwords. This facility will require the client to send the password to the server in plain-text, so SSL and 'password' authentication is necessary to use this features. So in my opinion that should be: This facility will require to send new and changed password to the server in plain-text, so it will require SSL, and the use of encrypted passwords in CREATE/ALTER ROLE will have to be disabled. Actually, not one word of *either* version should be in TODO. All of that is speculation about policies that a particular add-on module might or might not choose to enforce. regards, tom lane -- 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] Application name patch - v2
* Pavel Stehule (pavel.steh...@gmail.com) wrote: Superuser permission could not be a problem. Simple security definer function can do it. Then you've defeated the point of making it superuser-only. I don't think that changing the app name deserves a warning, to be perfectly honest. Notice should be sufficient. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Application name patch - v2
Dave Page dp...@pgadmin.org writes: On Mon, Oct 19, 2009 at 12:57 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I thing, so change of original name should generate warning. Well, if other people think that's necessary, it's certainly possible. I think Pavel's entire line of argument is utter nonsense. He's setting up a straw man that has nothing to do with any actually likely use of the variable. I do agree with Peter's concerns about limiting the character set of the name string, and maybe there should be some sort of length limit too. regards, tom lane -- 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] Application name patch - v2
* Pavel Stehule (pavel.steh...@gmail.com) wrote: 2009/10/19 Heikki Linnakangas heikki.linnakan...@enterprisedb.com: Or are you saying that it should not be possible for the client to change the value after connecting? That limits the usefulness with connection pools. What I know, connections from connection pool without reset are shared by one application. But I am not against some possibility to change this value from application. I am against to possibility an change by normal user. When we allow it, then this value has not any wight, because any broken appliaction (via SQL injection) can change it. Broken applications have much bigger problems than this. Predicating what we would/should do on the assumption of an application that's broken just doesn't make sense to me. Stephen signature.asc Description: Digital signature
Re: [HACKERS] Application name patch - v2
On Mon, Oct 19, 2009 at 3:17 PM, David Fetter da...@fetter.org wrote: Could you point to a reference for this? It could help the rest of us see what you're aiming for even better :) Sure. Here's a nice example from SQL Server as well as related doc links: http://blog.benhall.me.uk/2007/10/sql-connection-application-name.html http://msdn.microsoft.com/en-us/library/ms189770.aspx http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring%28VS.85%29.aspx Kris pointed out the JDBC usage of this (and some related properties): http://archives.postgresql.org/pgsql-hackers/2009-10/msg00726.php Similar features are available in Oracle: http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_appin2.htm http://www.dba-oracle.com/security/the_client_identifier.htm (in this case, it is considered a security/auditing feature) -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Application name patch - v2
David Fetter da...@fetter.org writes: On Mon, Oct 19, 2009 at 11:39:58AM +0100, Dave Page wrote: Please bear in mind that this feature is based on similar features in other DBMSs (and in fact, a feature in the JDBC spec) Could you point to a reference for this? It could help the rest of us see what you're aiming for even better :) Yeah. One thing I would like to see is a confirmation that this feature can actually be used by the JDBC driver to implement the relevant feature(s) of the JDBC spec. regards, tom lane -- 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] Application name patch - v2
* Tom Lane (t...@sss.pgh.pa.us) wrote: Dave Page dp...@pgadmin.org writes: Well, if other people think that's necessary, it's certainly possible. I think Pavel's entire line of argument is utter nonsense. He's setting up a straw man that has nothing to do with any actually likely use of the variable. I do agree with Peter's concerns about limiting the character set of the name string, and maybe there should be some sort of length limit too. I was thinking we might just declare it of type 'name'.. Stephen signature.asc Description: Digital signature
Re: [HACKERS] Application name patch - v2
Sure. Here's a nice example from SQL Server as well as related doc links: http://blog.benhall.me.uk/2007/10/sql-connection-application-name.html http://msdn.microsoft.com/en-us/library/ms189770.aspx http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring%28VS.85%29.aspx that looks as if the entry defaults to the application name (argv[0]) Similar features are available in Oracle: http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_appin2.htm and I definitely know from Oracle, that the application name is the default without the progammer / user issuing any SET command. Would'nt this also make sense for PostgreSQL? That is, when no environment is set, and no SET-command is issued, that the application name becomes the default? Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - %s is too gigantic of an industry to bend to the whims of reality
Re: [HACKERS] Application name patch - v2
On Mon, Oct 19, 2009 at 3:42 PM, Massa, Harald Armin c...@ghum.de wrote: Would'nt this also make sense for PostgreSQL? That is, when no environment is set, and no SET-command is issued, that the application name becomes the default? That needs to be set by the application. As discussed previously, there's no way for libpq to get at argv[0]. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Application name patch - v2
Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: I do agree with Peter's concerns about limiting the character set of the name string, and maybe there should be some sort of length limit too. I was thinking we might just declare it of type 'name'.. 'name' wouldn't help, since it's pretty character-set-agnostic. Anyway this is a GUC not a table column. Thinking about it more, it should be sufficient if we can ensure that the value is in the database encoding; logging of statements will already cause pretty much any legal DB-encoded string to be written to the log, so if you have a problem with that then you've already got a problem to fix. This is no issue for an ordinary SET, but AFAIR we do not have a good story for handling non-ASCII stuff arriving within the initial connection request packet. It might be time to try to do something about that. Or we could just restrict those values to ASCII. regards, tom lane -- 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] Controlling changes in plpgsql variable resolution
* Tom Lane (t...@sss.pgh.pa.us) wrote: I think there are basically three behaviors that we could offer: 1. Resolve ambiguous names as plpgsql (historical PG behavior) 2. Resolve ambiguous names as query column (Oracle behavior) 3. Throw error if name is ambiguous (useful for finding problems) 4. Resolve ambiguous names as query column, but throw warning #4 would be my vote, followed by #3. To be perfectly honest, I'd be a whole lot happier with a pl/pgsql that let me prefix variable names with a '$' or similar to get away from this whole nonsense. I've been very tempted to tell everyone I work with to start prefixing their variables names with '_' except that it ends up looking just plain ugly. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Application name patch - v2
On Mon, Oct 19, 2009 at 3:49 PM, Tom Lane t...@sss.pgh.pa.us wrote: Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: I do agree with Peter's concerns about limiting the character set of the name string, and maybe there should be some sort of length limit too. I was thinking we might just declare it of type 'name'.. 'name' wouldn't help, since it's pretty character-set-agnostic. Anyway this is a GUC not a table column. As a sidenote, in the stats part of this patch I did limit the length to NAMEDATALEN for the purposes of sizing the shared memory allocation, however it's otherwise unlimited in length. Practically that just means that like the current query, the application name may be truncated when viewed through pg_stat_get_activity(). Thinking about it more, it should be sufficient if we can ensure that the value is in the database encoding; logging of statements will already cause pretty much any legal DB-encoded string to be written to the log, so if you have a problem with that then you've already got a problem to fix. Right - that's what I was saying to Peter earlier. That can of course be turned off though This is no issue for an ordinary SET, but AFAIR we do not have a good story for handling non-ASCII stuff arriving within the initial connection request packet. It might be time to try to do something about that. Or we could just restrict those values to ASCII. It would seem sensible to apply the same rule to the connection string and SET, if only for consistency (at least as far as application_name is concerned). I know that use of Japanese/Chinese characters in database names is not uncommon however, so restricting connection strings to ASCII in general might not go down well. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Application name patch - v2
2009/10/19 Stephen Frost sfr...@snowman.net: * Pavel Stehule (pavel.steh...@gmail.com) wrote: Superuser permission could not be a problem. Simple security definer function can do it. Then you've defeated the point of making it superuser-only. no. Because when I write security definer function, then I explicitly allow an writing for some roles. When I don't write this function, then GUC is secure. Pavel I don't think that changing the app name deserves a warning, to be perfectly honest. Notice should be sufficient. Thanks, Stephen -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkrceMsACgkQrzgMPqB3kihrpwCePXXJLxXIpvfHF0fMXbO6Pn94 uJcAn2cnT97QNqeRW2coKRDZfWVKaXxz =xvXs -END PGP SIGNATURE- -- 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] COPY enhancements
Gokulakannan Somasundaram escribió: Actually this problem is present even in today's transaction id scenario and the only way we avoid is by using freezing. Can we use a similar approach? This freezing should mean that we are freezing the sub-transaction in order to avoid the sub-transaction wrap around failure. This would mean we would have to go over the data inserted by the subtransaction and mark it as subxact frozen. Some sort of sub-vacuum if you will (because it obviously needs to work inside a transaction). Doesn't sound real workable to me. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Application name patch - v2
* Pavel Stehule (pavel.steh...@gmail.com) wrote: 2009/10/19 Stephen Frost sfr...@snowman.net: * Pavel Stehule (pavel.steh...@gmail.com) wrote: Superuser permission could not be a problem. Simple security definer function can do it. Then you've defeated the point of making it superuser-only. no. Because when I write security definer function, then I explicitly allow an writing for some roles. When I don't write this function, then GUC is secure. And what happens when those 'some roles' are used by broken applications? You don't get to say make it superuser only and then turn around and tell people to hack around the fact that it's superuser only to be able to use it. That's not a solution. Stephen signature.asc Description: Digital signature
Re: [HACKERS] COPY enhancements
On Mon, Oct 19, 2009 at 11:21 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Gokulakannan Somasundaram escribió: Actually this problem is present even in today's transaction id scenario and the only way we avoid is by using freezing. Can we use a similar approach? This freezing should mean that we are freezing the sub-transaction in order to avoid the sub-transaction wrap around failure. This would mean we would have to go over the data inserted by the subtransaction and mark it as subxact frozen. Some sort of sub-vacuum if you will (because it obviously needs to work inside a transaction). Doesn't sound real workable to me. Especially because the XID consumed by the sub-transaction would still be consumed, advancing the global XID counter. Reclaiming the XIDs after the fact doesn't fix anything as far as I can see. ...Robert -- 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] Controlling changes in plpgsql variable resolution
On Mon, Oct 19, 2009 at 10:54 AM, Stephen Frost sfr...@snowman.net wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: I think there are basically three behaviors that we could offer: 1. Resolve ambiguous names as plpgsql (historical PG behavior) 2. Resolve ambiguous names as query column (Oracle behavior) 3. Throw error if name is ambiguous (useful for finding problems) 4. Resolve ambiguous names as query column, but throw warning #4 would be my vote, followed by #3. To be perfectly honest, I'd be a whole lot happier with a pl/pgsql that let me prefix variable names with a '$' or similar to get away from this whole nonsense. I've been very tempted to tell everyone I work with to start prefixing their variables names with '_' except that it ends up looking just plain ugly. I think warnings are too easy to miss, but I agree your other suggestion. I know you can write function_name.variable_name, but that's often massively long-winded. We either need a short, fixed prefix, or some kind of sigil. I previously suggested ? to parallel ECPG, but Tom didn't like it. I still do. :-) ...Robert -- 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] Application name patch - v2
On Mon, Oct 19, 2009 at 10:36 AM, Tom Lane t...@sss.pgh.pa.us wrote: Dave Page dp...@pgadmin.org writes: On Mon, Oct 19, 2009 at 12:57 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I thing, so change of original name should generate warning. Well, if other people think that's necessary, it's certainly possible. I think Pavel's entire line of argument is utter nonsense. He's setting up a straw man that has nothing to do with any actually likely use of the variable. +1. I can't even understand why we're still arguing about this. Other than Pavel, everyone thinks this is a complete non-problem, and Pavel's hypothesis basically boils down to someone might use this feature in a stupid and naive way. Well, sure. They might. So what? I do agree with Peter's concerns about limiting the character set of the name string, and maybe there should be some sort of length limit too. I don't have a strong feeling about this. If limiting this to 7-bit characters solves some nasty encoding problems or something, then fine, but otherwise I think we can just escape what we emit into the log and say that users who log this information should have a sufficiently sophisticated log parser to cope with it. ...Robert -- 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] Writeable CTEs and side effects
Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: I'm looking at this, and if I understood correctly, you're suggesting we'd add a WithClause to InsertStmt. Would we also allow this? Yeah, we could eventually do all that. I think supporting it in SELECT would be plenty to start with, though. regards, tom lane -- 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] Application name patch - v2
David Fetter da...@fetter.org wrote: Could you point to a reference for this? It could help the rest of us see what you're aiming for even better :) Sybase Adaptive Server Enterprise (ASE) clientapplname varchar(30) column in sysprocesses table: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.tables/html/tables/tables50.htm It can be set (for example) in the LOGINREC structure with DBSETLAPP: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32600.1500/html/dblib/dblib18.htm SET clientapplname command to set on the fly by running SQL on the connection: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.commands/html/commands/commands85.htm -Kevin -- 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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock
Simon Riggs wrote: On Fri, 2009-08-07 at 15:58 -0400, Alvaro Herrera wrote: Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: Is there a good reason for $subject, other than that the code is entangled with other ALTER TABLE code? I think it could be lower, but it would take nontrivial restructuring of the ALTER TABLE support. In particular, consider what happens when you have a list of subcommands that don't all require the same lock level. I think you'd need to scan the list and find the highest required lock level before starting ... IIRC there was a patch from Simon to address this issue, but it had some holes which he didn't have time to close, so it sank. Maybe this can be resurrected and fixed. I was intending to finish that patch in this release cycle. Since you're busy with Hot Standby, any chance you could pass it on? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock
On Mon, 2009-10-19 at 12:56 -0300, Alvaro Herrera wrote: Simon Riggs wrote: On Fri, 2009-08-07 at 15:58 -0400, Alvaro Herrera wrote: Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: Is there a good reason for $subject, other than that the code is entangled with other ALTER TABLE code? I think it could be lower, but it would take nontrivial restructuring of the ALTER TABLE support. In particular, consider what happens when you have a list of subcommands that don't all require the same lock level. I think you'd need to scan the list and find the highest required lock level before starting ... IIRC there was a patch from Simon to address this issue, but it had some holes which he didn't have time to close, so it sank. Maybe this can be resurrected and fixed. I was intending to finish that patch in this release cycle. Since you're busy with Hot Standby, any chance you could pass it on? If you'd like. It's mostly finished, just one last thing to finish: atomic changes to pg_class via an already agreed API. -- Simon Riggs www.2ndQuadrant.com -- 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] Rejecting weak passwords
On Mon, 2009-10-19 at 14:54 +0200, Albe Laurenz wrote: Peter Eisentraut wrote: Note that this solution will still not satisfy the original checkbox requirement. I guess I misunderstood something there, but I had assumed that the checkbox item read something like: Does the product offer password policy enforcement? (to quote Dave Page). The answer to that is currently Yes, with external tools. Using the plugin approach, the answer will remain Yes, with external tools. So we wouldn't gain much. -- 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] Application name patch - v2
Robert Haas robertmh...@gmail.com wrote: Tom Lane t...@sss.pgh.pa.us wrote: I think Pavel's entire line of argument is utter nonsense. +1. I can't even understand why we're still arguing about this. Agreed. One premise of the whole concept was don't even think of using it for security[1]. That's not it's purpose; so any criticisms on that basis are irrelevant. Claims that it opens new security holes if you *don't* try to use it for this purpose don't seem to have any merit that I can see; I don't think Pavel has even attempted to put such an argument forward. -Kevin [1] http://archives.postgresql.org/message-id/407d949e0907161237r76ebd92av6836c6563d8a2...@mail.gmail.com -- 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] Rejecting weak passwords
Peter Eisentraut pete...@gmx.net writes: On Mon, 2009-10-19 at 14:54 +0200, Albe Laurenz wrote: I guess I misunderstood something there, but I had assumed that the checkbox item read something like: Does the product offer password policy enforcement? (to quote Dave Page). The answer to that is currently Yes, with external tools. Using the plugin approach, the answer will remain Yes, with external tools. So we wouldn't gain much. Except that your first statement is false. It is not possible currently for any tool to prevent someone from doing ALTER USER joe PASSWORD joe. A server-side plugin can provide a guarantee that there are no bad passwords (for some value of bad, and with some possible adverse consequences). We don't have that today. regards, tom lane -- 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] Rejecting weak passwords
Tom Lane escribió: Peter Eisentraut pete...@gmx.net writes: On Mon, 2009-10-19 at 14:54 +0200, Albe Laurenz wrote: I guess I misunderstood something there, but I had assumed that the checkbox item read something like: Does the product offer password policy enforcement? (to quote Dave Page). The answer to that is currently Yes, with external tools. Using the plugin approach, the answer will remain Yes, with external tools. So we wouldn't gain much. Except that your first statement is false. It is not possible currently for any tool to prevent someone from doing ALTER USER joe PASSWORD joe. A server-side plugin can provide a guarantee that there are no bad passwords (for some value of bad, and with some possible adverse consequences). We don't have that today. We do, if you have you server grabbing passwords from LDAP or whatever external auth service you use. That would be more secure than anything mentioned in this thread, because the password enforcement could work on unencrypted passwords without adverse consequences. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 7:54 AM, Stephen Frost wrote: 4. Resolve ambiguous names as query column, but throw warning #4 would be my vote, followed by #3. To be perfectly honest, I'd be a whole lot happier with a pl/pgsql that let me prefix variable names with a '$' or similar to get away from this whole nonsense. I've been very tempted to tell everyone I work with to start prefixing their variables names with '_' except that it ends up looking just plain ugly. +1, just what I was thinking. Best, David -- 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] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 8:36 AM, Robert Haas wrote: I think warnings are too easy to miss, but I agree your other suggestion. I know you can write function_name.variable_name, but that's often massively long-winded. We either need a short, fixed prefix, or some kind of sigil. I previously suggested ? to parallel ECPG, but Tom didn't like it. I still do. :-) I suppose that $ would interfere with dollar quoting. What about @ or @@ (sorry, I did mess with MSSQL back in the 90s). Hrm…PostgreSQL is starting to have the same problem as Perl: running out of characters because they're used for operators. :var would be perfect, if it wasn't for psql. ?var is okay, I guess, if a bit… questionable. Are {braces} used for anything? Best, David -- 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] Controlling changes in plpgsql variable resolution
* David E. Wheeler (da...@kineticode.com) wrote: On Oct 19, 2009, at 8:36 AM, Robert Haas wrote: I think warnings are too easy to miss, but I agree your other suggestion. I know you can write function_name.variable_name, but that's often massively long-winded. We either need a short, fixed prefix, or some kind of sigil. I previously suggested ? to parallel ECPG, but Tom didn't like it. I still do. :-) I suppose that $ would interfere with dollar quoting. What about @ or @@ (sorry, I did mess with MSSQL back in the 90s). Uh, what dollar quoting? $_$ is what I typically use, so I wouldn't expect a $ prefix to cause a problem. I think it'd be more of an issue because pl/pgsql still uses $1 and whatnot internally (doesn't it?). Stephen signature.asc Description: Digital signature
Re: [HACKERS] Rejecting weak passwords
Alvaro Herrera wrote: Except that your first statement is false. It is not possible currently for any tool to prevent someone from doing ALTER USER joe PASSWORD joe. A server-side plugin can provide a guarantee that there are no bad passwords (for some value of bad, and with some possible adverse consequences). We don't have that today. We do, if you have you server grabbing passwords from LDAP or whatever external auth service you use. That would be more secure than anything mentioned in this thread, because the password enforcement could work on unencrypted passwords without adverse consequences. We don't have it today for passwords that postgres manages. Unless we're going to rely on an external auth source completely, I think there's a good case for the hooks, but not for any of the other adjustments that people have suggested. cheers andrew -- 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] Rejecting weak passwords
I wrote: A server-side plugin can provide a guarantee that there are no bad passwords (for some value of bad, and with some possible adverse consequences). We don't have that today. BTW, it strikes me that ALTER USER RENAME introduces an interesting hazard for such a plugin. Consider CREATE USER joe; ALTER USER joe PASSWORD joe; -- presumably, plugin will reject this ALTER USER joe PASSWORD mumblefrotz; -- assume this is considered OK ALTER USER joe RENAME TO mumblefrotz; Now we have a user with name equal to password, which no sane security policy will think is a good thing, but the plugin had no chance to prevent it. In the case where the password is stored MD5-crypted, we clear it on RENAME because of the fact that the username is part of the hash. (We had always thought that was a bug^Wimplementation restriction, but now it looks like a feature.) So in normal practice the above hazard doesn't exist; but it would for cleartext passwords. One thing we could do is *always* clear the password on RENAME. Another is to keep the cleartext password, but pass the new name and password through the plugin before allowing the RENAME to succeed. Since the PW is cleartext, presumably the plugin won't have any problem checking it. The latter however seems like we are getting a security-critical behavior out of a chance combination of implementation artifacts, which doesn't make me feel comfortable. Thoughts? regards, tom lane -- 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] Application name patch - v2
2009/10/19 Stephen Frost sfr...@snowman.net: * Pavel Stehule (pavel.steh...@gmail.com) wrote: 2009/10/19 Stephen Frost sfr...@snowman.net: * Pavel Stehule (pavel.steh...@gmail.com) wrote: Superuser permission could not be a problem. Simple security definer function can do it. Then you've defeated the point of making it superuser-only. no. Because when I write security definer function, then I explicitly allow an writing for some roles. When I don't write this function, then GUC is secure. And what happens when those 'some roles' are used by broken applications? You don't get to say make it superuser only and then turn around and tell people to hack around the fact that it's superuser only to be able to use it. That's not a solution. You don't understand me. When I would to have a secure environment, then I don't write this function. So there will not be a posibility to change application name from session. Pavel Stephen -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkrchUYACgkQrzgMPqB3kij8nACfUrF/wkpsORpXiN0QgbXvONdi ghYAn19MpPNnRrf9BxmIOVBRR212JU6c =c5tL -END PGP SIGNATURE- -- 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] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 9:29 AM, Stephen Frost wrote: Uh, what dollar quoting? $_$ is what I typically use, so I wouldn't expect a $ prefix to cause a problem. I think it'd be more of an issue because pl/pgsql still uses $1 and whatnot internally (doesn't it?). Yes, but that's no more an issue than it is in Perl, where the same $n variables are globals. The issue with dollar quoting is that you can put anything between the dollar signs. So if you have two $variables, they can get in the way. Potentially. But perhaps the lexer and/or Parser won't be confused by that, Tom? I'd sure love $, as it's like shell, Perl, and other stuff. Best, David -- 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] Controlling changes in plpgsql variable resolution
David E. Wheeler da...@kineticode.com writes: I'd sure love $, as it's like shell, Perl, and other stuff. This discussion has gotten utterly off track. The problem I am trying to solve is a non-Oracle-compatible behavior in plpgsql. I have got substantially less than zero interest in proposals that solve the problem by introducing notations that don't even pretend to be compatible. regards, tom lane -- 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] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 9:49 AM, Tom Lane wrote: I'd sure love $, as it's like shell, Perl, and other stuff. This discussion has gotten utterly off track. The problem I am trying to solve is a non-Oracle-compatible behavior in plpgsql. I have got substantially less than zero interest in proposals that solve the problem by introducing notations that don't even pretend to be compatible. Party pooper. I'd be in favor of a GUC that I could turn on to throw an error when there's an ambiguity. As for which way it should go, I have no dog in that pony hunt. Or something. Best, David -- 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] Scaling up deferred unique checks and the after trigger queue
On Mon, Oct 19, 2009 at 12:48 PM, Dean Rasheed dean.a.rash...@googlemail.com wrote: This is a WIP patch to replace the after-trigger queues with TID bitmaps to prevent them from using excessive amounts of memory. Each round of trigger executions is a modified bitmap heap scan. If the bitmap becomes lossy, how do you preserve the correct semantics? ...Robert -- 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] Controlling changes in plpgsql variable resolution
David E. Wheeler da...@kineticode.com wrote: I'd be in favor of a GUC that I could turn on to throw an error when there's an ambiguity. I would consider hiding one definition with another very bad form, so I would prefer to have plpgsql throw an error when that happens. I don't particularly care whether that is the only supported behavior or whether there's a GUC to control it, or what its default is, if present. -Kevin -- 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] Scaling up deferred unique checks and the after trigger queue
2009/10/19 Robert Haas robertmh...@gmail.com: On Mon, Oct 19, 2009 at 12:48 PM, Dean Rasheed dean.a.rash...@googlemail.com wrote: This is a WIP patch to replace the after-trigger queues with TID bitmaps to prevent them from using excessive amounts of memory. Each round of trigger executions is a modified bitmap heap scan. If the bitmap becomes lossy, how do you preserve the correct semantics? ...Robert The idea is that it filters by the transaction ID and command ID of modified rows to see what's been updated in the command(s) the trigger is for... - Dean -- 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] Rejecting weak passwords
Andrew Dunstan and...@dunslane.net writes: Alvaro Herrera wrote: We do, if you have you server grabbing passwords from LDAP or whatever external auth service you use. That would be more secure than anything mentioned in this thread, because the password enforcement could work on unencrypted passwords without adverse consequences. We don't have it today for passwords that postgres manages. Unless we're going to rely on an external auth source completely, I think there's a good case for the hooks, but not for any of the other adjustments that people have suggested. Yeah. Installing LDAP or Kerberos or whatever is sensible if you have a need for a central auth server anyway. If you are just trying to run a database, it's a major additional investment of effort, and I can't quibble at all with people who think that it's unreasonable to have to do that just to have some modicum of a password policy. I also am of the opinion that it's reasonable to provide a hook or two for this purpose, but not to go further than that. regards, tom lane -- 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] Controlling changes in plpgsql variable resolution
2009/10/19 Kevin Grittner kevin.gritt...@wicourts.gov: David E. Wheeler da...@kineticode.com wrote: I'd be in favor of a GUC that I could turn on to throw an error when there's an ambiguity. I would consider hiding one definition with another very bad form, so I would prefer to have plpgsql throw an error when that happens. I don't particularly care whether that is the only supported behavior or whether there's a GUC to control it, or what its default is, if present. ambiguous identifiers is probably the top reason of some plpgsql's mysterious errors. More times I found wrong code - sometime really important (some security checks). I never found good code with ambiguous identifiers - so for me, exception is good. But - there will be lot of working applications that contains this hidden bug - and works well. So it could be a problem. GUC should be a solution. Pavel -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Controlling changes in plpgsql variable resolution
On Mon, Oct 19, 2009 at 12:49 PM, Tom Lane t...@sss.pgh.pa.us wrote: David E. Wheeler da...@kineticode.com writes: I'd sure love $, as it's like shell, Perl, and other stuff. This discussion has gotten utterly off track. The problem I am trying to solve is a non-Oracle-compatible behavior in plpgsql. I have got substantially less than zero interest in proposals that solve the problem by introducing notations that don't even pretend to be compatible. Personally, I'd vote against a GUC option. I just plain don't like the idea that a function could do different things depending on server configuration. TBH, I'm not very happy with #option either. That said, I agree that Oracle method is far better. Maybe invent a new language handler? plpgsql2 or shorten to pgsql? Now you can mess around all you want (and maybe fix some other compatibility warts at the same time). merlin -- 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] Controlling changes in plpgsql variable resolution
On Mon, Oct 19, 2009 at 12:49 PM, Tom Lane t...@sss.pgh.pa.us wrote: David E. Wheeler da...@kineticode.com writes: I'd sure love $, as it's like shell, Perl, and other stuff. This discussion has gotten utterly off track. The problem I am trying to solve is a non-Oracle-compatible behavior in plpgsql. I have got substantially less than zero interest in proposals that solve the problem by introducing notations that don't even pretend to be compatible. OK. In that case, it seems like we should offer options #2 and #3 with a GUC or #option to switch between them. Nobody has made an argument in favor of keeping #1 around. I'm still strongly of the opinion that #3 (error) should be the default behavior to avoid silent failures. ...Robert -- 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] postgres 8.3.8 and Solaris 10_x86 64 bit problems?
Zdenek Kotala wrote: I can point on this article: http://tweakers.net/reviews/649/all/database-test-sun-ultrasparc-t1-vs-punt-amd-opteron.html Zdenek Ok so I'm checking everything in my environment. The system actually builds postgres with openssl98k. Comes back and says it's ready to install. I run 'make install' and try to run something like pg_ctl again. Seem to be seeing the same results. # file pg_ctl pg_ctl: ELF 64-bit LSB executable AMD64 Version 1 [SSE FXSR CMOV FPU], dynamically linked, not stripped # ./pg_ctl ld.so.1: pg_ctl: fatal: relocation error: R_AMD64_32: file /usr/local/postgres64/lib/libpq.so.5: symbol (unknown): value 0xfd7fff1cf210 does not fit Killed So I run 'ldd pg_ctl' to see if everything is linking ok. libpq.so.5 =/usr/local/postgres64/lib/libpq.so.5 libm.so.2 = /usr/lib/64/libm.so.2 libxml2.so.2 = /usr/lib/64/libxml2.so.2 libz.so.1 = /usr/lib/64/libz.so.1 libreadline.so.6 = /usr/local/lib/libreadline.so.6 libcurses.so.1 =/usr/lib/64/libcurses.so.1 librt.so.1 =/usr/lib/64/librt.so.1 libsocket.so.1 =/usr/lib/64/libsocket.so.1 libc.so.1 = /usr/lib/64/libc.so.1 libpthread.so.1 = /usr/lib/64/libpthread.so.1 libnsl.so.1 = /lib/64/libnsl.so.1 libgcc_s.so.1 = /usr/sfw/lib/amd64/libgcc_s.so.1 libaio.so.1 = /lib/64/libaio.so.1 libmd.so.1 =/lib/64/libmd.so.1 libmp.so.2 =/lib/64/libmp.so.2 libscf.so.1 = /lib/64/libscf.so.1 libdoor.so.1 = /lib/64/libdoor.so.1 libuutil.so.1 = /lib/64/libuutil.so.1 libgen.so.1 = /lib/64/libgen.so.1 And I'm wondering if there is a problem with libpq.so.5 as mentioned in the original error # file /usr/local/postgres64/lib/libpq.so.5 /usr/local/postgres64/lib/libpq.so.5: ELF 64-bit LSB dynamic lib AMD64 Version 1 [SSE CMOV], dynamically linked, not stripped Ok. So looking good. Maybe there is a library or header libpq needs that I'm missing in 64 bit? # ldd /usr/local/postgres64/lib/libpq.so.5 libsocket.so.1 =/usr/lib/64/libsocket.so.1 libpthread.so.1 = /usr/lib/64/libpthread.so.1 libc.so.1 = /usr/lib/64/libc.so.1 libnsl.so.1 = /lib/64/libnsl.so.1 libmp.so.2 =/lib/64/libmp.so.2 libmd.so.1 =/lib/64/libmd.so.1 libscf.so.1 = /lib/64/libscf.so.1 libdoor.so.1 = /lib/64/libdoor.so.1 libuutil.so.1 = /lib/64/libuutil.so.1 libgen.so.1 = /lib/64/libgen.so.1 libm.so.2 = /lib/64/libm.so.2 Looks good. I'm not sure where to go from here. I have everything else I need built in 64 bit except for Postgres with ssl :/ I've spent the last few weeks googling and talking to people about it. Not sure what I'm missing here. Thanks! -- 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] Controlling changes in plpgsql variable resolution
Pavel Stehule pavel.steh...@gmail.com writes: ambiguous identifiers is probably the top reason of some plpgsql's mysterious errors. More times I found wrong code - sometime really important (some security checks). I never found good code with ambiguous identifiers - so for me, exception is good. But - there will be lot of working applications that contains this hidden bug - and works well. So it could be a problem. GUC should be a solution. So the conclusions so far are: (a) Nobody but me is afraid of the consequences of treating this as a GUC. (I still think you're all wrong, but so be it.) (b) Everybody agrees that a throw error setting would be helpful. I am not sure there's any consensus on what the default setting should be, though. Can we get away with making the default be throw error? What are the probabilities that the OpenACSes of the world will just set the value to backward compatible instead of touching their code? Do we need/want a hack in pg_dump to attach a SET to functions dumped from old DBs? regards, tom lane -- 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] Controlling changes in plpgsql variable resolution
On Mon, Oct 19, 2009 at 1:50 PM, Tom Lane t...@sss.pgh.pa.us wrote: Pavel Stehule pavel.steh...@gmail.com writes: ambiguous identifiers is probably the top reason of some plpgsql's mysterious errors. More times I found wrong code - sometime really important (some security checks). I never found good code with ambiguous identifiers - so for me, exception is good. But - there will be lot of working applications that contains this hidden bug - and works well. So it could be a problem. GUC should be a solution. So the conclusions so far are: (a) Nobody but me is afraid of the consequences of treating this as a GUC. (I still think you're all wrong, but so be it.) I'm afraid of it, I'm just not sure I have a better idea. It wouldn't bother me a bit if we made the only available behavior throw an error, but I'm afraid it will bother someone else. Is there a chance we could make this a GUC, but only allow it to be changed at the function level, with no way to override the server default? It seems to me that the chances of blowing up the world would be a lot lower that way, though possibly still not low enough. (b) Everybody agrees that a throw error setting would be helpful. I am not sure there's any consensus on what the default setting should be, though. Can we get away with making the default be throw error? What are the probabilities that the OpenACSes of the world will just set the value to backward compatible instead of touching their code? Do we need/want a hack in pg_dump to attach a SET to functions dumped from old DBs? I've already commented on most of these (recap: yes, very high, yes) so I'll refrain from beating a dead horse. ...Robert -- 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] Controlling changes in plpgsql variable resolution
Robert Haas robertmh...@gmail.com writes: On Mon, Oct 19, 2009 at 1:50 PM, Tom Lane t...@sss.pgh.pa.us wrote: (a) Nobody but me is afraid of the consequences of treating this as a GUC. (I still think you're all wrong, but so be it.) I'm afraid of it, I'm just not sure I have a better idea. It wouldn't bother me a bit if we made the only available behavior throw an error, but I'm afraid it will bother someone else. Is there a chance we could make this a GUC, but only allow it to be changed at the function level, with no way to override the server default? It seems to me that the chances of blowing up the world would be a lot lower that way, though possibly still not low enough. I don't particularly care to invent a new GUC class just for this, but if we think the issue is important enough, we could (a) make the GUC superuser-only (b) invent a #option or similar syntax to override the GUC per-function. regards, tom lane -- 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] Controlling changes in plpgsql variable resolution
Tom Lane t...@sss.pgh.pa.us wrote: (a) Nobody but me is afraid of the consequences of treating this as a GUC. Well, it seems dangerous to me, but I'm confident we can cover this within our shop, so I'm reluctant to take a position on it. I guess the main question is whether we want to allow an Oracle-compatibility mode, knowing it's a foot-gun. Without it we'd likely make extra work for someone converting from Oracle to PostgreSQL, although they would be likely to fix bugs during the cleanup work. Based on previous decisions I've seen here, I would have expected people to just go with an error, period; especially since it would simplify the code. (b) Everybody agrees that a throw error setting would be helpful. That's the only setting I would use on any of our databases, if it were a GUC. -Kevin -- 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] Controlling changes in plpgsql variable resolution
Tom Lane wrote: (a) Nobody but me is afraid of the consequences of treating this as a GUC. (I still think you're all wrong, but so be it.) I can't say I'm happy about it. For one thing, the granularity seems all wrong. I'd rather be able to keep backwards compatibility on a function by function basis. Or would the value of the GUC at the time the function was created stick? What are the probabilities that the OpenACSes of the world will just set the value to backward compatible instead of touching their code? Quite high, I should say. cheers andrew -- 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] postgres 8.3.8 and Solaris 10_x86 64 bit problems?
# ./pg_ctl ld.so.1: pg_ctl: fatal: relocation error: R_AMD64_32: file /usr/local/postgres64/lib/libpq.so.5: symbol (unknown): value 0xfd7fff1cf210 does not fit Killed symbol (unknown). Can you turn on debugging symbols? Knowing the symbol may point to a library that was not compiled properly. So I run 'ldd pg_ctl' to see if everything is linking ok. And I'm wondering if there is a problem with libpq.so.5 as mentioned in the original error # file /usr/local/postgres64/lib/libpq.so.5 /usr/local/postgres64/lib/libpq.so.5: ELF 64-bit LSB dynamic lib AMD64 Version 1 [SSE CMOV], dynamically linked, not stripped Ok. So looking good. Maybe there is a library or header libpq needs that I'm missing in 64 bit? # ldd /usr/local/postgres64/lib/libpq.so.5 Are you sure that all pg_ctl referenced libraries and all libpq.so referenced libraries were built as 64-bit using PIC? Are you linking with any static library that may contain 32-bit objects? That error is most commonly PIC or arch-mismatch. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- 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] Controlling changes in plpgsql variable resolution
Merlin Moncure mmonc...@gmail.com writes: Maybe invent a new language handler? plpgsql2 or shorten to pgsql? Now you can mess around all you want (and maybe fix some other compatibility warts at the same time). Well, pl/psm is out there, and might even make it into core someday. I don't find a lot of attraction in inventing a new language type that's only marginally different from plpgsql --- that approach doesn't scale up to handling multiple compatibility issues, at least not unless you fix them all at the same time. regards, tom lane -- 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] Controlling changes in plpgsql variable resolution
Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: (a) Nobody but me is afraid of the consequences of treating this as a GUC. (I still think you're all wrong, but so be it.) I can't say I'm happy about it. For one thing, the granularity seems all wrong. I'd rather be able to keep backwards compatibility on a function by function basis. Or would the value of the GUC at the time the function was created stick? Again, I can't see making a GUC that works fundamentally differently from the rest of them. Given this round of feedback, I make the following proposal: 1. Invent a GUC that has the settings backwards-compatible, oracle-compatible, throw-error (exact spellings TBD). Factory default, at least for a few releases, will be throw-error. Make it SUSET so that unprivileged users can't break things by twiddling it; but it's still possible for the DBA to set it per-database or per-user. 2. Also invent a #option syntax that allows the GUC to be overridden per-function. (Since the main GUC is SUSET, we can't just use a per-function SET to override it. There are other ways we could do this but none seem less ugly than #option...) Given that the global default will be throw-error, I don't feel a need to kluge up pg_dump to insert #option in old function definitions; that's ugly and there are too many cases it would not cover. But that could be added to this proposal if folks feel strongly enough. regards, tom lane -- 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] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 11:47 AM, Tom Lane wrote: 1. Invent a GUC that has the settings backwards-compatible, oracle-compatible, throw-error (exact spellings TBD). Factory default, at least for a few releases, will be throw-error. Make it SUSET so that unprivileged users can't break things by twiddling it; but it's still possible for the DBA to set it per-database or per-user. 2. Also invent a #option syntax that allows the GUC to be overridden per-function. (Since the main GUC is SUSET, we can't just use a per-function SET to override it. There are other ways we could do this but none seem less ugly than #option...) What about adopting the modifier syntax you're adding to COPY? David -- 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] Controlling changes in plpgsql variable resolution
David E. Wheeler da...@kineticode.com writes: On Oct 19, 2009, at 11:47 AM, Tom Lane wrote: 2. Also invent a #option syntax that allows the GUC to be overridden per-function. (Since the main GUC is SUSET, we can't just use a per-function SET to override it. There are other ways we could do this but none seem less ugly than #option...) What about adopting the modifier syntax you're adding to COPY? Where exactly would you put the modifier, and why is that better than the existing #option convention? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers