CODEWITHSUNDEEP
postgresqlazure-active-directorypsqlpgadmin
Eve
Eve

Reputation: 646

Connect PostgreSQL with Azure Active Directory - server closed the connection unexpectedly

After looking through the previously asked questions, I can see that this is a common issue, but couldn't see one that is specifically seeing my problem.

On the top of the local db admin, I have set AAD authentication, but I can only connect to the postgres DB with the local db admin that was added during provisioning the service.

When I try to connect with AAD with whatever tool (including psql in the powershell test scripts), I get the following error:

server closed the connection unexpectedly
This probably means the server terminated
abnormally before or while processing the request.

When trying to connect with the AAD account, I follow the rules like:

[email protected]@servername

Same error comes from PGAdmin as well, while I can easily connect with the postgresql admin.

I'm not sure where to find the issue, any ideas are welcome!

Upvotes: 0

Views: 2861

Answers (2)

Eve
Eve

Reputation: 646

So the issue was that I must either provide roles to a security group or a user. If the security group gets the role, when logging in with PGAdmin, you need to login with the group name, and not the user in the group.

Reference from Microsoft

We received some code as well which can support our work with the issue:

az account get-access-token --resource-type oss-rdbms

export PGPASSWORD=az account get-access-token --resource-type oss-rdbms

psql -h aaduserpostresss.postgres.database.azure.com -U <GroupName>@<PGServerName> dbname=postgres

\du

[email protected]@<PGServerName>

CREATE ROLE "[email protected]" WITH LOGIN IN ROLE azure_ad_user;

Security groups
CREATE ROLE "readonly" WITH LOGIN IN ROLE azure_ad_user; !!CASE SENSITIVE for group names!!

GRANT SELECT ON ALL TABLES ON SCHEMA public TO "readonly";
GRANT ALL ON ALL TABLES IN SCHEMA public TO "writegroup";

Upvotes: 0

Kartik Bhiwapurkar
Kartik Bhiwapurkar

Reputation: 5159

• Please check whether the Azure AD Administrator assigned for your PostgreSQL database Server has the role of ‘azure_ad_admin’ in the database roles. Also, please check whether the Azure AD admin is removed from the server or not, because as you said you were not able to login to the Database using the PGAdmin account, thus if the Azure AD Admin account is removed from the server, the server will not be associated with the Azure tenant any longer due to which also AAD authentication might not be possible for your Server.

• Please check whether your PostgreSQL Server is listening to the external interfaces, i.e., whether the communication and accessibility is not blocked by any firewall or NSG or sort of. Also, to sort this problem out of external communication through PostgreSQL DB Server, go to ‘postgresql.conf’ file and execute the below command to modify it: -

 sudo vim /etc/postgresql/9.3/main/postgresql.conf

Add the below line in that file: -

 listen_addresses = '*'

And then restart the PostgreSQL service through the below command: -

 sudo /etc/init.d/postgresql restart

The above commands are with respect to Linux (Ubuntu), for Windows version of local PostgreSQL client, the ‘postgresql.conf’ file can be located at ‘C:\Program Files\PostgreSQL<version>\data’

• Also, I would suggest you to please check the community thread link below for more details regarding fixing your issue: -

Server closes connection unexpectedly when connecting to Azure Postgres

As you are facing persistent connection issues with your PostgreSQL DB Server, most probably, it might be due to incorrect Server and client firewall configuration, a user error while entering the server’s suffix name and its credentials and other general issues too. Please check the documentation link below for detailed information: -

https://learn.microsoft.com/en-us/azure/postgresql/howto-troubleshoot-common-connection-issues#troubleshoot-persistent-errors

Upvotes: 1

Related Questions