在PostgreSQL和Dynamics 365 Web API之间构建Python3验证代理服务器 [英] Building Python3 auth proxy server between PostgreSQL and Dynamics 365 Web API

问题描述

经过几天的进展，我正在考虑到我缺乏知识或技能水平，将所有这些作品整理在一起，完成这个项目。因此，我呼吁并感谢任何可以帮助我的人。

After several days of some progress, I am coming to terms with the fact I lack the knowledge, or level of skill, to put all of these pieces together and finish this project. Thus, I am appealing to, and grateful to, anyone who can help me out with this.

技术

• CentOS 7.5


• Python 3.6.0


• Django 1.10.5


• 具有最新客户端数据的Microsoft CRM Dynamics 365在线，因此必须使用Web API： https://msdn.microsoft.com/en-us/library/gg334767.aspx

• CentOS 7.5
• Python 3.6.0
• Django 1.10.5
• PostreSQL 9.2
• Microsoft CRM Dynamics 365 online which has most current client data, thus have to use the Web API: https://msdn.microsoft.com/en-us/library/gg334767.aspx

问题

• CRM拥有最新的客户端数据，并希望将其带入PostgreSQL以用于众多的东西


• 要使用 www_fdw ，因为它是PostgreSQL看到的唯一可以使用Web API的外部数据包装器： https://github.com / cyga / www_fdw / wiki


• Dynamics Web API使用OA uth2和 www_fdw 本身不支持任何类型的身份验证


• 与 www_fdw 谁建议使用代理服务器来处理与Microsoft的OAuth2身份验证


• PostgreSQL与 www_fdw 会与代理商谈话，这将反过来将身份验证发送给Microsoft，最终能够将Web API视为外部表，以便像其他表格一样对待。

• CRM has the most current client data in it and want to bring it into PostgreSQL to use for numerous things
• Want to use www_fdw since it is the only foreign data wrapper I have seen for PostgreSQL than can use Web API's: https://github.com/cyga/www_fdw/wiki
• The Dynamics Web API uses OAuth2 and www_fdw does not support any type of authentication natively
• Talked to the dev of www_fdw who recommended making a proxy server to handle the OAuth2 authentication with Microsoft
• PostgreSQL with www_fdw would talk to the proxy, which would in turn send authentication to Microsoft culminating in the ability to treat the Web API as a Foreign Table so that it is treated like any other table

三部分以及迄今为止所尝试的内容

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

1. www_fdw ：我已根据以下参数进行安装： https://github.com / cyga / www_fdw / wiki /示例

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;

crmproxytest.py 代理服务器，我一直在尝试使用这个链接来制作一个裸机： 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()

这似乎工作，因为它说在控制台上的端口12345 上运行，显示运行 nmap -sT -O localhost ，控制台上有一些活动运行运行 nmap 时的服务器。否则无法从中获取任何活动。

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.

运行 PostgreSQL中的SELECT * FROM accounts 导致无法从服务器获取响应：无法连接到:: 1：权限被拒绝。

的OAuth2 。我把它放在一起 crm.py ，并在与Microsoft交谈后排序文档，并找到以下链接： http://alexanderdevelopment.net/post/2016/11/ 27 / dynamics-365-and-python-integration-using-the-web-api /

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/

简而言之，你必须注册你的应用程序Azure Active Directory，以便您可以获得 client_id ， client_secret ，除了能够获取 OAuth 2.0令牌URI 和 OAuth 2.0授权URI 。然后，您可以向 authorizationendpoint 发送请求，如果凭据匹配返回一个令牌，则该令牌将被发送到 tokenendpoint 并最终授予对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.

这是我最终用到的代码，从Dynamics Web API中检索数据，并填充它在控制台中：

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')

这样做就像一个魅力，但实际上是用来测试OAuth2用户。变量 crmwebapi 和 crmwebapiquery 的组合查询并不是真的需要在PostgreSQL中，如果FDW正在工作，应该允许针对Web API运行SQL查询。

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 可能需要组合，但不知道如何。

Anyway, I hope I explained this all well enough. It seems that I got three independent pieces of the puzzle that work, or kind of work, but bringing it all together is where I am stuck. crm.py and crmtest.py probably need to be combined, but not sure how.

提前感谢帮助！

编辑：显然已经有$ code> www_ftw 而不是 www_fdw 。

Apparently had www_ftw everywhere instead of www_fdw.

推荐答案

在步骤1中设置FDW对我而言

Setting up the FDW in Step 1 looks okay to me.

您在第2步中的Python脚本需要顶部的一个shebang。否则将被视为bash，因此前3行运行 import（1 ） ，并将屏幕截图保存到名为 http.server ， socketserver ，和 urllib 。在 PORT 行之前，脚本会停止一段时间。在此期间（甚至死后），只运行 curl http：// localhost：12345 给出与Postgres相同的错误：

Your Python script in Step 2 needs a shebang at the top. Otherwise it is treated as bash, so the first 3 lines run import(1) and save screenshots to new files named http.server, socketserver, and urllib. That keeps the script busy for a little while before it dies on the PORT line. In the meantime (or even after it dies), running just curl http://localhost:12345 gives the same error as Postgres:

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

添加＃！/ usr / bin / env python3 后，您的脚本将会回复请求。例如我可以说 curl http：// localhost：12345 / etc / passwd 并得到结果。

After adding #!/usr/bin/env python3 your script will answer requests. For instance I can say curl http://localhost:12345/etc/passwd and get a result.

我不知道你打算如何连接第3步（OAuth），但是希望这个答案可以让你过去阻碍你的目标。

I'm not sure how you intend to wire up Step 3 (the OAuth), but hopefully this answer will get you past what is blocking you right now.

这篇关于在PostgreSQL和Dynamics 365 Web API之间构建Python3验证代理服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！