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
Attachments:
appname-v2.diffapplication/octet-stream; name=appname-v2.diffDownload+224-74
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@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
2009/10/19 Dave Page <dpage@pgadmin.org>:
On Mon, Oct 19, 2009 at 8:37 AM, Peter Eisentraut <peter_e@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
Show quoted text
--
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
On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule <pavel.stehule@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
Pavel Stehule wrote:
2009/10/19 Dave Page <dpage@pgadmin.org>:
On Mon, Oct 19, 2009 at 8:37 AM, Peter Eisentraut <peter_e@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
2009/10/19 Dave Page <dpage@pgadmin.org>:
On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule <pavel.stehule@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
Show quoted text
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
2009/10/19 Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>:
Pavel Stehule wrote:
2009/10/19 Dave Page <dpage@pgadmin.org>:
On Mon, Oct 19, 2009 at 8:37 AM, Peter Eisentraut <peter_e@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
Show quoted text
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Mon, Oct 19, 2009 at 9:23 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2009/10/19 Dave Page <dpage@pgadmin.org>:
On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule <pavel.stehule@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
2009/10/19 Dave Page <dpage@pgadmin.org>:
On Mon, Oct 19, 2009 at 9:23 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2009/10/19 Dave Page <dpage@pgadmin.org>:
On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule <pavel.stehule@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
Show quoted text
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
Pavel Stehule wrote:
2009/10/19 Dave Page <dpage@pgadmin.org>:
On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule <pavel.stehule@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
On Mon, Oct 19, 2009 at 9:36 AM, Pavel Stehule <pavel.stehule@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
2009/10/19 Andrew Dunstan <andrew@dunslane.net>:
Pavel Stehule wrote:
2009/10/19 Dave Page <dpage@pgadmin.org>:
On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule <pavel.stehule@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.
Show quoted text
cheers
andrew
2009/10/19 Dave Page <dpage@pgadmin.org>:
On Mon, Oct 19, 2009 at 9:36 AM, Pavel Stehule <pavel.stehule@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
Show quoted text
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
On Mon, Oct 19, 2009 at 10:01 AM, Pavel Stehule <pavel.stehule@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
Pavel Stehule wrote:
2009/10/19 Andrew Dunstan <andrew@dunslane.net>:
Pavel Stehule wrote:
2009/10/19 Dave Page <dpage@pgadmin.org>:
On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule <pavel.stehule@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
2009/10/19 Dave Page <dpage@pgadmin.org>:
On Mon, Oct 19, 2009 at 10:01 AM, Pavel Stehule <pavel.stehule@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
Show quoted text
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
2009/10/19 Andrew Dunstan <andrew@dunslane.net>:
Pavel Stehule wrote:
2009/10/19 Andrew Dunstan <andrew@dunslane.net>:
Pavel Stehule wrote:
2009/10/19 Dave Page <dpage@pgadmin.org>:
On Mon, Oct 19, 2009 at 8:54 AM, Pavel Stehule
<pavel.stehule@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
Show quoted text
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.stehule@gmail.com> wrote:
2009/10/19 Dave Page <dpage@pgadmin.org>:
On Mon, Oct 19, 2009 at 10:01 AM, Pavel Stehule <pavel.stehule@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
Andrew Dunstan <andrew@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
