Re: Application name patch - v2

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

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.

On Mon, Oct 19, 2009 at 8:37 AM, Peter Eisentraut <peter_e(at)gmx(dot)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

2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
> On Mon, Oct 19, 2009 at 8:37 AM, Peter Eisentraut <peter_e(at)gmx(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

Pavel Stehule wrote:
> 2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
>> On Mon, Oct 19, 2009 at 8:37 AM, Peter Eisentraut <peter_e(at)gmx(dot)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

2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
> On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
>

2009/10/19 Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>:
> Pavel Stehule wrote:
>> 2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
>>> On Mon, Oct 19, 2009 at 8:37 AM, Peter Eisentraut <peter_e(at)gmx(dot)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
>

On Mon, Oct 19, 2009 at 9:23 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
>> On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
> On Mon, Oct 19, 2009 at 9:23 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
>>> On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
>

Pavel Stehule wrote:
> 2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
>
>> On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

On Mon, Oct 19, 2009 at 9:36 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

2009/10/19 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
>
> Pavel Stehule wrote:
>>
>> 2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
>>
>>>
>>> On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
>
>
>

2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
> On Mon, Oct 19, 2009 at 9:36 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
>

On Mon, Oct 19, 2009 at 10:01 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

Pavel Stehule wrote:
> 2009/10/19 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
>> Pavel Stehule wrote:
>>
>>> 2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
>>>
>>>
>>>> On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
> On Mon, Oct 19, 2009 at 10:01 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
>

2009/10/19 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
>
> Pavel Stehule wrote:
>>
>> 2009/10/19 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>>
>>>
>>> Pavel Stehule wrote:
>>>
>>>>
>>>> 2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
>>>>
>>>>
>>>>>
>>>>> On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule
>>>>> <pavel(dot)stehule(at)gmail(dot)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
>

On Mon, Oct 19, 2009 at 10:22 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
>> On Mon, Oct 19, 2009 at 10:01 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

Andrew Dunstan <andrew(at)dunslane(dot)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

2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
> On Mon, Oct 19, 2009 at 10:22 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
>>> On Mon, Oct 19, 2009 at 10:01 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
>

2009/10/19 Dimitri Fontaine <dfontaine(at)hi-media(dot)com>:
> Andrew Dunstan <andrew(at)dunslane(dot)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
>

On Mon, Oct 19, 2009 at 10:45 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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:

dpage(at)myapp: LOG: connection authorized: user=dpage database=postgres
dpage(at)myapp: LOG: statement: set application_name='hax0red';
dpage(at)hax0red: LOG: disconnection: session time: 0:00:20.152
user=dpage database=postgres host=[local]

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

Updated patch attached, fixing a silly thinko.

On Fri, Oct 16, 2009 at 12:58 PM, Dave Page <dpage(at)pgadmin(dot)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

On Mon, 2009-10-19 at 08:47 +0100, Dave Page wrote:
> On Mon, Oct 19, 2009 at 8:37 AM, Peter Eisentraut <peter_e(at)gmx(dot)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.

On Mon, Oct 19, 2009 at 11:21 AM, Peter Eisentraut <peter_e(at)gmx(dot)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

2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
> On Mon, Oct 19, 2009 at 10:45 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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:
>
> dpage(at)myapp: LOG:  connection authorized: user=dpage database=postgres
> dpage(at)myapp: LOG:  statement: set application_name='hax0red';
> dpage(at)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
>

2009/10/19 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
>> On Mon, Oct 19, 2009 at 10:45 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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:
>>
>> dpage(at)myapp: LOG:  connection authorized: user=dpage database=postgres
>> dpage(at)myapp: LOG:  statement: set application_name='hax0red';
>> dpage(at)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
>>
>

On Mon, Oct 19, 2009 at 12:33 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
>> On Mon, Oct 19, 2009 at 10:45 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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:
>>
>> dpage(at)myapp: LOG:  connection authorized: user=dpage database=postgres
>> dpage(at)myapp: LOG:  statement: set application_name='hax0red';
>> dpage(at)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

2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
> On Mon, Oct 19, 2009 at 12:33 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
>>> On Mon, Oct 19, 2009 at 10:45 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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:
>>>
>>> dpage(at)myapp: LOG:  connection authorized: user=dpage database=postgres
>>> dpage(at)myapp: LOG:  statement: set application_name='hax0red';
>>> dpage(at)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
>

On Mon, Oct 19, 2009 at 12:57 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

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(dot)stehule(at)gmail(dot)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 daveg(at)sonic(dot)net 510 536 1443 510 282 0869
If simplicity worked, the world would be overrun with insects.

>
>  -- 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       daveg(at)sonic(dot)net      510 536 1443    510 282 0869
> If simplicity worked, the world would be overrun with insects.
>

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 <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

* Pavel Stehule (pavel(dot)stehule(at)gmail(dot)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

Dave Page <dpage(at)pgadmin(dot)org> writes:
> On Mon, Oct 19, 2009 at 12:57 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

* Pavel Stehule (pavel(dot)stehule(at)gmail(dot)com) wrote:
> 2009/10/19 Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)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

On Mon, Oct 19, 2009 at 3:17 PM, David Fetter <david(at)fetter(dot)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

David Fetter <david(at)fetter(dot)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

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Dave Page <dpage(at)pgadmin(dot)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

> 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

On Mon, Oct 19, 2009 at 3:42 PM, Massa, Harald Armin <chef(at)ghum(dot)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

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)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

