在PostgreSQL和Dynamics 365 Web API之间构建Python3身份验证代理服务器

Question

经过几天的努力，我认识到我缺乏知识或技能水平，无法将所有这些部分组合起来完成这个项目。因此，我呼吁并感激任何可以帮助我的人。

技术

• CentOS 7.5
• Python 3.6.0
• Django 1.10.5
• PostreSQL 9.2
• Microsoft CRM Dynamics 365在线版，其中包含最新的客户数据，因此必须使用Web API：https://msdn.microsoft.com/en-us/library/gg334767.aspx

问题

• CRM中包含最新的客户数据，并希望将其导入PostgreSQL以供多种用途
• 希望使用www_fdw，因为它是我见过的唯一一个可以使用Web API的PostgreSQL外部数据包装器：https://github.com/cyga/www_fdw/wiki
• Dynamics Web API使用OAuth2，而www_fdw不支持任何类型的身份验证
• 与www_fdw的开发人员交谈后，建议创建一个代理服务器来处理与Microsoft的OAuth2身份验证
• 使用www_fdw的PostgreSQL将与代理服务器通信，代理服务器将向Microsoft发送身份验证，最终能够将Web API视为外部表格，使其像任何其他表格一样被处理

三个部分及已尝试的内容

三个部分= www_fdw + 代理服务器 + OAuth2

1. www_fdw: I have setup using the following parameters based on this: https://github.com/cyga/www_fdw/wiki/Examples

   DROP EXTENSION IF EXISTS www_fdw CASCADE;
   CREATE EXTENSION www_fdw;
   CREATE SERVER crm FOREIGN DATA WRAPPER www_fdw OPTIONS
       (uri 'http://localhost:12345');  -- proxy server
   CREATE USER MAPPING FOR current_user SERVER crm;
   
   -- for testing trying to get 'name' out of the CRM 'accounts' table and
      naming the foreign table the same as the table in CRM
   CREATE FOREIGN TABLE accounts (
       name varchar(255)
   ) SERVER crm;
   

2. crmproxytest.py for the proxy server, I have been trying to make a bare bones one using this link: http://effbot.org/librarybook/simplehttpserver.htm

   import socketserver
   import http.server
   import urllib
   
   PORT = 12345
   
   class Proxy(http.server.SimpleHTTPRequestHandler):
       def do_GET(self):
           self.copyfile(urllib.urlopen(self.path), self.wfile)
   
   httpd = socketserver.ForkingTCPServer(('', PORT), Proxy)
   print ("serving at port", PORT)
   httpd.serve_forever()
   

   This seems to work as it says serving at port 12345 on console, shows up running nmap -sT -O localhost, there is some activity on the console running the server when nmap is run. Otherwise can't get any activity out of it.

   Running SELECT * FROM accounts from PostgreSQL results in Can't get a response from server: Failed to connect to ::1: Permission denied.

