{"action":"opened","issue":{"url":"https://api.github.com/repos/pgbouncer/pgbouncer/issues/149","repository_url":"https://api.github.com/repos/pgbouncer/pgbouncer","labels_url":"https://api.github.com/repos/pgbouncer/pgbouncer/issues/149/labels{/name}","comments_url":"https://api.github.com/repos/pgbouncer/pgbouncer/issues/149/comments","events_url":"https://api.github.com/repos/pgbouncer/pgbouncer/issues/149/events","html_url":"https://github.com/pgbouncer/pgbouncer/issues/149","id":180782233,"number":149,"title":"[Question] How to use the example \"secure function for auth_query\"?","user":{"login":"lirao","id":6883412,"avatar_url":"https://avatars.githubusercontent.com/u/6883412?v=3","gravatar_id":"","url":"https://api.github.com/users/lirao","html_url":"https://github.com/lirao","followers_url":"https://api.github.com/users/lirao/followers","following_url":"https://api.github.com/users/lirao/following{/other_user}","gists_url":"https://api.github.com/users/lirao/gists{/gist_id}","starred_url":"https://api.github.com/users/lirao/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lirao/subscriptions","organizations_url":"https://api.github.com/users/lirao/orgs","repos_url":"https://api.github.com/users/lirao/repos","events_url":"https://api.github.com/users/lirao/events{/privacy}","received_events_url":"https://api.github.com/users/lirao/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2016-10-04T00:46:11Z","updated_at":"2016-10-04T00:46:11Z","closed_at":null,"body":"I'm trying to set up pgbouncer with auth_user and auth_query example.\r\nSetting just auth_user works perfectly fine, but overriding the auth_query parameter just doesn't work.\r\nI've used the following query\r\n```\r\ncreate or replace function user_search(uname TEXT) returns table(uname name, pass text)\r\nas $$ SELECT usename, passwd FROM pg_shadow WHERE usename=$1  $$ \r\nlanguage sql SECURITY DEFINER;\r\n```\r\nwith `auth_query = select user_search($1)` in the config file. \r\nTrying to login through pgbouncer with a user that's not on users.txt gave a `psql: ERROR:  unexpected response from login query`, and the verbose=2 logs showed that the return code is `S: pkt 'E', len=206`.\r\n\r\nI don't even get prompted to type in my password, so I'm not sure what login is failing here. Is it the auth_user login? \r\n\r\nI've verified that my auth_query is usable when logged in as my auth_user.\r\n\r\nTried the example query from https://pgbouncer.github.io/config.html, and that also didn't work.\r\n\r\nDid I set up anything wrong? Is my return format wrong? I tried tweaking the query and function around but none of my attempts worked... \r\n\r\nWould be great if the example config could also show the auth_query parameter that corresponds to the function creation statement!\r\n\r\npgbouncer version: 1.7.2\r\npostgres version: 9.5.4 \r\nOSX"}}
{"actor":{"display_login":"lirao"}}