Psql: Server Closed the Connection Unexepectedly

R DBI connection closed unexpectedly by server + server terminated abnormally before or while processing request

Maybe it's related to stricter permission settings on your production postgreSQL instance. Even when user/pwd is the same, the production instance might only accept connections from localhost or specific IP addresses. Furthermore, authentication via username/password could be disabled, allowing e.g. only the "cert" method.

Please check /etc/postgresql/VERSION/main/pg_hba.conf (or have it checked/explained to you by your DBA/IT).

Also, it could be due to specific firewall/security settings for your production instance. Which would allow connections only from specific IPs, or it would simply drop all packets from the DBMS to your machine.

HtH

cannot connect to Heroku Postgres - error server closed the connection unexpectedly

Port 5432 must be unblocked for psql to work and I was unable to do that. Switching to a different network (e.g. Internet provider at home or mobile Internet) may help.

azure + postgresql: How to fix error: Unable to connect to server: server closed the connection unexpectedly

https://stackoverflow.com/a/59640480/798053 fixed it for me:

$ brew services stop postgresql
$ brew install postgresql@10
$ brew services start postgresql@10
$ brew link --overwrite --force postgresql@10ode here

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

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";

---

Related Topics