Will we get access to pg_shadow in Azure Postgres Flexible Server?

Question

Hi,

We have been using Azure Postgres Flexible Server and pgbouncer on top of it. Whenever, we add any new user, we want to add the user details in pgbouncer's auth_file (userlist.txt). It is becoming unmanageable since our requirement is to create separate database user for each user. We found auth_query can be used to solve this. But pg_shadow is not accessible in Azure Postgres Flexible server. Is there any way to get access to pg_shadow? if not, how can auth_query implemented with Azure Postgres Flexible server?

Answer 1

@Anonymous Thank you for reaching out.

My understanding is that you are looking to add user in pgbouncer's auth_file.

Is there any way to get access to pg_shadow? if not, how can auth_query implemented with Azure Postgres Flexible server?

The view pg_shadow exists for backwards compatibility: it emulates a catalog that existed in PostgreSQL before version 8.1. It shows properties of all roles that are marked as rolcanlogin in pg_authid

The name stems from the fact that this table should not be readable by the public since it contains passwords. pg_user is a publicly readable view on pg_shadow that blanks out the password field.

Access to this table is restricted in Azure Postgres Flexible Server.

same question was raised here https://learn.microsoft.com/en-us/answers/questions/1316636/azure-flexible-postgres-configuring-auth-query-ref

Please let us know if that answer your question.

Regards,

Oury