[Postgres-xl-general] Getting "Failed to get pooled connections" w/ pgxl created user

Anson Abraham anson.abraham at gmail.com
Tue Jul 29 07:31:47 PDT 2014


yeah, that's the cause.  On DN i have to put explicit trust for created
user in the pg_hba.  I recall the same issue w/ PG-XC as well.  So I have
to explicitly define per ip from coordinators to be trust, but from other
ips, to be md5.  Make sense.


On Tue, Jul 29, 2014 at 7:12 AM, Pavan Deolasee <pavan.deolasee at gmail.com>
wrote:

>
>
> On Tue, Jul 29, 2014 at 2:11 AM, Anson Abraham <anson.abraham at gmail.com>
> wrote:
>
>> I used pg_ctl to create GTM, 2 coordinators and 2 Datanodes.  all on
>> separate machines.  Meaning I have 5 separate machines .  It successfully
>> created the cluster, where I am able to connect to the coordinators w/ the
>> system user pgxl.  I'm able to create tables, insert, update, etc...
>> However when I created a postgresXL user:
>>
>> CREATE ROLE testuser WITH LOGIN PASSWORD 'testpass';
>> GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;
>> GRANT ALL PRIVILEGES ON SCHEMA public TO testuser;
>> GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO testuser;
>>
>> I'm able to connect to the DB testdb as testuser.  However, when I try to
>> select a table that I created w/ the pgxl user, i get failed to get pooled
>> connections error.  I granted select, update, on the created table to the
>> testuser, and still getting the same error.
>>
>>
> Hmm. Interesting. I am running with the current tip of the master which
> contains a few bug fixes and at least one of those fixes the error you are
> seeing. But I'm not sure if the fix has anything specific to do with the
> user privileges. In any case, do you mind trying with the current master
> branch? FWIW I tried the steps mentioned by you and things work OK for me.
> I am running 2 datanodes, 2 coordinators, 2 proxies, a GTM master and a
> standby, all on a single node.
>
> $ psql -p 30005 postgres
> psql (PGXL 9.2.0, based on PG 9.2.4 (Postgres-XL 9.2.0))
> Type "help" for help.
>
> postgres=# CREATE DATABASE testdb;
> CREATE DATABASE
> postgres=# \q
>
> $ psql -p 30005 testdb
> psql (PGXL 9.2.0, based on PG 9.2.4 (Postgres-XL 9.2.0))
> Type "help" for help.
>
> testdb=# CREATE ROLE testuser WITH LOGIN PASSWORD 'testpass';
> CREATE ROLE
> testdb=# GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;
> GRANT
> testdb=# GRANT ALL PRIVILEGES ON SCHEMA public TO testuser;
> GRANT
>
> testdb=# CREATE TABLE testtab (a int, b int);
> CREATE TABLE
> testdb=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO testuser;
> GRANT
> testdb=# \q
> $ psql -p 30005 testdb -U testuser
> psql (PGXL 9.2.0, based on PG 9.2.4 (Postgres-XL 9.2.0))
> Type "help" for help.
>
> testdb=> SELECT * FROM testtab ;
>  a | b
> ---+---
> (0 rows)
>
> testdb=> INSERT INTO testtab VALUES(1, 2);
> INSERT 0 1
> testdb=> SELECT * FROM testtab ;
>  a | b
> ---+---
>  1 | 2
> (1 row)
>
>
> I then create another user that was superuser:
>>
>> CREATE ROLE testsuper WITH LOGIN PASSWORD 'testpass' SUPERUSER INHERIT
>> CREATEDB CREATEROLE;
>>
>> and still testsuper is getting same error.  I also did pgxc_reload on all
>> the coordinators and datanodes and still getting this same error.
>>
>> However, when I log into the datanode, I get no issues to select from
>> said tables as the testuser. i actually connected from the coordinator
>> server as testuser to the datanode.
>>
>> I remember having this same issue w/ postgresXC, but I don't recall what
>> the resolution was for this.
>>
>> In the coordinator log i see the error:
>>
>> LOG:  failed to connect to Datanode
>> WARNING:  can not connect to node 16385
>> LOG:  failed to acquire connections
>> STATEMENT:  select * from test;
>> ERROR:  Failed to get pooled connections
>> STATEMENT:  select * from test;
>>
>>
> So if you connect to the coordinator as pgxl user, does everything work OK
> for you?
>
>
>>
>>
>> My datanode pg_hba looks like this:
>>
>> # Addition at initialization, 20140728_18:27:59
>> host all postgresxl 10.1.0.221/32 trust  #GTM
>> host all postgresxl 10.1.0.220/32 trust # Coord1
>> host all postgresxl 10.1.0.219/32 trust # Coord2
>> host all postgresxl 10.1.1.47/32 trust  #DN1
>> host all postgresxl 10.1.1.48/32 trust  #DN2
>> # End of additon
>>
>>
>> host all all 10.1.0.221/32 md5  #GTM
>> host all all 10.1.0.220/32 md5  #Coord1
>> host all all 10.1.0.219/32 md5  #Coord2
>> host all all 10.1.1.47/32 md5  #DN1
>> host all all 10.1.1.48/32 md5  #DN2
>>
>>
> Hmm. This piece looks different on my setup. I'd the auth method set to
> "trust" which AFAICS pgxc_ctl added because of the following line in
> pgxc_ctl.conf
> datanodePgHbaEntries=(::1/128)
>
> I tried changing "trust" to "md5" to match your configuration and I also
> started seeing the same error as you do. I'll look into it, but may be you
> can try with "trust" auth method for now and see if that helps.
>
> Thanks,
> Pavan
>
> --
> Pavan Deolasee
> http://www.linkedin.com/in/pavandeolasee
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-general-postgres-xl.org/attachments/20140729/78485314/attachment.htm>


More information about the postgres-xl-general mailing list