Application name patch - v2
in [PgSql]
|
Prev: Hot standby status
Next: per table random-page-cost?
From: Dave Page on 19 Oct 2009 06:16 Updated patch attached, fixing a silly thinko. On Fri, Oct 16, 2009 at 12:58 PM, Dave Page <dpage(a)pgadmin.org> wrote: > I believe the attached patch is ready for review at the next > commitfest. It does the following: > > - Adds a userset GUC called application_name. > - Allows application_name to be reported in log_line_prefix using %a. > - Includes application_name in CSV logs. > - Displays application_name in the output of pg_stat_activity and > pg_stat_get_activity(). > - Adds a PQconnectdb connection string option called application_name > and corresponding envvar PGAPPLICATIONNAME to allow application_name > to be set during connection. > - Adds a PQconnectdb connection string option called > fallback_application_name to allow applications to specify a default > application_name value that can be overriden by PGAPPLICATIONNAME. > - Ensures that the application_name value is processed early in the > connection phase to ensure it can be used in connection log messages. > - Includes docs > > I think that covers all the suggestions discussed over the last couple > of days, with the exception of the rejection of \n and similar > characters which I'm still not entirely convinced is worth the effort. > Any other opinions on that? Anything else that should be > added/changed? > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com > -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
From: Peter Eisentraut on 19 Oct 2009 06:21 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(a)gmx.net> wrote: > > On Fri, 2009-10-16 at 12:58 +0100, Dave Page wrote: > >> I think that covers all the suggestions discussed over the last couple > >> of days, with the exception of the rejection of \n and similar > >> characters which I'm still not entirely convinced is worth the effort. > >> Any other opinions on that? Anything else that should be > >> added/changed? > > > > So this would effectively allow any minimally authorized user to write > > whatever they want into the log file whenever they want? Doesn't sound > > very safe to me. > > A user can do that anyway if query logging is turned on, but anyway, > what would you suggest - accept a-zA-Z0-9 and a few other choice > characters only, or just reject a handful (and if so, what)? Well, either you make the thing wide open and thus pretty insecure and unreliable, or you put in arbitrary limits which will possibly upset many users, or you design some fairly complex rules about what is allowed or not in what context. At which point you might realize that you can pretty much do all of this already in a much better way: Create a user account for each application or group of applications and assign them the roles that you are currently using as login users. The user names already show up in all the places that people want: ps, log, activity tables. And moreover, the admin can control exactly who is allowed to use what user name in what context, so there is no log spamming or confusing one's identity. -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Dave Page on 19 Oct 2009 06:39 On Mon, Oct 19, 2009 at 11:21 AM, Peter Eisentraut <peter_e(a)gmx.net> wrote: >> A user can do that anyway if query logging is turned on, but anyway, >> what would you suggest - accept a-zA-Z0-9 and a few other choice >> characters only, or just reject a handful (and if so, what)? > > Well, either you make the thing wide open and thus pretty insecure and > unreliable, or you put in arbitrary limits which will possibly upset > many users, or you design some fairly complex rules about what is > allowed or not in what context. > > At which point you might realize that you can pretty much do all of this > already in a much better way: Create a user account for each application > or group of applications and assign them the roles that you are > currently using as login users. The user names already show up in all > the places that people want: ps, log, activity tables. And moreover, > the admin can control exactly who is allowed to use what user name in > what context, so there is no log spamming or confusing one's identity. Excuse me one moment whilst I pick myself up from the floor :-) Can you imagine what a maintenance nightmare that would soon become? I might need a role for running the nightly backup, one for a weekly backup, one for each of a dozen data import/export tasks. What about a system supporting multiple applications? I used to have a dozen or more running on one server, with a hundred plus users, many of whom used 2 or 3 applications, some of who would also use reporting tools such as Crystal Reports in addition to the primary application. I'd need to give those users half a dozen or more roles each (which probably won't work nicely in my SSO environment). Please bear in mind that this feature is based on similar features in other DBMSs (and in fact, a feature in the JDBC spec) that people have asked for on a number of occasions. It's not a random idea I've come up with - my aim is to create a comparable feature to that which people may be accustomed to, in a secure and PostgreSQL-applicable way. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Pavel Stehule on 19 Oct 2009 07:33 2009/10/19 Dave Page <dpage(a)pgadmin.org>: > On Mon, Oct 19, 2009 at 10:45 AM, Pavel Stehule <pavel.stehule(a)gmail.com> wrote: > >> sure, you have to fix fulnerable application. But with some >> unsophisticated using %a and using wrong tools, the people can be >> blind and don't register an SQL injection attack. > > If they're logging the statements (which they presumably are if > looking for unusual activity), then they'll see the attack: > > dpage(a)myapp: LOG: Â connection authorized: user=dpage database=postgres > dpage(a)myapp: LOG: Â statement: set application_name='hax0red'; > dpage(a)hax0red: LOG: Â disconnection: session time: 0:00:20.152 > user=dpage database=postgres host=[local] > this is bad solution. yes, I can found probmlematics rows, but I'll get ten or more larger log. This is available only when loging of application name changes depend on own configuration setting. Regards Pavel > > -- > Dave Page > EnterpriseDB UK: Â http://www.enterprisedb.com > -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Pavel Stehule on 19 Oct 2009 07:35
2009/10/19 Pavel Stehule <pavel.stehule(a)gmail.com>: > 2009/10/19 Dave Page <dpage(a)pgadmin.org>: >> On Mon, Oct 19, 2009 at 10:45 AM, Pavel Stehule <pavel.stehule(a)gmail.com> wrote: >> >>> sure, you have to fix fulnerable application. But with some >>> unsophisticated using %a and using wrong tools, the people can be >>> blind and don't register an SQL injection attack. >> >> If they're logging the statements (which they presumably are if >> looking for unusual activity), then they'll see the attack: >> >> dpage(a)myapp: LOG: Â connection authorized: user=dpage database=postgres >> dpage(a)myapp: LOG: Â statement: set application_name='hax0red'; >> dpage(a)hax0red: LOG: Â disconnection: session time: 0:00:20.152 >> user=dpage database=postgres host=[local] >> > > this is bad solution. yes, I can found probmlematics rows, but I'll > get ten or more larger log. This is available only when loging of > application name changes depend on own configuration setting. > what is +/- same as GUC for write access to application name. Pavel > Regards > Pavel >> >> -- >> Dave Page >> EnterpriseDB UK: Â http://www.enterprisedb.com >> > -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |