PostgreSQL
Connecting to PostgreSQL
The following connection properties are usually required to connect to PostgreSQL.
Server: The host name or IP of the server hosting the PostgreSQL database.
User: The user which will be used to authenticate with the PostgreSQL server.
You can also optionally set the following:
Database: The database to connect to when connecting to the PostgreSQL Server. If this is not set, the user's default database will be used.
Port: The port of the server hosting the PostgreSQL database. 5432 by default.
Standard
Unless you select another scheme, Password is the default authentication mechanism the provider uses to connect to PostgreSQL Server.
To use standard authentication, set the AuthScheme to Password to connect to PostgreSQL with login credentials.
Then, to authenticate, set the Password associated with the authenticating user.
pg_hba.conf Auth Schemes
There are subtypes of the Password authentication scheme supported by the provider which must be enabled in the pg_hba.conf file on the PostgreSQL server.
See the PostgreSQL documentation for more information about authentication setup on the PostgreSQL Server.
MD5
The provider can authenticate by verifying the password with MD5. This authentication method must be enabled by setting the auth-method in the pg_hba.conf file to md5.
SASL
The provider can authenticate by verifying the password with SASL (particularly, SCRAM-SHA-256). This authentication method must be enabled by setting the auth-method in the pg_hba.conf file to scram-sha-256.
Azure
Methods available for connecting to PostgreSQL with Microsoft Azure include:
Azure Active Directory OAuth
Azure Active Directory Password
Azure Active Directory MSI
Azure AD
Azure AD is a connection type that leverages OAuth to authenticate. OAuth requires the authenticating user to interact with PostgreSQL using an internet browser. The provider facilitates this in several ways as described below. Set your AuthScheme to AzureAD. All AzureAD flows assume that you have done so.
See Creating a Custom AzureAD App for information about creating custom applications and reasons for doing so.
After setting the following connection properties, you are ready to connect:
InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
OAuthClientId: (custom applications only) Set this to the client Id in your application settings.
OAuthClientSecret: (custom applications only) Set this to the client secret in your application settings.
CallbackURL: Set this to the Redirect URL in your application settings.
When you connect the provider opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The provider then completes the OAuth process:
Extracts the access token from the callback URL and authenticates requests.
Obtains a new access token when the old one expires.
Saves OAuth values in OAuthSettingsLocation that persist across connections.
Admin Consent
Admin consent refers to when the Admin for an Azure Active Directory tenant grants permissions to an application which requires an admin to consent to the use case.
Admin Consent Permissions
When creating a new AzureAD app in the Azure Portal, you must specify which permissions the app will require. Some permissions may be marked as "Admin Consent Required". For example, all Groups permissions require Admin Consent. If your app requires admin consent, there are a couple of ways this can be done.
The easiest way to grant admin consent is to just have an admin log into portal.azure.com and navigate to the app you have created in App Registrations. Under API Permissions, click Grant Consent for your app to have permissions on the tenant under which it was created.
Client Credentials
Client credentials refers to a flow in OAuth where there is no direct user authentication taking place. Instead, credentials are created for just the app itself. All tasks taken by the app are done without a default user context. This makes the authentication flow a bit different from standard.
Client OAuth Flow
All permissions related to the client oauth flow require admin consent. T
In your App Registration in portal.azure.com, navigate to API Permissions and select the Microsoft Graph permissions. There are two distinct sets of permissions - Delegated and Application permissions. The permissions used during client credential authentication are under Application Permissions. Select the permissions you require for your integration.
You are ready to connect after setting one of the connection properties groups depending on the authentication type.
Authenticating using a Client Secret
InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
AzureTenant: Set this to the tenant you wish to connect to.
OAuthGrantType: Set this to CLIENT.
OAuthClientId: Set this to the client Id in your app settings.
OAuthClientSecret: Set this to the client secret in your app settings.
Authenticating using a Certificate
InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
AzureTenant: Set this to the tenant you wish to connect to.
OAuthGrantType: Set this to CLIENT.
OAuthClientId: Set this to the client Id in your app settings.
OAuthJWTCert: Set this to the JWT Certificate store.
OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.
Authentication with client credentials takes place automatically like any other connection, except there is no window opened prompting the user. Because there is no user context, there is no need for a browser popup. Connections will take place and be handled internally.
Note: Azure PostgreSQL Flexible servers are not supported. Only Azure PostgreSQL Single Server instances are supported.
Ensure that an Active Directory admin has been set in the Azure PostgreSQL instance (Active Directory admin -> Set admin).
Next, set the following to connect:
User: Set this to the Azure Active Directory user you granted access to the Azure PostgreSQL server.
AzureTenant: Set this to the Directory (tenant) ID, found on the Overview page of the OAuth app used to authenticate to PostgreSQL on Azure.
Server: Set this to the Server name of the Azure PostgreSQL server, found on the Overview page of the Azure PostgreSQL instance.
Database: Set this to the database you'd like to connect to on the Azure PostgreSQL instance.
Port: The port of the server hosting the PostgreSQL database. 5432 by default.
InitiateOAuth: Set this to GETANDREFRESH.
OAuthClientId: Set this to the Application (client) ID, found on the Overview page of the OAuth app used to authenticate to PostgreSQL on Azure.
OAuthClientSecret: Set this to the Value of the client secret, generated at the Certificates and secrets page of the authenticating OAuth app.
CallbackURL: Set this to the Redirect URI you specified during the creation of your OAuth app.
Azure Password
To connect using your Azure credentials directly, specify the following connection properties:
AuthScheme: Set this to AzurePassword.
User: Set this to your user account you use to connect to Azure.
Password: Set this to the password you use to connect to Azure.
AzureTenant: Set this to the Directory (tenant) ID, found on the Overview page of the OAuth app used to authenticate to PostgreSQL on Azure.
Server: Set this to the Server name of the Azure PostgreSQL server, found on the Overview page of the Azure PostgreSQL instance.
Database: Set this to the database you'd like to connect to on the Azure PostgreSQL instance.
Port: The port of the server hosting the PostgreSQL database. 5432 by default.
GCP Service Account
To authenticate to your PostgreSQL Google SQL Cloud Instance using a service account, you must create a new service account and have a copy of the accounts certificate. For a JSON file, set these properties:
AuthScheme: Set this to GCPServiceAccount.
InitiateOAuth: Set this to GETANDREFRESH.
OAuthJWTCertType: Set this to GOOGLEJSON.
OAuthJWTCert: Set this to the path to the .json file provided by Google.
OAuthJWTSubject: (optional) Only set this value if the service account is part of a GSuite domain and you want to enable delegation. The value of this property should be the email address of the user whose data you want to access.
For a PFX file, set these properties instead:
AuthScheme: Set this to GCPServiceAccount.
InitiateOAuth: Set this to GETANDREFRESH.
OAuthJWTCertType: Set this to PFXFILE.
OAuthJWTCert: Set this to the path to the .pfx file provided by Google.
OAuthJWTCertPassword: (optional) Set this to the .pfx file password. In most cases you must provide this since Google encrypts PFX certificates.
OAuthJWTCertSubject: (optional) Set this only if you are using a OAuthJWTCertType which stores multiple certificates. Should not be set for PFX certificates generated by Google.
OAuthJWTIssuer: Set this to the email address of the service account. This address will usually include the domain iam.gserviceaccount.com.
OAuthJWTSubject: (optional) Only set this value if the service account is part of a GSuite domain and you want to enable delegation. The value of this property should be the email address of the user whose data you want to access.
MSI
If you are running PostgreSQL on an Azure VM, you can leverage Managed Service Identity (MSI) credentials to connect:
AuthScheme: Set this to AzureMSI.
User: Set this to the Azure PostgreSQL user that maps to your managed service identity. Provide this in the format myuser@myservername, where myservername doesn't include the ".postgres.database.azure.com" part.
Server: Set this to the Server name of the Azure PostgreSQL server, found on the Overview page of the Azure PostgreSQL instance.
Database: Set this to the database you'd like to connect to on the Azure PostgreSQL instance.
Port: The port of the server hosting the PostgreSQL database. 5432 by default.
The MSI credentials are automatically obtained for authentication.
Amazon Web Services
Obtain AWS Keys
To obtain the credentials for an IAM user, follow the steps below:
Sign into the IAM console.
In the navigation pane, select Users.
To create or manage the access keys for a user, select the user and then go to the Security Credentials tab.
To obtain the credentials for your AWS root account, follow the steps below:
Sign into the AWS Management console with the credentials for your root account.
Select your account name or number and select My Security Credentials in the menu that is displayed.
Click Continue to Security Credentials and expand the "Access Keys" section to manage or create root account access keys.
AWS IAM Roles
In many situations it may be preferable to use an IAM role for authentication instead of the direct security credentials of an AWS root user.
To authenticate as an AWS role, set the following:
AuthScheme: Set this to AwsIAMRoles.
User: Set this to the AWS-hosted PostgreSQL user that you granted the aws_iam role to. This user should map to an AWS user that has a role containing a policy which includes the rds-db:connect permission.
AWSRoleARN: Specify the Role ARN for the role attached to the authenticating IAM user. This will cause the provider to attempt to retrieve credentials for the specified role.
AWSAccessKey: The access key of the authenticating IAM user.
AWSSecretKey: The secret key of the authenticating IAM user.
Note: Roles may not be used when specifying the AWSAccessKey and AWSSecretKey of an AWS root user.
Kerberos
The authentication with Kerberos is initiated by PostgreSQL Server when the CData ADO.NET Provider for PostgreSQL is trying to connect to it. You should setup Kerberos on the PostgreSQL Server to activate this authentication method. Once you have Kerberos authentication setup on the PostgreSQL Server, see Using Kerberos for details on how to authenticate with Kerberos by the provider.
Last updated