Azure Flexible Postgres: Configuring auth_query refering to pg_shadow table in pgbouncer throws "permission denied for table pg_shadow"

Question

I am trying to setup auth_query for pgbouncer authentication,

auth_query in-turn is running below query.

Select usename, 
  passwd 
from 
  pg_shadow 

This query throws below error "permission denied for view pg_shadow".

Is there a way to provide my user, access to pg_shadow?

Answer 1

Hi,@Channabasawa Nagur Welcome to the Microsoft Q&A platform, thanks for posting the question and for using Azure Services.

Can you please confirm if you are using a Single server or a flexible server?
If auth_user is set then any user not specified in auth_file will be queried through the auth_query query from pg_shadow in the database, using auth_user the password of auth_user will be taken from auth_file, if the auth_user does not require a password then it does not need to be defined in auth_file.
Direct access to pg_shadow requires admin rights.

Regards

Geetha