Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2006
    Posts
    15

    Unanswered: Postgresql ODBC and Access

    Information: Local network (TCP/IP)
    XP pc with MS Access
    Fedora Core 4 pc with Postgresql

    I want XP pc through Access to be able to import the Postgres databases on the Linux machine.

    What I already installed and changed:
    Linux machine:

    1. pg_hba.conf
    local all all trust
    host all all 127.0.0.1/32 trust
    host sameuser all 127.0.0.1 255.255.255.255 trust
    host test1 chris 192.168.0.0 255.255.255.0 md5
    host sameuser all 192.168.0.0 255.255.255.0 md5
    2. I created a postgresql user for hosts.

    XP pc:

    1.Installed pgsqodb-08_01_200.zip
    2.Configuring the ODBC driver and MS Access is to be used, I have set:
    Selected USER DSN with:

    Data Source : test1-dsn
    Database : test1
    Server : 192.168.0.1
    SSL mode : disabled
    Port : 5432
    Username and password being entered at login

    Options, Datasource, page 1:
    Disable Genetic Optimizer: yes
    Keyset Query Optimization: yes
    Recognize Unique Indexes: yes
    Use Declare/Fetch: yes
    CommLog: yes
    Parse Statements: yes
    Cancel as FreeStmt: no
    MyLog: yes
    Unknown Sizes: Maximum
    Text as LongVarChar: yes
    Unknowns as LongVarChar: no
    Bool as Char: yes
    Max Varchar: 254
    Max LongVarChar: 8190
    Cache Size: 100
    SysTable Prefixes: dd_;
    Options, Datasource, page 2:
    Read Only: no
    Show System Tables: no
    LF <-> CR/LF conversion: yes
    Updatable Cursors: yes
    bytea as LO: no
    Row Versioning: no
    Disallow Premature: no
    True is -1: yes
    Server side prepare: no
    Int8 As: default
    OID Options: (none checked)
    Connect Settings: (blank)

    Problem: When I open MS Access , create new db, go to where I select Machine Data Source
    and select test1-dsn, enter my user name and press OK.
    Access just hangs with this in postgres log file:CONN ERROR: func=PGAPI_GetConnectOption, desc='fOption=30002', errnum=205, sqlstate=, errmsg='Unknown connect option (Get)'


    !Any ideas are welcome!!
    !!Thanks in Advance!!

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    try it with SSL Mode set to Preferred

    I assume that your WinXP client is on 192.168.0.0 ?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Also, to enable network communications every time the server starts, you should update configuration file postgresql.conf, as outlined in chapter 17 of the v8.1 online documentation.

    Chapter 17.3, in particular, describes the settings required for internet access.

    I would suggest that you ensure that
    Code:
    listen_addresses = '*'
    in postgresql.conf. This will require a restart of the postgres service.
    Last edited by loquin; 03-24-06 at 15:24.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    Mar 2006
    Posts
    15
    Thanks for feedback

    WinXP client : 192.168.0.0
    listen_addresses = '*'
    I tried SSL Mode set to Preferred

    I get the same error message.

    Q: Should the "tcpip_socket= true" be enabled in the postgresql.conf file.
    When I enable it postgres don't restart with following error message:
    FATAL: unrecognized configuration parameter "tcpip_socket"

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    What version of PostgreSQL?

    In version 8, per the release documentation,
    Server configuration parameters virtual_host and tcpip_socket have been replaced with a more general parameter listen_addresses. Also, the server now listens on localhost by default, which eliminates the need for the -i postmaster switch in many scenarios.
    Last edited by loquin; 03-24-06 at 17:12.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I BELIEVE the issue is in your pg_hba.conf configuration.

    Try this as a beginning:
    Code:
    host    all         all         192.168.0.0/32       trust
    This SHOULD accept all logons from your client machine to all databases on the server, without requiring a password.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Mar 2006
    Posts
    15
    I've got version 8.
    I just read it on an article where the author used postgresql-7.2.3-5.80 with
    tcpip_socket setup.

    Q: When importing an table into Access, I named only the database I created the same as the postgres (ie. test1) and then selected File-Get External Data-Import-....
    Is this suppose to be sufficient for importing into Access?

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by Gurr
    I've got version 8.
    v8.0.x or 8.1.x?
    Quote Originally Posted by Gurr
    Q: When importing an table into Access, I named only the database I created the same as the postgres (ie. test1) and then selected File-Get External Data-Import-....
    Is this suppose to be sufficient for importing into Access?
    Yes - It should be - I just followed that approach & imported one table & linked to the information schema with another.

    If you would, copy/paste the pga_hba.conf file to a reply, or, better yet, just attach the file to a reply, so I could take a look.
    Last edited by loquin; 03-24-06 at 17:43.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  9. #9
    Join Date
    Mar 2006
    Posts
    15
    v8.0.x or 8.1.x?
    I've got version: postgresql-8.1.2

    Note: Changed my XP pc's ip-address to 192.168.0.69

    Here is my pg_hba.conf file.

    # PostgreSQL Client Authentication Configuration File
    # ================================================== =
    #
    # Refer to the PostgreSQL Administrator's Guide, chapter "Client
    # Authentication" for a complete description. A short synopsis
    # follows.
    #
    # This file controls: which hosts are allowed to connect, how clients
    # are authenticated, which PostgreSQL user names they can use, which
    # databases they can access. Records take one of these forms:
    #
    # local DATABASE USER METHOD [OPTION]
    # host DATABASE USER CIDR-ADDRESS METHOD [OPTION]
    # hostssl DATABASE USER CIDR-ADDRESS METHOD [OPTION]
    # hostnossl DATABASE USER CIDR-ADDRESS METHOD [OPTION]
    #
    # (The uppercase items must be replaced by actual values.)
    #
    # The first field is the connection type: "local" is a Unix-domain socket,
    # "host" is either a plain or SSL-encrypted TCP/IP socket, "hostssl" is an
    # SSL-encrypted TCP/IP socket, and "hostnossl" is a plain TCP/IP socket.
    #
    # DATABASE can be "all", "sameuser", "samerole", a database name, or
    # a comma-separated list thereof.
    #
    # USER can be "all", a user name, a group name prefixed with "+", or
    # a comma-separated list thereof. In both the DATABASE and USER fields
    # you can also write a file name prefixed with "@" to include names from
    # a separate file.
    #
    # CIDR-ADDRESS specifies the set of hosts the record matches.
    # It is made up of an IP address and a CIDR mask that is an integer
    # (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that specifies
    # the number of significant bits in the mask. Alternatively, you can write
    # an IP address and netmask in separate columns to specify the set of hosts.
    #
    # METHOD can be "trust", "reject", "md5", "crypt", "password",
    # "krb5", "ident", or "pam". Note that "password" sends passwords
    # in clear text; "md5" is preferred since it sends encrypted passwords.
    #
    # OPTION is the ident map or the name of the PAM service, depending on METHOD.
    #
    # Database and user names containing spaces, commas, quotes and other special
    # characters must be quoted. Quoting one of the keywords "all", "sameuser" or
    # "samerole" makes the name lose its special character, and just match a
    # database or username with that name.
    #
    # This file is read on server startup and when the postmaster receives
    # a SIGHUP signal. If you edit the file on a running system, you have
    # to SIGHUP the postmaster for the changes to take effect. You can use
    # "pg_ctl reload" to do that.

    # Put your actual configuration here
    # ----------------------------------
    #
    # If you want to allow non-local connections, you need to add more
    # "host" records. In that case you will also need to make PostgreSQL listen
    # on a non-local interface via the listen_addresses configuration parameter,
    # or via the -i or -h command line switches.
    #
    # TYPE DATABASE USER CIDR-ADDRESS METHOD

    # "local" is for Unix domain socket connections only
    local all all trust
    # IPv4 local connections:
    host all all 127.0.0.1/32 trust
    # IPv6 local connections:
    #host all all ::1/128 ident sameuser
    #test 24/3/2006
    host sameuser all 127.0.0.1 255.255.255.255 trust
    host test1 chris 192.168.0.69 255.255.255.0 md5
    host all all 192.168.0.69/32 trust

  10. #10
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    That should work.

    are you able to connect with pgAdmin from your machine?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  11. #11
    Join Date
    Mar 2006
    Posts
    15
    I have not installed pgAdmin. Will install it and give you feedback on it.

  12. #12
    Join Date
    Mar 2006
    Posts
    15
    I have installed the latest release of pgAdmin3.
    When I try connecting to the server, pgAdmin also hangs with
    log file : STATUS : Connecting to database...

    Note: I've connected through Openoffice to the postgres database on the
    same machine.

  13. #13
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    OK. Try changing your pg_hba.conf line

    Code:
    host test1 chris 192.168.0.69 255.255.255.0 md5
    to

    Code:
    host test1 chris 192.168.0.69 255.255.255.0 trust
    Then, try to log on to database "test1" using logon name "chris" with no password using pgAdmin.

    Also, here's a pertinent portion of the postgresql.conf file in our test server.

    Code:
    # - Connection Settings -
    
    listen_addresses = '*'	# what IP interface(s) to listen on; 
    				# defaults to localhost, '*' = any
    port = 5432
    max_connections = 50
    	# note: increasing max_connections costs about 500 bytes of shared
    	# memory per connection slot, in addition to costs from shared_buffers
    	# and max_locks_per_transaction.
    #superuser_reserved_connections = 2
    Make SURE that you've set listen_addresses to '*', so as to listen on any address...
    Last edited by loquin; 03-28-06 at 13:30.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  14. #14
    Join Date
    Mar 2006
    Posts
    15
    I double checked everything in pg_hba

    Code:
    host test1 chris 192.168.0.69 255.255.255.0 trust
    Code:
    listen_addresses = '*'	
    port = 5432
    max_connections = 50
    	# note: increasing max_connections costs about 500 bytes of shared
    	# memory per connection slot, in addition to costs from shared_buffers
    	# and max_locks_per_transaction.
    Still the same problem.

    I don't anymore.

  15. #15
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    OK. Next, try this in pg_hba.conf

    Code:
    host all all  192.168.0.69/32 trust
    This should allow any user from your machine trusted access to all databses on the server. (We'll tighten it up in a bit)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •