4 Replies Latest reply on Jan 28, 2014 9:59 PM by Nelson Calero

    Postgresql Connection Issue - v4.0.0.13

    716245
    716245 Jan 14, 2014 10:28 PM

      I added my Third Party JDBC drivers for Postgresql, the tab shows up.

       

      However, after I fill in the Username/Pwd and Hostname/Port and then select Choose Database - I get:

       

      Failure -FATAL: database "bi_app_user" does not exist

       

      Anyone else having this issue?

       

      The JDBC driver I am using is:

       

      postgresql-8.4-701.jdbc4.jar

      • 2864 Views
      • Tags:
        • 1. Re: Postgresql Connection Issue - v4.0.0.13
          716245
          716245 Jan 25, 2014 9:15 PM (in response to 716245)

          I have been working with the DBA and we have made some progress.  He added a DB with the same name as the USER.  That allowed the 'Choose Database' drop down to fill in and I was able to choose the DB and connect.  But, that is it.  There is nothing else associated to the connection, like tables, views, etc.  The only thing I can do is run a SQL statement. 

          • 2. Re: Postgresql Connection Issue - v4.0.0.13
            Nelson Calero
            Nelson Calero Jan 25, 2014 10:37 PM (in response to 716245)

            This error is because the user you are trying to use does not have enough privileges on the PG side.

             

            You should use the psql command line utility to test that your username ('psql -U user -W') is able to connect and see the database and tables that you want, and then use the same for the SQLDeveloper.

             

            Regards.

            • 3. Re: Postgresql Connection Issue - v4.0.0.13
              716245
              716245 Jan 27, 2014 5:10 PM (in response to Nelson Calero)

              The user does have appropriate permissions.  When I connect via ODBC using WinSQL, using the same user.  I can see tables, views, etc and run SQL.  Also, the WinSQL connection did not require that a dummy DB be created named the same as the USER.  Oracle SQL Developer is a nice tool and I use it for mostly Oracle development and a little SQL Server.  Sure wish GP would function better.

              • 4. Re: Postgresql Connection Issue - v4.0.0.13
                Nelson Calero
                Nelson Calero Jan 28, 2014 9:59 PM (in response to 716245)

                Well, what is happening is that SQL Developer uses INFORMATION_SCHEMA.* views to show information, and those views filter out information about objects that the user doesn't have any privileges for. While on the other hand, the PG catalog (pg_* views) will show you all information regarding your privileges, and those are probably used by your windows utility (it is the classical approach, also).

                 

                You can validate this granting the owner of the database to the connection user. It is not enough to grant CONNECT or "ALL PRIVILEGES ON DATABASE", you need to grant a role who owns the database you want to use (PG roles are users).

                 

                But this is a bit off-topic, it is a Postgres privilege issue, you can find a lot more information on PG forums.

                For example, the discussion about how INFORMATION_SCHEMA views filters information: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: Small clarification in "34.41. schemata")

                 

                Regards.