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

    Postgresql Connection Issue - v4.0.0.13

    716245

      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

        • 1. Re: Postgresql Connection Issue - v4.0.0.13
          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

            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

              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

                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.