On Mon, Oct 19, 2009 at 3:49 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)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

2009/10/19 Stephen Frost <sfrost(at)snowman(dot)net>:
> * Pavel Stehule (pavel(dot)stehule(at)gmail(dot)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-----
>
>

* Pavel Stehule (pavel(dot)stehule(at)gmail(dot)com) wrote:
> 2009/10/19 Stephen Frost <sfrost(at)snowman(dot)net>:
> > * Pavel Stehule (pavel(dot)stehule(at)gmail(dot)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

On Mon, Oct 19, 2009 at 10:36 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)org> writes:
>> On Mon, Oct 19, 2009 at 12:57 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

David Fetter <david(at)fetter(dot)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

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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/407d949e0907161237r76ebd92av6836c6563d8a230e@mail.gmail.com

2009/10/19 Stephen Frost <sfrost(at)snowman(dot)net>:
> * Pavel Stehule (pavel(dot)stehule(at)gmail(dot)com) wrote:
>> 2009/10/19 Stephen Frost <sfrost(at)snowman(dot)net>:
>> > * Pavel Stehule (pavel(dot)stehule(at)gmail(dot)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-----
>
>

2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
> On Mon, Oct 19, 2009 at 3:42 PM, Massa, Harald Armin <chef(at)ghum(dot)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].

Um, that has to be platform dependent, no? On windows for example, you
can use GetCommandLine().

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Robert Haas 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 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.

Once problem I can imagine is someone with a long log_line prefix, like
'%t %a|', and assuming that the pipe is the end of the log_prefix
arguments. If someone adds a pipe to the application name, log parsing
code will assume the %a pipe ends the log_line_prefix, and we have no
system of escaping things like pipes in log_line_prefix.

Effectively, if you use %a, there is no good way to terminate
log_line_prefix with a known unique character.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

2009/10/20 Bruce Momjian <bruce(at)momjian(dot)us>:
> Robert Haas 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 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.
>
> Once problem I can imagine is someone with a long log_line prefix, like
> '%t %a|', and assuming that the pipe is the end of the log_prefix
> arguments.  If someone adds a pipe to the application name, log parsing
> code will assume the %a pipe ends the log_line_prefix, and we have no
> system of escaping things like pipes in log_line_prefix.
>
> Effectively, if you use %a, there is no good way to terminate
> log_line_prefix with a known unique character.

If you're going to parse your logfile, you should probably be using
CSV format logs, which I believe would not have this issue...

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On Mon, Oct 19, 2009 at 9:34 PM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> 2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
>> On Mon, Oct 19, 2009 at 3:42 PM, Massa, Harald Armin <chef(at)ghum(dot)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].
>
> Um, that has to be platform dependent, no? On windows for example, you
> can use GetCommandLine().

Yeah, and there's a similar API on *BSD I believe, but nothing standard.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
PGDay.EU 2009 Conference: http://2009.pgday.eu/start

2009/10/20 Dave Page <dpage(at)pgadmin(dot)org>:
> On Mon, Oct 19, 2009 at 9:34 PM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>> 2009/10/19 Dave Page <dpage(at)pgadmin(dot)org>:
>>> On Mon, Oct 19, 2009 at 3:42 PM, Massa, Harald Armin <chef(at)ghum(dot)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].
>>
>> Um, that has to be platform dependent, no? On windows for example, you
>> can use GetCommandLine().
>
> Yeah, and there's a similar API on *BSD I believe, but nothing standard.

