Re: proposal: hide application_name from other users - Mailing list pgsql-hackers

On Fri, Jan 24, 2014 at 6:46 AM, Magnus Hagander <magnus@hagander.net> wrote:
>
> On Thu, Jan 23, 2014 at 2:01 AM, Greg Stark <stark@mit.edu> wrote:
>>
>> On Wed, Jan 22, 2014 at 1:03 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> > Probably Heroku has some more specific exploit case to be concerned
>> > about here; if so, might I suggest taking it up with the -security list?
>>
>> I don't think there's a specific vulnerability that needs to be kept
>> secret here.
>>
>> Here's an example. I just created a new "hobby" database which is on a
>> multi-tenant cluster and ran select * from pg_stat_activity. Here are
>> two of the more interesting examples:
>>
>>  463752 | de5nmf0gbii3u5 | 32250 |   463751 | qspfkgrwgqtbcu | unicorn
>> worker[1] -p 30390 -c ./config/unicorn.rb |                |
>>       |             |                              |
>>             |                               |
>>      |         |        | <insufficient privilege>
>>  463752 | de5nmf0gbii3u5 | 32244 |   463751 | qspfkgrwgqtbcu | unicorn
>> worker[0] -p 30390 -c ./config/unicorn.rb |                |
>>       |             |                              |
>>             |                               |
>>      |         |        | <insufficient privilege>
>>
>>
>> Note that the contents of the ARGV array are being set by the
>> "unicorn" task queuing library. It knows it's making this information
>> visible to other users with shell access on this machine. But the
>> decision to stuff the ARGV information into the application_name is
>> being made by the Pg driver. Neither is under the control of the
>> application author who may not even be aware this is happening.
>> Neither component has the complete information to make a competent
>> decision about whether this information is safe to be in
>> application_name or not.
>>
>> Note that the query is showing as "<insufficient privilege>" even
>> though it is listed in the ps output -- the same ps output that is
>> listing the unicorn ARGV that is being shown in the
>> application_name....
>>
>> You might say that the Pg gem is at fault for making a blanket policy
>> decision for applications that the ARGV is safe to show to other
>> database users but realistically it's so useful to see this
>> information for your own connections that it's probably the right
>> decision. Without it it's awfully hard to tell which worker is on
>> which connection. It would just be nice to be able to treat
>> application_name the same as query.
>
>
> I would say that yes, this is clearly broken in the Pg gem. I can see it
> having such a default, but not allowing an override...

Uhm, it does allow an override as I said before.

>
> The application can of course issue a SET application_name, assuming there
> is a hook somewhere in the system that will run after the connection has
> been established. I've had customers use that many times in java based
> systems for example, but I don't know enough about the pg gem, or unicorn,
> to have a clue if anything like it exists there. This is also a good way to
> track how connections are used throughout a pooled system where the same
> connection might be used for different things at different times.
>
> What actually happens if you set the application_name in the connection
> string in that environment? Does it override it to it's own default? If so,
> the developers there clearly need to be taught about
> fallback_application_name.

It can be overridden using any of these methods. It does in fact use
fallback_application_name when it defaults to $0, see
https://bitbucket.org/ged/ruby-pg/src/6c2444dc63e17eb695363993e8887cc5d67750bc/lib/pg/connection.rb?at=default#cl-46

>
> And what happens if you set it in PGAPPNAME?

It works fine:

```
PGAPPNAME=this_is_a_custom_app_name ruby -w -rpg -e
"PG.connect(dbname: 'hgmnz', host: 'localhost').exec('SELECT
application_name FROM pg_stat_activity') { |res| res.each { |row| puts
row.values_at('application_name') } }"
this_is_a_custom_app_name
```

-Harold