date:20091019

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

2009-10-19 Thread Heikki Linnakangas

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

2009-10-19 Thread daveg

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

2009-10-19 Thread daveg

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

2009-10-19 Thread Dimitri Fontaine

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

2009-10-19 Thread Albe Laurenz

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

2009-10-19 Thread Alex Brasetvik



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?

2009-10-19 Thread Andrew Chernow

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

2009-10-19 Thread David Fetter

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

2009-10-19 Thread Marko Tiikkaja


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

2009-10-19 Thread Massa, Harald Armin

 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

2009-10-19 Thread Alvaro Herrera

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

2009-10-19 Thread Alvaro Herrera

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

2009-10-19 Thread Simon Riggs

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

2009-10-19 Thread Alvaro Herrera

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 Thread Dean Rasheed

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

2009-10-19 Thread Merlin Moncure

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?

2009-10-19 Thread u235sentinel


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?

2009-10-19 Thread Andrew Chernow


# ./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