Right, but it might be worth investigating using the API that's
available on the platform, if one is. It's a fairly simple operation
after all, so it won't take huge amounts of platform-specific code.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> 2009/10/20 Dave Page <dpage(at)pgadmin(dot)org>:
>> Yeah, and there's a similar API on *BSD I believe, but nothing standard.

> Right, but it might be worth investigating using the API that's
> available on the platform, if one is. It's a fairly simple operation
> after all, so it won't take huge amounts of platform-specific code.

It would be a seriously bad idea for this to behave one way on some
platforms and differently on others.

regards, tom lane

Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> 2009/10/20 Dave Page <dpage(at)pgadmin(dot)org>:
>>> Yeah, and there's a similar API on *BSD I believe, but nothing standard.
>
>> Right, but it might be worth investigating using the API that's
>> available on the platform, if one is. It's a fairly simple operation
>> after all, so it won't take huge amounts of platform-specific code.
>
> It would be a seriously bad idea for this to behave one way on some
> platforms and differently on others.

Why would that be so bad? On platforms that support getting argv[0],
you'd get "mycoolapp" in the application name by default. On others,
you'd get something like "unknown libpq app". And if you care enough,
you can override it in the application or with the environment variable.
Seems quite sane and harmless to me.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> Tom Lane wrote:
>> It would be a seriously bad idea for this to behave one way on some
>> platforms and differently on others.

> Why would that be so bad? On platforms that support getting argv[0],
> you'd get "mycoolapp" in the application name by default. On others,
> you'd get something like "unknown libpq app".

Right, and if your software is written to depend on the appname being
set a particular way, you suddenly find you have a portability problem.
I don't think we should be in the business of creating hidden vendor
lock-ins.

regards, tom lane

2009/10/20 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> Tom Lane wrote:
>>> It would be a seriously bad idea for this to behave one way on some
>>> platforms and differently on others.
>
>> Why would that be so bad? On platforms that support getting argv[0],
>> you'd get "mycoolapp" in the application name by default. On others,
>> you'd get something like "unknown libpq app".
>
> Right, and if your software is written to depend on the appname being
> set a particular way, you suddenly find you have a portability problem.
> I don't think we should be in the business of creating hidden vendor
> lock-ins.

If your application depends on it being set properly, then have the
application set it. This would just be a *default*, you could still
override it - and should if it's really that important to your
application.

Also, how many platforms can't we do this on? If we have BSD and
Windows covered already. on linux, I believe you can easily read it
out of /proc/self/cmdline, no?

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> if your software is written to depend on the appname being set a
> particular way

then you're not using for its intended purpose, I should think. Since
any client can set this to whatever they want, having the application
name as a default, rather than NULL (at least for clients on Windows,
BSD, and Linux) would be a convenience for those using it for its
intended purpose.

-Kevin

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> Also, how many platforms can't we do this on? If we have BSD and
> Windows covered already. on linux, I believe you can easily read it
> out of /proc/self/cmdline, no?

Writing a pile of platform-specific code for this is simply insane from
a support point of view. The feature is NOT worth it. Especially not
since the typical result will be something quite uninformative like
"psql" or "java". The cases that are actually useful are the ones where
the application sets it. I don't think we should have a default at all
--- you don't set it, you don't get a name.

regards, tom lane

2009/10/20 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> Also, how many platforms can't we do this on? If we have BSD and
>> Windows covered already. on linux, I believe you can easily read it
>> out of /proc/self/cmdline, no?
>
> Writing a pile of platform-specific code for this is simply insane from
> a support point of view.  The feature is NOT worth it.  Especially not
> since the typical result will be something quite uninformative like
> "psql" or "java".  The cases that are actually useful are the ones where
> the application sets it.  I don't think we should have a default at all
> --- you don't set it, you don't get a name.

For psql, yes.
For java, it doesn't even go through libpq, so it wouldn't be set for
it. And I'd expect the JDBC driver to set it based on Something
Reasonable (TM) that it can get the information about. After all, this
thing was listed in the JDBC spec somebody said...

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> 2009/10/20 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> "psql" or "java".  The cases that are actually useful are the ones where
>> the application sets it.  I don't think we should have a default at all
>> --- you don't set it, you don't get a name.
>
> For psql, yes.

