Build a Python3 Auth Proxy Server between PostgreSQL and Dynamics 365 Web API

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 s erver
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;

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

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 ='[email protected]' #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 thesevalues ​​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')
< br /># 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'])
 e xcept KeyError:
 #handle any missing key errors
 print('Could not parse CRM results')

curl: (7) Failed to connect to localhost port 12345: Connection refused
 pre> 
Add #! After /usr/bin/env python3, your script will answer the request. For example, I can say curl http://localhost:12345/etc/passwd and get a result.
 
I'm not sure you intend How to connect step 3 (OAuth), but hope this answer will let you go beyond what is blocking you now.
 

After a few days of progress, I accepted my lack The fact of knowledge or skill level, put all these parts together and completed this project. Therefore, I welcome and appreciate anyone who can help me solve this problem.

Technology< /p>

> CentOS 7.5
> Python 3.6.0
> Django 1.10.5
> PostreSQL 9.2
> Microsoft CRM Dynamics 365 online, with the latest client data, so it must Use Web API: https://msdn.microsoft.com/en-us/library/gg334767.aspx

Question

> CRM has the latest client data and hopes to It introduces PostgreSQL for many things
>I want to use www_fdw, because it is the only external data wrapper for PostgreSQL that I have seen that can use Web API: https://github.com/cyga/www_fdw/wiki< br>> Dynamics Web API uses OAuth2, www_fdw itself does not support any type of authentication
>Talk to the developers of www_fdw, they recommend using a proxy server to handle OAuth2 authentication with Microsoft
>With www_fdw PostgreSQL will communicate with the agent, and the agent will send authentication to Microsoft, and finally treat the Web API as an external table so that it can be treated as any other table

These three parts and what I have tried so far Content

Three parts = www_fdw proxy server OAuth2

> www_fdw: I have used the following parameters according to this setting: https://github.com/cyga/www_fdw/wiki/Examples< /p>

DROP EXTENSION IF EXISTS www_fdw CASCADE;
CREATE EXTENSIO N 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 (< br /> name varchar(255)
) SERVER crm;

> crmproxytest.py proxy server, I have been trying to use this link to make a simple bone: 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)
< br />httpd = socketserver.ForkingTCPServer(('', PORT), Proxy)
print ("serving at port", PORT)
httpd.serve_forever()

It seems Works because it says running on port 12345 on the server, it shows that running nmap -sT -O localhost, there is some activity on the console of the running server when nmap is running. Otherwise it can't get any from it Active.

Running the SELECT * FROM account from PostgreSQL resulted in failure to get response from the server: Unable to connect to :: 1: Permission denied.
> OAuth2. I put crm.py together, and After talking with Microsoft, I worked on it, sorted out their documents, and found this link: http://alexanderdevelopment.net/post/2016/11/27/dynamics-365-and-python-integration-using -the-web-api/

In short, you must register your application with Azure Active Directory so that in addition to being able to obtain OAuth 2.0 token URI and OAuth 2.0 authorization URI, you can also Get client_id, client_secret. Then, you can send a request to authorizationendpoint, if the credentials match, return the token, then send the token to tokenendpoint, and finally grant access to the Web API.

This is me The final code used retrieves data from Dynamics Web API and fills 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 ='[email protected]' #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 it is actually testing OAuth2. The query combining the variables crmwebapi and crmwebapiquery is actually No need to be there because PostgreSQL, if FDW is working properly, it should allow SQL queries to run against Web API.

Anyway, I hope I can explain it all well. It seems I have three separate puzzles Work or work, but putting them together is where I get stuck. crm.py and crmtest.py may need to be merged, but not sure how.

Thanks for your help in advance!

Edit: Apparently there is www_ftw everywhere instead of www_fdw.

Setting up FDW in step 1 is no problem for me.

Your Python script in step 2 needs the shebang at the top. Otherwise it is treated as bash, so the first 3 lines run import(1) and save the screenshot to In new files named http.server, socketserver and urllib. This makes the script stay for a while before it dies on the PORT line. During this period (or even after it dies), run curl http://localhost:12345 will Produce the same error as Postgres:

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

Add #! After /usr/bin/env python3, your script will answer the request. For example, I can say curl http://localhost:12345/etc/passwd and get a result.

I'm not sure what you intend How to connect to step 3 (OAuth), but hope this answer will let you go beyond what is blocking you now.