Thread: Again, problem with pgbouncer

Again, problem with pgbouncer

From
Phoenix Kiula
Date:
Hi,

  - PG 9.0.10
  - Pgbouncer version 1.4.2

Not long ago, during the last server reboot for us, we had fixed the
really painful (and largely mysterious) process of setting up
pgbouncer.

File permissions and other mysteries were solved with help from Raghavendra:
http://permalink.gmane.org/gmane.comp.db.postgresql.pgbouncer.general/854

After a long we rebooted our server today and again, as if on cue,
pgbouncer has problems yet again :(

PG itself is running without problems.

The Pgbouncer process starts properly too. All the auth file, log file
etc are setup as mentioned in that URL above. We haven't changed
anything at all!

At first, just connecting via pgbouncer port was giving the "no user"
error. Which is funny, because the authfile has been working without
problems forever. The .pgpass file had the same problems, and is still
the same all this time.

So, upon reading that old thread again, I guessed that the "postgres"
user permissions were needed, so I did this:

  chown -R postgres:postgres /etc/pgbouncer
  chown -R postgres:postgres /var/run/pgbouncer/
  chown postgres:postgres /var/log/pgbouncer.log
  chown postgres:postgres /var/lib/pgsql/pgbouncer.txt

Then restarted both PG and Pgbouncer.

Now pgbouncer won't do anything at all. Trying to connect to psql via
the pgbouncer port gives this error:

     psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.6789"?


And in the log is this line:


     2012-10-01 06:12:00.703  3754 FATAL @src/main.c:553 in function
write_pidfile(): /var/run/pgbouncer/pgbouncer.pid: Permission denied
[13]


What now? Would appreciate some pointers.

Thanks.


Re: Again, problem with pgbouncer

From
raghu ram
Date:


On Mon, Oct 1, 2012 at 3:56 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
Hi,

  - PG 9.0.10
  - Pgbouncer version 1.4.2

Not long ago, during the last server reboot for us, we had fixed the
really painful (and largely mysterious) process of setting up
pgbouncer.

File permissions and other mysteries were solved with help from Raghavendra:
http://permalink.gmane.org/gmane.comp.db.postgresql.pgbouncer.general/854

After a long we rebooted our server today and again, as if on cue,
pgbouncer has problems yet again :(

PG itself is running without problems.

The Pgbouncer process starts properly too. All the auth file, log file
etc are setup as mentioned in that URL above. We haven't changed
anything at all!

At first, just connecting via pgbouncer port was giving the "no user"
error. Which is funny, because the authfile has been working without
problems forever. The .pgpass file had the same problems, and is still
the same all this time.

So, upon reading that old thread again, I guessed that the "postgres"
user permissions were needed, so I did this:

  chown -R postgres:postgres /etc/pgbouncer
  chown -R postgres:postgres /var/run/pgbouncer/
  chown postgres:postgres /var/log/pgbouncer.log
  chown postgres:postgres /var/lib/pgsql/pgbouncer.txt

Then restarted both PG and Pgbouncer.

Now pgbouncer won't do anything at all. Trying to connect to psql via
the pgbouncer port gives this error:

     psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.6789"?


And in the log is this line:


     2012-10-01 06:12:00.703  3754 FATAL @src/main.c:553 in function
write_pidfile(): /var/run/pgbouncer/pgbouncer.pid: Permission denied
[13]


What now? Would appreciate some pointers.

Thanks.


Could you please check permission of /var/run/pgbouncer/ directory. If pgbouncer directory does not have "postgres" user permissions,please assign it and then start the pgbouncer. 

Looking to the error thrown by pgbouncer the port shown up as 6789, but whereas the link of pgbouncer.ini file it has 6389. Please mention appropriate port while connecting via pgbouncer and give port number which is in pgbouncer.ini file.

-- 

Thanks & Regards,

Raghu Ram

EnterpriseDB Corporation

skypeid: raghu.ramedb

Blog:http://raghurc.blogspot.in/



Re: Again, problem with pgbouncer

From
Phoenix Kiula
Date:
> Could you please check permission of /var/run/pgbouncer/ directory. If
> pgbouncer directory does not have "postgres" user permissions,please assign
> it and then start the pgbouncer.


The /var/run/pgbouncer/ directory has

   chown -R postgres:postgres ..

The port number everywhere is already 6789.

What else?

Thanks.


Re: Again, problem with pgbouncer

From
Phoenix Kiula
Date:
On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>> Could you please check permission of /var/run/pgbouncer/ directory. If
>> pgbouncer directory does not have "postgres" user permissions,please assign
>> it and then start the pgbouncer.
>
>
> The /var/run/pgbouncer/ directory has
>
>    chown -R postgres:postgres ..
>
> The port number everywhere is already 6789.
>
> What else?



And just to be safe, I also added pgbouncer user to postgres group:


    usermod -a -G postgres pgbouncer


Now when I restart the pgbouncess service, it fails. The log has this message:


    2012-10-01 23:25:24.004 21037 FATAL
    Cannot open logfile: '/var/log/pgbouncer.log':
    Permission denied


That file is owned by "postgres:postgres" as indicated in a gazillion
threads and documentation online (none of which is comprehensive) but
just to be sure I also did this:


    chown :postgres /var/log/pgbouncer.log


Still the same permission error. Seriously, why can't the log message
be a little more useful? Why can't it say clearly WHICH USER is
looking for permission to the log file? Both "pgbouncer" and
"postgres" have permissions (through the group "postgres") on that
file. So which is it?

Much appreciate any pointers.

Thanks.


Re: Again, problem with pgbouncer

From
Phoenix Kiula
Date:
On Tue, Oct 2, 2012 at 11:29 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>>> Could you please check permission of /var/run/pgbouncer/ directory. If
>>> pgbouncer directory does not have "postgres" user permissions,please assign
>>> it and then start the pgbouncer.
>>
>>
>> The /var/run/pgbouncer/ directory has
>>
>>    chown -R postgres:postgres ..
>>
>> The port number everywhere is already 6789.
>>
>> What else?
>
>
>
> And just to be safe, I also added pgbouncer user to postgres group:
>
>
>     usermod -a -G postgres pgbouncer
>
>
> Now when I restart the pgbouncess service, it fails. The log has this message:
>
>
>     2012-10-01 23:25:24.004 21037 FATAL
>     Cannot open logfile: '/var/log/pgbouncer.log':
>     Permission denied
>
>
> That file is owned by "postgres:postgres" as indicated in a gazillion
> threads and documentation online (none of which is comprehensive) but
> just to be sure I also did this:
>
>
>     chown :postgres /var/log/pgbouncer.log
>
>
> Still the same permission error. Seriously, why can't the log message
> be a little more useful? Why can't it say clearly WHICH USER is
> looking for permission to the log file? Both "pgbouncer" and
> "postgres" have permissions (through the group "postgres") on that
> file. So which is it?



I made the port number 6389 everywhere. I changed the permissions of
the pgbouncer.log to:

   chown pgbouncer:postgres /var/log/pgbouncer.log

Now at least the service starts. But when I try and connect via the
pgbouncer ID:

   psql -p 6389 -U  snipurl_snipurl snipurl

I get this error:

   psql: ERROR:  No such user: MYSITE_MYSITE

And yet, the authfile has this:

    "MYSITE_MYSITE" "<md5 of raw password>"
    "MYSITE_MYSITE" "<raw password>"
    "postgres" "<md5 of string>"
    "MYSITE_pgbouncer" ""


The authfile permissions are:

   283377983 -rw-r--r-- 1 pgbouncer postgres 262 Apr 14 11:15
/var/lib/pgsql/pgbouncer.txt


What else?


Re: Again, problem with pgbouncer

From
Phoenix Kiula
Date:
On Tue, Oct 2, 2012 at 5:27 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> On Tue, Oct 2, 2012 at 11:29 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>> On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>>>> Could you please check permission of /var/run/pgbouncer/ directory. If
>>>> pgbouncer directory does not have "postgres" user permissions,please assign
>>>> it and then start the pgbouncer.
>>>
>>>
>>> The /var/run/pgbouncer/ directory has
>>>
>>>    chown -R postgres:postgres ..
>>>
>>> The port number everywhere is already 6789.
>>>
>>> What else?
>>
>>
>>
>> And just to be safe, I also added pgbouncer user to postgres group:
>>
>>
>>     usermod -a -G postgres pgbouncer
>>
>>
>> Now when I restart the pgbouncess service, it fails. The log has this message:
>>
>>
>>     2012-10-01 23:25:24.004 21037 FATAL
>>     Cannot open logfile: '/var/log/pgbouncer.log':
>>     Permission denied
>>
>>
>> That file is owned by "postgres:postgres" as indicated in a gazillion
>> threads and documentation online (none of which is comprehensive) but
>> just to be sure I also did this:
>>
>>
>>     chown :postgres /var/log/pgbouncer.log
>>
>>
>> Still the same permission error. Seriously, why can't the log message
>> be a little more useful? Why can't it say clearly WHICH USER is
>> looking for permission to the log file? Both "pgbouncer" and
>> "postgres" have permissions (through the group "postgres") on that
>> file. So which is it?
>
>
>
> I made the port number 6389 everywhere. I changed the permissions of
> the pgbouncer.log to:
>
>    chown pgbouncer:postgres /var/log/pgbouncer.log
>
> Now at least the service starts. But when I try and connect via the
> pgbouncer ID:
>
>    psql -p 6389 -U  snipurl_snipurl snipurl
>
> I get this error:
>
>    psql: ERROR:  No such user: MYSITE_MYSITE
>
> And yet, the authfile has this:
>
>     "MYSITE_MYSITE" "<md5 of raw password>"
>     "MYSITE_MYSITE" "<raw password>"
>     "postgres" "<md5 of string>"
>     "MYSITE_pgbouncer" ""
>
>
> The authfile permissions are:
>
>    283377983 -rw-r--r-- 1 pgbouncer postgres 262 Apr 14 11:15
> /var/lib/pgsql/pgbouncer.txt
>
>
> What else?




No response.

Is there anyone who can help me with pgbouncer?

What are the permissions for the authfile, etc?


Re: Again, problem with pgbouncer

From
dinesh kumar
Date:
Hi,

Do you have "MYSITE_MYSITE" user at your database. 

Please login to the database directly (I mean, without any pgbouncer and check once. 

select* from pg_user where usename ~~* 'MYSITE_MYSITE'; And also please check your's pgbouncer.ini admin users list also.

Best Regards,
Dinesh


On Wed, Oct 3, 2012 at 11:31 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
On Tue, Oct 2, 2012 at 5:27 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> On Tue, Oct 2, 2012 at 11:29 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>> On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>>>> Could you please check permission of /var/run/pgbouncer/ directory. If
>>>> pgbouncer directory does not have "postgres" user permissions,please assign
>>>> it and then start the pgbouncer.
>>>
>>>
>>> The /var/run/pgbouncer/ directory has
>>>
>>>    chown -R postgres:postgres ..
>>>
>>> The port number everywhere is already 6789.
>>>
>>> What else?
>>
>>
>>
>> And just to be safe, I also added pgbouncer user to postgres group:
>>
>>
>>     usermod -a -G postgres pgbouncer
>>
>>
>> Now when I restart the pgbouncess service, it fails. The log has this message:
>>
>>
>>     2012-10-01 23:25:24.004 21037 FATAL
>>     Cannot open logfile: '/var/log/pgbouncer.log':
>>     Permission denied
>>
>>
>> That file is owned by "postgres:postgres" as indicated in a gazillion
>> threads and documentation online (none of which is comprehensive) but
>> just to be sure I also did this:
>>
>>
>>     chown :postgres /var/log/pgbouncer.log
>>
>>
>> Still the same permission error. Seriously, why can't the log message
>> be a little more useful? Why can't it say clearly WHICH USER is
>> looking for permission to the log file? Both "pgbouncer" and
>> "postgres" have permissions (through the group "postgres") on that
>> file. So which is it?
>
>
>
> I made the port number 6389 everywhere. I changed the permissions of
> the pgbouncer.log to:
>
>    chown pgbouncer:postgres /var/log/pgbouncer.log
>
> Now at least the service starts. But when I try and connect via the
> pgbouncer ID:
>
>    psql -p 6389 -U  snipurl_snipurl snipurl
>
> I get this error:
>
>    psql: ERROR:  No such user: MYSITE_MYSITE
>
> And yet, the authfile has this:
>
>     "MYSITE_MYSITE" "<md5 of raw password>"
>     "MYSITE_MYSITE" "<raw password>"
>     "postgres" "<md5 of string>"
>     "MYSITE_pgbouncer" ""
>
>
> The authfile permissions are:
>
>    283377983 -rw-r--r-- 1 pgbouncer postgres 262 Apr 14 11:15
> /var/lib/pgsql/pgbouncer.txt
>
>
> What else?




No response.

Is there anyone who can help me with pgbouncer?

What are the permissions for the authfile, etc?


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

From
Wolf Schwurack
Date:
I use pgpool but some of the problem you listed are same as I had with pgpool

I would not run pgbouner in /var/run/pbbouner. Every time you reboot the directory will get deleted. I set my parameter
toanother directory the would not get deleted after a reboot.  

/var/log/pgbouncer.log:
what is the permission on /var/log? If you don't have write permission on the directory then you cannot write to the
file.

Psql: ERROR: No such user:
 You have to create the user in postgres, check you users

postgres=# /du

Role name
--------------
testuser


Wolfgang Schwurack
DBA/SA
UEN


-----Original Message-----
From: pgbouncer-general-bounces@pgfoundry.org [mailto:pgbouncer-general-bounces@pgfoundry.org] On Behalf Of Phoenix
Kiula
Sent: Wednesday, October 03, 2012 12:02 PM
To: raghu ram
Cc: pgbouncer-general@pgfoundry.org; PG-General Mailing List
Subject: Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer

On Tue, Oct 2, 2012 at 5:27 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> On Tue, Oct 2, 2012 at 11:29 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>> On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>>>> Could you please check permission of /var/run/pgbouncer/ directory.
>>>> If pgbouncer directory does not have "postgres" user
>>>> permissions,please assign it and then start the pgbouncer.
>>>
>>>
>>> The /var/run/pgbouncer/ directory has
>>>
>>>    chown -R postgres:postgres ..
>>>
>>> The port number everywhere is already 6789.
>>>
>>> What else?
>>
>>
>>
>> And just to be safe, I also added pgbouncer user to postgres group:
>>
>>
>>     usermod -a -G postgres pgbouncer
>>
>>
>> Now when I restart the pgbouncess service, it fails. The log has this message:
>>
>>
>>     2012-10-01 23:25:24.004 21037 FATAL
>>     Cannot open logfile: '/var/log/pgbouncer.log':
>>     Permission denied
>>
>>
>> That file is owned by "postgres:postgres" as indicated in a gazillion
>> threads and documentation online (none of which is comprehensive) but
>> just to be sure I also did this:
>>
>>
>>     chown :postgres /var/log/pgbouncer.log
>>
>>
>> Still the same permission error. Seriously, why can't the log message
>> be a little more useful? Why can't it say clearly WHICH USER is
>> looking for permission to the log file? Both "pgbouncer" and
>> "postgres" have permissions (through the group "postgres") on that
>> file. So which is it?
>
>
>
> I made the port number 6389 everywhere. I changed the permissions of
> the pgbouncer.log to:
>
>    chown pgbouncer:postgres /var/log/pgbouncer.log
>
> Now at least the service starts. But when I try and connect via the
> pgbouncer ID:
>
>    psql -p 6389 -U  snipurl_snipurl snipurl
>
> I get this error:
>
>    psql: ERROR:  No such user: MYSITE_MYSITE
>
> And yet, the authfile has this:
>
>     "MYSITE_MYSITE" "<md5 of raw password>"
>     "MYSITE_MYSITE" "<raw password>"
>     "postgres" "<md5 of string>"
>     "MYSITE_pgbouncer" ""
>
>
> The authfile permissions are:
>
>    283377983 -rw-r--r-- 1 pgbouncer postgres 262 Apr 14 11:15
> /var/lib/pgsql/pgbouncer.txt
>
>
> What else?




No response.

Is there anyone who can help me with pgbouncer?

What are the permissions for the authfile, etc?
_______________________________________________
Pgbouncer-general mailing list
Pgbouncer-general@pgfoundry.org
http://pgfoundry.org/mailman/listinfo/pgbouncer-general


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

From
Phoenix Kiula
Date:
On Thu, Oct 4, 2012 at 2:50 AM, Wolf Schwurack <wolf@uen.org> wrote:
> I use pgpool but some of the problem you listed are same as I had with pgpool


Thanks Wolf, for the thoughts.


> I would not run pgbouner in /var/run/pbbouner. Every time you reboot the
> directory will get deleted. I set my parameter to another directory the would not
> get deleted after a reboot.


OK, but this is not a showstopper here. Right?



> /var/log/pgbouncer.log:
> what is the permission on /var/log? If you don't have write permission on the directory then you cannot write to the
file.


Permissions:

/var/run/pgbouncer --
    70058074 drwxr-xr-x  2 pgbouncer postgres 4.0K Oct  2 06:17 pgbouncer/

/var/log --
    145686529 drwxr-xr-x 17 root root  4.0K Oct  5 04:29 log/

Please note that whatever the settings, they were working before a
server reboot. What settings do I need to give "/var/log" (currently
root) so the pgbouncer process can write to it? Why are these special
permissions needed-- I mean Apache, MysQL, Nginx etc...all of them can
write to the logs in this log folder.



> Psql: ERROR: No such user:
>  You have to create the user in postgres, check you users
>
> postgres=# /du
>



Yes, this user exists in the postgres database.


                          List of roles
    Role name    |            Attributes             | Member of
-----------------+-----------------------------------+-----------
 postgres        | Superuser, Create role, Create DB | {}
 rvadmin         |                                   | {}
 MYSITE          |                                   | {}
 MYSITE_MYSITE   | Superuser, Create DB              | {}



And the authfile also has permissions for "pgbouncer:postgres".

What else?


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

From
Wolf Schwurack
Date:
-- OK, but this is not a showstopper here. Right?
Your right - just a thought

-- What settings do I need to give "/var/log" (currently root) so the pgbouncer process can write to it? Why are these
specialpermissions needed 
You need to have a pgbouner directory in /var/log and have the owner pgbouncer. This is easy to test try creating a
filein /var/log as the user pgbouncer. It should fail because pgbouncer does not have writer permissions to /var/log.
Asroot 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 

If the user exists in the postgres then I'm not sure why it fails.




Wolf


-----Original Message-----
From: Phoenix Kiula [mailto:phoenix.kiula@gmail.com]
Sent: Friday, October 05, 2012 9:37 AM
To: Wolf Schwurack
Cc: raghu ram; pgbouncer-general@pgfoundry.org; PG-General Mailing List
Subject: Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer

On Thu, Oct 4, 2012 at 2:50 AM, Wolf Schwurack <wolf@uen.org> wrote:
> I use pgpool but some of the problem you listed are same as I had with
> pgpool


Thanks Wolf, for the thoughts.


> I would not run pgbouner in /var/run/pbbouner. Every time you reboot
> the directory will get deleted. I set my parameter to another
> directory the would not get deleted after a reboot.


OK, but this is not a showstopper here. Right?



> /var/log/pgbouncer.log:
> what is the permission on /var/log? If you don't have write permission on the directory then you cannot write to the
file.


Permissions:

/var/run/pgbouncer --
    70058074 drwxr-xr-x  2 pgbouncer postgres 4.0K Oct  2 06:17 pgbouncer/

/var/log --
    145686529 drwxr-xr-x 17 root root  4.0K Oct  5 04:29 log/

Please note that whatever the settings, they were working before a server reboot. What settings do I need to give
"/var/log"(currently 
root) so the pgbouncer process can write to it? Why are these special permissions needed-- I mean Apache, MysQL, Nginx
etc...allof them can write to the logs in this log folder. 



> Psql: ERROR: No such user:
>  You have to create the user in postgres, check you users
>
> postgres=# /du
>



Yes, this user exists in the postgres database.


                          List of roles
    Role name    |            Attributes             | Member of
-----------------+-----------------------------------+-----------
 postgres        | Superuser, Create role, Create DB | {}
 rvadmin         |                                   | {}
 MYSITE          |                                   | {}
 MYSITE_MYSITE   | Superuser, Create DB              | {}



And the authfile also has permissions for "pgbouncer:postgres".

What else?


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

From
Phoenix Kiula
Date:
On Sat, Oct 6, 2012 at 12:01 AM, Wolf Schwurack <wolf@uen.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
filein /var/log as the user pgbouncer. It should fail because pgbouncer does not have writer permissions to /var/log.
Asroot 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.

Thanks.


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

From
Adrian Klaver
Date:
On 10/05/2012 07:00 PM, Phoenix Kiula wrote:
> On Sat, Oct 6, 2012 at 12:01 AM, Wolf Schwurack <wolf@uen.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
filein /var/log as the user pgbouncer. It should fail because pgbouncer does not have writer permissions to /var/log.
Asroot 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?
>
> Thanks.
>
>


--
Adrian Klaver
adrian.klaver@gmail.com


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

From
Phoenix Kiula
Date:
On Sat, Oct 6, 2012 at 10:07 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

...snip...

> What are the contents of your pgbouncer.ini file?
>>



Thanks Adrian.

I mentioned the full ini file details 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


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

From
Adrian Klaver
Date:
On 10/05/2012 07:23 PM, Phoenix Kiula wrote:
> On Sat, Oct 6, 2012 at 10:07 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>
> ...snip...
>
>> What are the contents of your pgbouncer.ini file?
>>>
>
>
>
> Thanks Adrian.
>
> I mentioned the full ini file details 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

One thing I see above:
http://pgbouncer.projects.postgresql.org/doc/config.html
""\*" acts as fallback database"

Notice the backslash.

>
> [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
>
>


--
Adrian Klaver
adrian.klaver@gmail.com


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

From
Phoenix Kiula
Date:
On Sat, Oct 6, 2012 at 10:24 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>
> One thing I see above:
> http://pgbouncer.projects.postgresql.org/doc/config.html
> ""\*" acts as fallback database"
>
> Notice the backslash.
>


Ok, but:

(1) The exact same INI file was working so far.

(2) Why do I need a fallback database? I want to be precise about
database names if possible.

(3) I did try and change the config to have the backslash, but when I
restart, I get this error:

     2012-10-05 22:30:06.882 27442 ERROR
     syntax error in configuration (/etc/pgbouncer/pgbouncer.ini:2),
stopping loading


Now?


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

From
Adrian Klaver
Date:
On 10/05/2012 07:30 PM, Phoenix Kiula wrote:
> On Sat, Oct 6, 2012 at 10:24 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>>
>> One thing I see above:
>> http://pgbouncer.projects.postgresql.org/doc/config.html
>> ""\*" acts as fallback database"
>>
>> Notice the backslash.
>>
>
>
> Ok, but:
>
> (1) The exact same INI file was working so far.
>
> (2) Why do I need a fallback database? I want to be precise about
> database names if possible.

So why was a database name not specified?

>
> (3) I did try and change the config to have the backslash, but when I
> restart, I get this error:
>
>       2012-10-05 22:30:06.882 27442 ERROR
>       syntax error in configuration (/etc/pgbouncer/pgbouncer.ini:2),
> stopping loading

Well it did not like that.
>
>
> Now?

What is the connection string you are using to make the connection to
pPgbouncer?

>
>


--
Adrian Klaver
adrian.klaver@gmail.com


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

From
Shashank Tripathi
Date:
On Sat, Oct 6, 2012 at 10:07 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> On 10/05/2012 07:00 PM, Phoenix Kiula wrote:
>>
>> On Sat, Oct 6, 2012 at 12:01 AM, Wolf Schwurack <wolf@uen.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


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

From
Johnny Tan
Date:

Since it started happening after reboot, I wonder if it's selinux related.

If you previously did "setenforce 0", that wouldn't persist across reboot.

On Oct 5, 2012 10:01 PM, "Phoenix Kiula" <phoenix.kiula@gmail.com> wrote:
On Sat, Oct 6, 2012 at 12:01 AM, Wolf Schwurack <wolf@uen.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.

Thanks.
_______________________________________________
Pgbouncer-general mailing list
Pgbouncer-general@pgfoundry.org
http://pgfoundry.org/mailman/listinfo/pgbouncer-general

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

From
Wolf Schwurack
Date:
Sorry but you are showing two different paths

Your first email showed this path
/var/log/pgbouncer.log

And now you are stating this, which would be correct
/var/log/pgbouncer/pgbouncer.log

Wolf


-----Original Message-----
From: Phoenix Kiula [mailto:phoenix.kiula@gmail.com]
Sent: Friday, October 05, 2012 8:01 PM
To: Wolf Schwurack
Cc: raghu ram; pgbouncer-general@pgfoundry.org; PG-General Mailing List
Subject: Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer

On Sat, Oct 6, 2012 at 12:01 AM, Wolf Schwurack <wolf@uen.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
thiserror: 

  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
ofcourse 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
thesethreads online? Would love to have some help or guidance. 

Thanks.


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

From
Marko Kreen
Date:
On Tue, Oct 2, 2012 at 12:27 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> I get this error:
>
>    psql: ERROR:  No such user: MYSITE_MYSITE
>
> And yet, the authfile has this:
>
>     "MYSITE_MYSITE" "<md5 of raw password>"
>     "MYSITE_MYSITE" "<raw password>"
>     "postgres" "<md5 of string>"
>     "MYSITE_pgbouncer" ""

Because of data sanitizing I cannot see actual problem,
but few hints:

- Don't give one username several times (pgbouncer uses just one of them)
- Usernames are case-sensitive
- Username max length is 63 chars

--
marko


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

From
Marko Kreen
Date:
On Sat, Oct 6, 2012 at 5:24 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> One thing I see above:
> http://pgbouncer.projects.postgresql.org/doc/config.html
> ""\*" acts as fallback database"
>
> Notice the backslash.

The backslash is asciidoc/docbook accident, it should be plain * there.

--
marko