What about having psql -f foo.sql using 'psql: foo.sql' or sth as the
default value?
--
dim

On Tue, Oct 20, 2009 at 12:16:42PM -0400, Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
> > Also, how many platforms can't we do this on? If we have BSD and
> > Windows covered already. on linux, I believe you can easily read it
> > out of /proc/self/cmdline, no?
>
> Writing a pile of platform-specific code for this is simply insane from
> a support point of view. The feature is NOT worth it. Especially not
> since the typical result will be something quite uninformative like
> "psql" or "java". The cases that are actually useful are the ones where
> the application sets it. I don't think we should have a default at all
> --- you don't set it, you don't get a name.

-1

I'd like a default, especially for psql, to help identify interactive sessions.

-dg

--
David Gould daveg(at)sonic(dot)net 510 536 1443 510 282 0869
If simplicity worked, the world would be overrun with insects.

daveg <daveg(at)sonic(dot)net> writes:
> I'd like a default, especially for psql, to help identify interactive sessions.

psql can certainly provide a default, and maybe even do something
actually useful like report the -f file it's running. The question here
is whether it is worth the trouble for libpq to try to report an appname
*without* the surrounding application's cooperation. I don't think so.
You are talking dozens if not hundreds of lines of unportable code,
versus one line (per app) of entirely portable code.

regards, tom lane

Magnus Hagander <magnus(at)hagander(dot)net> wrote:

> For java, it doesn't even go through libpq, so it wouldn't be set
> for it. And I'd expect the JDBC driver to set it based on Something
> Reasonable (TM) that it can get the information about. After all,
> this thing was listed in the JDBC spec somebody said...

I can't see a good way to get a meaningful default from within the
bowels of the JDBC driver, unless it's taking a system property or
environment variable. First off, generating a stack trace to try to
pick up a class name would be too expensive to be something you'd want
to do by default when opening a connection. For another, many
applications are started from a bootstrap class (for good reason), so
you can't just take the initial class name for a meaningful default.
If you were running a GUI application you might try to find the title
of some window frame, but that's pretty ugly.

In short, I'd suggest supporting a system property for a default name.
(For those unfamiliar with Java, a system property is very much like
an environment variable, but just for the one Java Virtual Machine.)
That seems more sane than an environment variable for the Java world.
I don't see much other reasonable alternative, except of course that
you should be able to override it when setting up any individual
connection.

-Kevin

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>> For java, it doesn't even go through libpq, so it wouldn't be set
>> for it. And I'd expect the JDBC driver to set it based on Something
>> Reasonable (TM) that it can get the information about. After all,
>> this thing was listed in the JDBC spec somebody said...

> I can't see a good way to get a meaningful default from within the
> bowels of the JDBC driver, unless it's taking a system property or
> environment variable.

[ scratches head... ] I thought the JDBC spec already said exactly how
one would set this. Why would we go to significant effort to make it
behave contrary to spec?

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> [ scratches head... ] I thought the JDBC spec already said exactly
> how one would set this. Why would we go to significant effort to
> make it behave contrary to spec?

We certainly should allow it to be set as specified in the spec. The
only question is whether it makes sense to provide a non-null default
if it is not set in that way. I thought Magnus was arguing for that.
I have no objection, and see potential use-cases where that would be
convenient.

To illustrate what I'm talking about, adding something like this to
the command line which starts Java would provide the non-null default:

-DPGAPPNAME="Receipting - Traffic"

(or whatever name we choose for this in place of PGAPPNAME.)
This seems similar to what is proposed for libpq.

The effort to support that would not be significant -- something
along the order of

if (appName == NULL)
appName = System.getProperty("PGAPPNAME");

Do you object?

-Kevin

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> (or whatever name we choose for this in place of PGAPPNAME.)

FWIW, I would prefer PGAPPNAME to PGAPPLICATIONNAME which is what
Dave has been using in his examples. The latter is too frickin
long, and the double N is a typo threat (I already mistyped it in
composing this message...)

regards, tom lane

Tom Lane wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>
>> (or whatever name we choose for this in place of PGAPPNAME.)
>>
>
> FWIW, I would prefer PGAPPNAME to PGAPPLICATIONNAME which is what
> Dave has been using in his examples. The latter is too frickin
> long, and the double N is a typo threat (I already mistyped it in
> composing this message...)
>
>
>

