Re: [Pgbouncer-general] Again, problem with pgbouncer

On Sat, Oct 6, 2012 at 10:07 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
> On 10/05/2012 07:00 PM, Phoenix Kiula wrote:
>>
>> On Sat, Oct 6, 2012 at 12:01 AM, Wolf Schwurack <wolf(at)uen(dot)org> wrote:
>> ....
>>>
>>> You need to have a pgbouner directory in /var/log and have the owner
>>> pgbouncer. This is easy to test try creating a file in /var/log as the user
>>> pgbouncer. It should fail because pgbouncer does not have writer permissions
>>> to /var/log. As root create a directory /var/log/pgbouncer, change owner to
>>> pgbouncer. Set your parameter for pgbouncer.log to /var/log/pgbouncer. Then
>>> test by creating a file in /var/log/pgbouncer as user pgbouncer
>>
>>
>>
>>
>> Wolf, I think you missed the earlier posts in this thread. The
>> "/var/log/pgbouncer.log" already has those permissions.
>>
>> Note this important fact: the same permissions have been working for
>> nearly 2 years.
>>
>> Anyway, I created a directory: /var/log/pgbouncer/, put the
>> pgbouncer.log file in it.
>>
>> chown -R pgbouncer:postgres /var/log/pgbouncer
>> chown pgbouncer:postgres /var/log/pgbouncer/pgbouncer.log
>> chmod 777 /var/log/pgbouncer/pgbouncer.log
>>
>> As was already happening, pgbouncer starts. No problem.
>>
>> It's now that I cannot connect to PSQL via pgbouncer (of course I can
>> connect to psql directly) because it fails with this error:
>>
>> psql: ERROR: No such user: MYSITE_MYSITE
>>
>>
>> Which is weird, because that user does exist. Both inside the postgres
>> database when I do "\du" as you suggested, and of course in the
>> pgbouncer authfile too --
>>
>>
>> > chown pgbouncer:postgres /var/lib/pgsql/pgbouncer.txt
>>
>> > cat /var/lib/pgsql/pgbouncer.txt
>>
>> "MYSITE_MYSITE" "md5 pass"
>> "MYSITE_MYSITE" "raw pass"
>> "postgres" "md5fd6313191fec7887f88c31a85c43df21"
>>
>>
>> So now. What? Why is this otherwise very useful tool coded so poorly
>> that there's reams of such permissions and all of these threads
>> online? Would love to have some help or guidance.
>
>
> What are the contents of your pgbouncer.ini file?

Mentioned above in the thread, but here they are again. Please do not
comment about port numbers. This is a public list so I change the
numbers, but they are very much on track everywhere they need to be.

Thanks for any pointers.

[databases]
* = host=127.0.0.1 port=5432

[pgbouncer]
listen_addr = *
listen_port = 6389
auth_type = trust
auth_file = /var/lib/pgsql/pgbouncer.txt
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
admin_users = postgres,MYSITE_MYSITE,MYSITE_pgbouncer
stats_users = postgres,MYSITE_MYSITE,MYSITE_pgbouncer,stats,root
pool_mode = transaction
server_reset_query = DISCARD ALL;
server_check_query = select 1
server_check_delay = 10
max_client_conn = 800
default_pool_size = 20
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1
unix_socket_dir = /tmp
ignore_startup_parameters = application_name