0

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:

aad_acc_name@domain.com@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!

Eve
  • 604
  • 8
  • 26
  • What is in the server log file when this happens? – jjanes Mar 29 '22 at 18:45
  • It says the role 'aad_acc_name@domain.com' does not exist. Although it is part of the AAD security group that is defined as AAD admin. – Eve Mar 30 '22 at 08:52
  • You say it does exist but the computer says it does not. I don't see how we can resolve this without having access to the computer. Maybe contact azure support. – jjanes Mar 30 '22 at 16:24
  • I did in the meantime, I'll update this post with the solution. The account is there, but the AAD admin is now a security group, and the user is part of it, roles must be given differently than we usually do it for simply a user. I'll keep you posted! Thank you for looking into the issue! :) – Eve Mar 30 '22 at 22:28

2 Answers2

1

• 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

Kartik Bhiwapurkar
  • 4,550
  • 2
  • 4
  • 9
  • Thank you for the headsup! The firewall configuration was fine, the error came from using the users and groups wrongly for providing roles and more importantly to login to PGAdmin (using tokens!). But true, it is important to check the steps you stated above! :) – Eve Apr 02 '22 at 08:21
0

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

user@xxx.onmicrosoft.com@<PGServerName>

CREATE ROLE "username@xxx.onmicrosoft.com" 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";
Eve
  • 604
  • 8
  • 26