Well, the latter argument suggests we should use an underscore ...

We don't usually use abbreviations, so how about PGCLIENTNAME or some such?

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Tom Lane wrote:
>> FWIW, I would prefer PGAPPNAME to PGAPPLICATIONNAME which is what

> We don't usually use abbreviations, so how about PGCLIENTNAME or some such?

Not sure I believe that argument. Among the set of existing libpq
environment variables I see

PGHOSTADDR
PGSSLCERT
PGSSLCRL
PGKRBSRVNAME
PGTZ
PGSYSCONFDIR

so it can hardly be said that there's a policy of avoiding
abbreviations. PGCLIENTNAME would be better than PGAPPLICATIONNAME
I guess, but I still prefer the other.

regards, tom lane

On Wed, Oct 21, 2009 at 11:25 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> Tom Lane wrote:
>>> FWIW, I would prefer PGAPPNAME to PGAPPLICATIONNAME which is what
>
>> We don't usually use abbreviations, so how about PGCLIENTNAME  or some such?
>
> Not sure I believe that argument.  Among the set of existing libpq
> environment variables I see
>
> PGHOSTADDR
> PGSSLCERT
> PGSSLCRL
> PGKRBSRVNAME
> PGTZ
> PGSYSCONFDIR
>
> so it can hardly be said that there's a policy of avoiding
> abbreviations.  PGCLIENTNAME would be better than PGAPPLICATIONNAME
> I guess, but I still prefer the other.

I also like PGAPPNAME better, for the same reasons as Tom.

...Robert

Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>
>> Tom Lane wrote:
>>
>>> FWIW, I would prefer PGAPPNAME to PGAPPLICATIONNAME which is what
>>>
>
>
>> We don't usually use abbreviations, so how about PGCLIENTNAME or some such?
>>
>
> Not sure I believe that argument. Among the set of existing libpq
> environment variables I see
>
> PGHOSTADDR
> PGSSLCERT
> PGSSLCRL
> PGKRBSRVNAME
> PGTZ
> PGSYSCONFDIR
>
> so it can hardly be said that there's a policy of avoiding
> abbreviations. PGCLIENTNAME would be better than PGAPPLICATIONNAME
> I guess, but I still prefer the other.
>
>
>

OK. I don't have strong feelings on the subject.

cheers

andrew

On Wed, Oct 21, 2009 at 4:29 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> I also like PGAPPNAME better, for the same reasons as Tom.

:-). Have to admit, I've mistyped it a few times too.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
PGDay.EU 2009 Conference: http://2009.pgday.eu/start

Dave Page <dpage(at)pgadmin(dot)org> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
>> I also like PGAPPNAME better, for the same reasons as Tom.
>
> :-). Have to admit, I've mistyped it a few times too.

Well, it would seem we have consensus on that. :-)

I don't feel that the Java default issue reached the same level of
consensus, though. I think we can rule out anything beyond an
environment variable or system property as a non-null default. The
options seem to be:

(1) No non-null default. If they don't set it through the standard
techniques, the default is null.

(2) Use an environment variable.

(3) Use a system property.

(4) Some combination of (2) and (3), with one having precedence over
the other.

Here's what the Java documentation has to say about environment
variables versus system properties:

| System properties and environment variables are both conceptually
| mappings between names and values. Both mechanisms can be used to
| pass user-defined information to a Java process. Environment
| variables have a more global effect, because they are visible to all
| descendants of the process which defines them, not just the
| immediate Java subprocess. They can have subtly different semantics,
| such as case insensitivity, on different operating systems. For
| these reasons, environment variables are more likely to have
| unintended side effects. It is best to use system properties where
| possible. Environment variables should be used when a global effect
| is desired, or when an external system interface requires an
| environment variable (such as PATH).

It would be zero lines of programming to support *setting* either or
both, just documenting it and very simple code to use either. For
example, assuming the code to support the standard setting is written,
we could allow both defaults for cases where it isn't explicitly set,
with precedence given to the system property; and the entire
programming effort would look something like this:

if (appName == null)
appName = System.getProperty("PGAPPNAME",
System.getenv("PGAPPNAME"));

So the coding involved isn't overwhelming. :-)

The primary use case would be to allow someone with an existing
application to set this without changing or recompiling any Java code
-- they could just set the value in the launch script.

Does anyone have an opinion on this?

-Kevin