Comments (11)
Can you add an R program that causes rpostgresql to fail prior to modification
and that passes after modification?
Thank you.
Neil
Original comment by [email protected]
on 9 Sep 2010 at 3:33
from rpostgresql.
the R program is the simplest one as:
> library("RPostgreSQL")
> m <- dbDriver("PostgreSQL")
> con <- dbConnect(m)
which is expected nearly equivalent to call just "psql" on the shell.
So, I assume your question is how to reproduce the failure to connect.
PostgreSQL have quite a number of ways for its authentication, which is
regulated by $PGDATA/pg_hba.conf, which distinguish unix domain connection
and IP connection.
The file with default install on CentOS lookslike:
# "local" is for Unix domain socket connections only
local all all ident
# IPv4 local connections:
host all all 127.0.0.1/32 ident
# IPv6 local connections:
host all all ::1/128 ident
after creating role and database which are named same as the user name
psql will connect to the default database which has the same name like
$ psql
psql (8.4.4)
Type "help" for help.
tomoaki=>
however
$ psql -h localhost
psql: FATAL: Ident authentication failed for user "tomoaki"
will fail, presumably because of identd or firewall setup.
But the point is that there is unix domain connection that is different
from ip connection, and in fact we can setup intentionally to deny IP connection
as well.
Current code makes no way to connect via unix domain and
con <- dbConnect(m)
corresponds to "psql -h localhost template1"
instead of "psql"
result before patch (but the patch for issue 21 was applied for compilation)
$ R --no-save < test-connect-psql.r
R version 2.11.1 (2010-05-31)
Copyright (C) 2010 The R Foundation for Statistical Computing
ISBN 3-900051-07-0
R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.
Natural language support but running in an English locale
R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.
Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.
> library("RPostgreSQL")
Loading required package: DBI
> m <- dbDriver("PostgreSQL")
> con <- dbConnect(m)
Error in postgresqlNewConnection(drv, ...) :
RS-DBI driver: (could not connect @localhost on dbname "template1"
)
Calls: dbConnect ... .valueClassTest -> is -> is -> postgresqlNewConnection ->
.Call
Execution halted
result after patch
$ R --no-save < test-connect-psql.r
R version 2.11.1 (2010-05-31)
Copyright (C) 2010 The R Foundation for Statistical Computing
ISBN 3-900051-07-0
R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.
Natural language support but running in an English locale
R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.
Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.
> library("RPostgreSQL")
Loading required package: DBI
> m <- dbDriver("PostgreSQL")
> con <- dbConnect(m)
>
During writing this, I additionally found that the
error message can be improved and updated the patch (attached as diff2)
I also attach a failing script with con <- dbConnect(m, host="localhost")
The new error message reads
Error in postgresqlNewConnection(drv, ...) :
RS-DBI driver: (could not connect tomoaki@localhost on dbname "tomoaki"
)
The point is that even if the connection fails, PQuser() and PQdb() will
return the default user and database name which was not supplied.
Also we should avoid passing NULL pointer to printf function although
some system works fine, but some may segfault (for host).
A small test program to understand the behavior of libpq is also attached.
Original comment by [email protected]
on 10 Sep 2010 at 1:28
Attachments:
from rpostgresql.
That looks like another great patch by bringing the Postgresql-specific code in
whereas Sameer initially just copied what was used on RMySQL to set up
connection strings.
Original comment by dirk.eddelbuettel
on 11 Sep 2010 at 2:04
from rpostgresql.
One comment though where I disagree. You wrote
however
$ psql -h localhost
psql: FATAL: Ident authentication failed for user "tomoaki"
will fail, presumably because of identd or firewall setup.
AFAIK this has always used tcp/ip connections and we need to support those too.
PostgreSQL is not SQLLite -- we can work across machines.
But the point is that there is unix domain connection that is different
from ip connection, and in fact we can setup intentionally to deny IP connection
as well.
No. I want them to work by default. "Setup intentionally" is very wrong.
Having applied your 'diff2' patch above, I now a get seg.fault from
tests/selectWhereZero.R which would be a regression:
$ ./check_with_vars.sh # simple script that loops over tests setting env vars
[...]
==== Running RPostgreSQL/tests/selectWhereZero.R
Loading required package: RPostgreSQL
Loading required package: DBI
[1] "Removing tmpirisdata\n"
*** buffer overflow detected ***: /usr/lib64/R/bin/exec/R terminated
Could you take a look if this is related to the patch?
Thanks, Dirk
Original comment by dirk.eddelbuettel
on 11 Sep 2010 at 3:26
from rpostgresql.
I added the test script 'check_with_vars.sh' to SVN.
Original comment by dirk.eddelbuettel
on 11 Sep 2010 at 4:31
from rpostgresql.
I didn't get that segfault.
What I got with the today svn version is:
==== Running RPostgreSQL/tests/selectWhereZero.R
Loading required package: RPostgreSQL
Loading required package: DBI
Error in postgresqlExecStatement(conn, statement, ...) :
RS-DBI driver: (could not Retrieve the result : ERROR: must be superuser to COPY to or from a file
HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works
for anyone.
)
Error in postgresqlExecStatement(conn, statement, ...) :
RS-DBI driver: (could not Retrieve the result : ERROR: operator does not exist: text = integer
LINE 1: select * from tmpirisdata where Species=0
^
HINT: No operator matches the given name and argument type(s). You might need
to add explicit type casts.
)
In addition: Warning message:
In postgresqlWriteTable(conn, name, value, ...) :
could not load data into table
NULL
[1] "Removing tmpirisdata\n"
[1] TRUE
Warning message:
In postgresqlQuickSQL(conn, statement, ...) :
Could not create executeselect * from tmpirisdata where Species=0
What I got with the previous local version (which has patched for dbWriteTable
as posted to issue 13)
==== Running RPostgreSQL/tests/selectWhereZero.R
Loading required package: RPostgreSQL
Loading required package: DBI
Error in postgresqlExecStatement(conn, statement, ...) :
RS-DBI driver: (could not Retrieve the result : ERROR: operator does not exist: text = integer
LINE 1: select * from tmpirisdata where Species=0
^
HINT: No operator matches the given name and argument type(s). You might need
to add explicit type casts.
)
NULL
[1] "Removing tmpirisdata\n"
[1] TRUE
Warning message:
In postgresqlQuickSQL(conn, statement, ...) :
Could not create executeselect * from tmpirisdata where Species=0
This is somewhat better than the latest svn version in that the data
seems created properly.
If I further change the query in selectWhereZero.R
to Sepcies='0', I got:
==== Running RPostgreSQL/tests/selectWhereZero.R
Loading required package: RPostgreSQL
Loading required package: DBI
data frame with 0 columns and 0 rows
[1] "Removing tmpirisdata\n"
[1] TRUE
----------------------------
The above test was run with
export POSTGRES_USER="tomoaki"
export POSTGRES_PASSWD=""
export POSTGRES_HOST="localhost"
export POSTGRES_DATABASE="tomoaki"
export POSTGRES_PORT="5432"
on a really single user computer having a line
host all all 127.0.0.1/32 trust
in the pg_hba.conf
Note, this should not be done on a multi-user system.
Since this is working and
host all all 127.0.0.1/32 ident
does not allow access, I presumed its identd or firewall problem
but not RPostgreSQL problem.
-----------------------------
AFAIK this has always used tcp/ip connections and we need to support those too. PostgreSQL is not SQLLite -- we can work across machines.
But the point is that there is unix domain connection that is different
from ip connection, and in fact we can setup intentionally to deny IP connection
as well.
No. I want them to work by default. "Setup intentionally" is very wrong.
I do agree that it working by default is desirable. However it is
a matter of PostgreSQL and PostgreSQL packaging by the distributions.
For the part of connector of R and PostgreSQL it is best to work as psql
does. If psql connect, RPostgreSQL does connect.
In a general network where there are many user and hosts, the authentication
is not a simple task, and I don't blame if distribution default is restrictive
and the administrator needs to setup the allowed way of connection under their
policy.
Original comment by [email protected]
on 12 Sep 2010 at 1:44
Attachments:
from rpostgresql.
Hi
Thanks for the follow-up! I agree that something odd is going on with the
segfault. I can trigger it one way but not the other.
I still disagree on the socket vs tcp-ip access isssue, but agree that when
psql can connect (using -h somehost), RPostgreSQL should too.
But would you mind posting the discussion on our mailing list
(see http://groups.google.com/group/rpostgresql-dev)
instead? I find it awfully hard to quote what you wrote to discuss some points
in
detail.
That said, I am *very very happy* that you are contributing so actively.
Bye, Dirk
Original comment by dirk.eddelbuettel
on 12 Sep 2010 at 3:09
from rpostgresql.
I can confirm the problem with selectWhereZero.R on mac os x with svn -r140
-------------- versions
----------------------
ProductName: Mac OS X
ProductVersion: 10.6.4
BuildVersion: 10F569
RPostgreSQL svn version: 140
psql (PostgreSQL) 9.0beta3
R version 2.11.1 (2010-05-31)
x86_64-apple-darwin9.8.0
locale:
Package: RPostgreSQL
Version: 0.1-7
Packaged: NA
Built: R 2.11.1; universal-apple-darwin9.8.0; 2010-09-12 12:55:05 UTC;
--------------start RPostgreSQL/tests/selectWhereZero.R
----------------------
Loading required package: RPostgreSQL
Loading required package: DBI
./check_with_vars_neil.sh: line 74: 18095 Abort trap R --arch=i386
--slave < RPostgreSQL/tests/selectWhereZero.R
Original comment by [email protected]
on 12 Sep 2010 at 12:58
from rpostgresql.
segfault with selectWhereZero.R on Mac OS X was reported as issue 19 on 24 Aug.
Do you find any differene with the recent changes?
If not, I guess its different problem that is independent of dbConnect() change.
Original comment by [email protected]
on 12 Sep 2010 at 1:26
from rpostgresql.
Reassigned.
Original comment by [email protected]
on 13 Oct 2010 at 2:34
- Changed state: Accepted
from rpostgresql.
This is considered to have been fixed at r138, though some more minor cleanups
of the code may be possible.
Original comment by [email protected]
on 14 Oct 2010 at 2:37
- Changed state: Fixed
from rpostgresql.
Related Issues (20)
- Get autogenerated keys
- Bug linker flag when build with/link to exist postgresql library in custom path HOT 3
- Usage of internal libpq distribution is broken
- Outdated config.guess and config.sub HOT 2
- Error connecting to postgres 13 using RPostgres package in R. “authentication method 10 not supported” HOT 6
- Error loading RPostgreSQL HOT 6
- 'dbWriteTable()' does not write data out of public schema HOT 4
- configure: error: cannot guess build type; you must specify one in [install.packages("RPostgreSQL")] HOT 2
- Issue installing RPostgreSQL on linux HOT 6
- Unable to install in R HOT 6
- Create Roles and users from RPostgreSQL HOT 4
- dbSendQuery segfault on bad SQL query HOT 1
- How to find headers in /usr/include/psql? HOT 2
- How to pass binary_parameters="yes"? HOT 1
- please support PQconnectdb() interface HOT 2
- `dbConnect()` crashes R Studio instead of erroring HOT 4
- CRAN NEWS and Changelog are out of date
- libpq version 10 or above required for authentication HOT 6
- Old problem: libpg version problems HOT 5
- RPostgreSQL 0.7-4 isn't working with libpq 15.1? HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from rpostgresql.