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

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.

On Mon, Oct 1, 2012 at 3:56 PM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)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/

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

On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)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.

On Tue, Oct 2, 2012 at 11:29 AM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
> On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)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?

On Tue, Oct 2, 2012 at 5:27 PM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
> On Tue, Oct 2, 2012 at 11:29 AM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
>> On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)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?

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
manojadinesh.blogspot.com

On Wed, Oct 3, 2012 at 11:31 PM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com>wrote:

> On Tue, Oct 2, 2012 at 5:27 PM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com>
> wrote:
> > On Tue, Oct 2, 2012 at 11:29 AM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com>
> wrote:
> >> On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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 to another 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(at)pgfoundry(dot)org [mailto:pgbouncer-general-bounces(at)pgfoundry(dot)org] On Behalf Of Phoenix Kiula
Sent: Wednesday, October 03, 2012 12:02 PM
To: raghu ram
Cc: pgbouncer-general(at)pgfoundry(dot)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(dot)kiula(at)gmail(dot)com> wrote:
> On Tue, Oct 2, 2012 at 11:29 AM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
>> On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)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(at)pgfoundry(dot)org
http://pgfoundry.org/mailman/listinfo/pgbouncer-general

On Thu, Oct 4, 2012 at 2:50 AM, Wolf Schwurack <wolf(at)uen(dot)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?

-- 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 special permissions needed
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

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

Wolf

-----Original Message-----
From: Phoenix Kiula [mailto:phoenix(dot)kiula(at)gmail(dot)com]
Sent: Friday, October 05, 2012 9:37 AM
To: Wolf Schwurack
Cc: raghu ram; pgbouncer-general(at)pgfoundry(dot)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(at)uen(dot)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?

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.

Thanks.

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?
>
> Thanks.
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

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

On Sat, Oct 6, 2012 at 10:07 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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

On 10/05/2012 07:23 PM, Phoenix Kiula wrote:
> On Sat, Oct 6, 2012 at 10:07 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)com

On Sat, Oct 6, 2012 at 10:24 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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?

On 10/05/2012 07:30 PM, Phoenix Kiula wrote:
> On Sat, Oct 6, 2012 at 10:24 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)com

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(dot)kiula(at)gmail(dot)com> 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.
>
> Thanks.
> _______________________________________________
> Pgbouncer-general mailing list
> Pgbouncer-general(at)pgfoundry(dot)org
> http://pgfoundry.org/mailman/listinfo/pgbouncer-general
>

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(dot)kiula(at)gmail(dot)com]
Sent: Friday, October 05, 2012 8:01 PM
To: Wolf Schwurack
Cc: raghu ram; pgbouncer-general(at)pgfoundry(dot)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(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.

Thanks.

On Tue, Oct 2, 2012 at 12:27 PM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)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

On Sat, Oct 6, 2012 at 5:24 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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