3. OAuth2. I put together crm.py and got it working after talking to Microsoft, sorting through their documentation, and the finding this link: http://alexanderdevelopment.net/post/2016/11/27/dynamics-365-and-python-integration-using-the-web-api/

   In a nutshell, you have to register your app with Azure Active Directory so that you can get a client_id, client_secret, in addition to being able to obtain the OAuth 2.0 Token URI and the OAuth 2.0 Authorization URI. You can then send a request to the authorizationendpoint, which if the credentials match it returns an token, the token is then sent to the tokenendpoint and access to the Web API is ultimately granted.

   This is the code I ended up with that works, retrieves data from the Dynamics Web API, and populates it in the console:

   import requests  
   import json
   
   #set these values to retrieve the oauth token
   crmorg = 'https://ORG.crm.dynamics.com' #base url for crm org  
   clientid = '00000000-0000-0000-0000-000000000000' #application client id  
   client_secret = 'SUPERSECRET'
   username = '[e-mail]' #username  
   userpassword = 'qwerty' #password
   authorizationendpoint =  'https://login.windows.net/ZZZZZZZZ-ZZZZ-ZZZZ-ZZZZ-ZZZZZZZZZZZZ/oauth2/authorize'
   tokenendpoint = 'https://login.windows.net/ZZZZZZZZ-ZZZZ-ZZZZ-ZZZZ-ZZZZZZZZZZZZ/oauth2/token' #oauth token endpoint
   
   #set these values to query your crm data
   crmwebapi = 'https://ORG.api.crm.dynamics.com/api/data/v8.2' #full path to web api endpoint  
   crmwebapiquery = '/accounts?$select=name&$orderby=name' #web api query (include leading /)
   
   #build the authorization token request
   tokenpost = {  
       'client_id':clientid,
       'client_secret': client_secret,
       'resource':crmorg,
       'oauthUrl': authorizationendpoint,
       'username':username,
       'password':userpassword,
       'grant_type':'password'
       }
   
   #make the token request
   tokenres = requests.post(tokenendpoint, data=tokenpost)
   
   #check the value of tokenres
   print(tokenres)
   
   #set accesstoken variable to empty string
   accesstoken = ''
   
   #extract the access token
   try:  
       accesstoken = tokenres.json()['access_token']
   except(KeyError):  
       #handle any missing key errors
       print('Could not get access token')
   
   # check point for debugging
   # print(accesstoken)
   
   #if we have an accesstoken
   if(accesstoken!=''):  
       #prepare the crm request headers
       crmrequestheaders = {
           'Authorization': 'Bearer ' + accesstoken,
           'OData-MaxVersion': '4.0',
           'OData-Version': '4.0',
           'Accept': 'application/json',
           'Content-Type': 'application/json; charset=utf-8',
           'Prefer': 'odata.maxpagesize=500',
           'Prefer': 'odata.include-annotations=OData.Community.Display.V1.FormattedValue'
           }
   
       #make the crm request
       crmres = requests.get(crmwebapi+crmwebapiquery, headers=crmrequestheaders)
   
       try:
           #get the response json
           crmresults = crmres.json()
   
           #loop through it
           for x in crmresults['value']:
               # print (x['fullname'] + ' - ' + x['contactid'])
               print (x['name'])
       except KeyError:
           #handle any missing key errors
           print('Could not parse CRM results')
   

   This works like a charm, but is really for testing the OAuth2. The query in combination of variables crmwebapi and crmwebapiquery doesn't really need to be in there since PostgreSQL, if the FDW is working right, should allow for running SQL queries against the Web API.

无论如何，我希望我已经解释得足够清楚了。看起来我已经获得了三个独立的工作或部分工作的谜题，但把它们汇集在一起就是我的难点。crm.py和crmtest.py可能需要合并，但不确定如何合并。

提前感谢您的帮助!

编辑：显然到处都是www_ftw而不是www_fdw。

- cjones

Answer 1

我认为在步骤1中设置FDW看起来没问题。

步骤2中的Python脚本需要在顶部添加shebang。否则它会被视为bash，因此前三行运行import(1)并将屏幕截图保存到名为http.server、socketserver和urllib的新文件中。这会让脚本忙碌一段时间，然后在PORT行死机。同时（甚至在死机后），仅运行curl http://localhost:12345就会出现与Postgres相同的错误。

curl: (7) Failed to connect to localhost port 12345: Connection refused

在添加#!/usr/bin/env python3后，您的脚本将响应请求。例如，我可以说curl http://localhost:12345/etc/passwd并获得结果。

我不确定您打算如何连接第三步（OAuth），但希望这个答案能帮助您克服当前的障碍。

- Paul A Jungwirth

---

嗨，保罗，我会按照你今天早上建议的去做。我认为目前我最大的问题是将步骤2和3结合起来：设置一个持久进程，使Postgres可以与之通信，从而调解Microsoft的OAuth2授权，以便使用外部数据包装器查询Dynamics CRM Web API。 - cjones

@sockpuppet，Paul的回答有帮助吗？如果有，请确保接受它或添加评论解释为什么这不是一个解决方案。 - Matthew Purdon