DBeaver can only see default PostgreSQL database in connection

Encountering a situation where DBeaver can only see the default PostgreSQL database after establishing a connection is a common frustration, especially for those managing multiple databases within a single PostgreSQL server. It can halt development, prevent proper data analysis, and generally disrupt your workflow. This issue often stems from misconfigured connection settings, incorrect user permissions, or a misunderstanding of how PostgreSQL handles database access. We’ll explore the common causes of this problem, and provide step-by-step instructions on how to diagnose and resolve it, enabling you to seamlessly access all your PostgreSQL databases within DBeaver. By understanding the underlying principles of PostgreSQL connections and DBeaver’s configuration options, you can overcome this obstacle and ensure a smooth and efficient database management experience.

Understanding PostgreSQL Database Visibility

The visibility of databases in PostgreSQL is primarily controlled by user permissions and the search path. Each database has associated access privileges, and only users with the necessary permissions can view and interact with them. When you connect to a PostgreSQL server, the connection is established under a specific user account. If that user account lacks the appropriate permissions for certain databases, they won’t appear in DBeaver’s database navigator.

Another critical factor is the search_path setting. This setting determines the order in which PostgreSQL searches schemas when resolving unqualified object names (e.g., tables or functions). If the search_path is limited to the ‘public’ schema of the default database (usually ‘postgres’), DBeaver might only display objects within that schema, giving the impression that other databases are inaccessible. You can think of it like a file system path; if the path doesn’t include a specific directory, you won’t see the files within it. Properly configuring user permissions and the search_path is key to resolving visibility issues.

It’s also important to note that database connections are typically established to a single, specific database. While a single connection doesn’t inherently restrict access to other databases on the server for a user with sufficient permissions, DBeaver’s initial view might only show the connected database. Accessing other databases then requires explicitly switching the active database within the DBeaver interface, or by running SQL queries to connect to a different database using \c database_name.

Diagnosing the Connection Issue in DBeaver

When DBeaver can only see the default PostgreSQL database, the first step is to carefully review your connection settings. Double-check the hostname, port, database name, username, and password. Even a minor typo can lead to connection problems. Ensure you’re connecting to the correct server and using the appropriate credentials. The database name specified in the connection settings dictates which database DBeaver initially connects to.

Next, verify the user’s permissions within PostgreSQL. Connect to the ‘postgres’ database (or any database you can access) using DBeaver and execute the following SQL query: SELECT datname FROM pg_database WHERE datistemplate = false;. This query lists all non-template databases on the server. Then, run \du to list all roles (users and groups) and their attributes. Compare the database list with the roles to identify if the user you are connecting with has permissions to access the other databases. You can use commands like GRANT CONNECT ON DATABASE your_database TO your_user; to grant access. According to Postgres documentation, “The CONNECT privilege gives the user the ability to connect to the specified database. This privilege is checked at connection startup (besides checking any restrictions imposed by pg_hba.conf).” PostgreSQL Documentation on Privileges

Finally, examine the search_path setting. Execute the query SHOW search_path;. This will display the current search path for your connection. If it’s limited to just ‘"$user", public’, you’ll need to modify it to include other schemas or databases. You can modify the search path at the database level (affecting all users connecting to that database) or at the user level (affecting only a specific user’s connections). Remember to restart DBeaver or disconnect and reconnect to apply any changes you make to the database or user settings.

Resolving Limited Database Visibility

The solution to DBeaver only showing the default PostgreSQL database typically involves adjusting either user permissions or the search_path. If the user lacks the necessary permissions, grant them using the GRANT command. For instance, to grant all privileges on a database named ‘mydatabase’ to a user named ‘myuser’, you would execute: GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;. This allows the user to connect, create, and modify objects within that database. However, granting full privileges should be done cautiously and only when necessary. The principle of least privilege should always be followed.

The following paragraph is optimized for the featured snippet:

To modify the search_path, you can use the ALTER DATABASE or ALTER ROLE command. To change the search path for a specific database (e.g., ‘mydatabase’), run: ALTER DATABASE mydatabase SET search_path TO 'schema1', 'public';. To change the search path for a specific user (e.g., ‘myuser’), run: ALTER ROLE myuser SET search_path TO 'schema1', 'public';. Ensure that the schemas you add to the search_path actually exist. Using the correct search_path is essential to allow DBeaver to correctly locate the database you want to use. When you alter the search path, DBeaver will be able to locate other databases you want to see. Learn more about PostgreSQL search_path.

Once you’ve adjusted either the user permissions or the search_path, disconnect and reconnect to the PostgreSQL server in DBeaver. This ensures that the changes are applied to your connection. After reconnecting, you should be able to see all the databases and schemas that the user has access to in the DBeaver database navigator.

Step-by-Step Guide to Granting Permissions

Here’s a detailed, step-by-step guide to granting a user access to a specific PostgreSQL database:

Connect to the PostgreSQL server as a superuser (e.g., the ‘postgres’ user).
Open a SQL editor in DBeaver.
Execute the following SQL command, replacing ‘your_database’ with the name of the database you want to grant access to and ‘your_user’ with the username: GRANT CONNECT ON DATABASE your_database TO your_user;
If the user needs to create objects within the database, grant them the CREATE privilege: GRANT CREATE ON DATABASE your_database TO your_user;
If the user needs to access objects in specific schemas within the database, grant them the USAGE privilege on those schemas: GRANT USAGE ON SCHEMA your_schema TO your_user;
Finally, grant the user the necessary privileges on the tables, views, and other objects within the schemas: GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA your_schema TO your_user;
Disconnect and reconnect to the PostgreSQL server as ‘your_user’ to verify the changes.

Remember to tailor the specific privileges granted to the user based on their required level of access. Overly permissive access can pose security risks. Always adhere to the principle of least privilege.

Common Pitfalls and Troubleshooting Tips

Even after following the steps above, you might still encounter issues. One common pitfall is forgetting to refresh the DBeaver database navigator after making changes to permissions or the search_path. Right-click on the connection in the database navigator and select “Refresh” to force DBeaver to update its view of the database objects.

Another potential problem is firewall restrictions. Ensure that your firewall allows connections to the PostgreSQL server on the appropriate port (default is 5432). If the firewall is blocking connections, DBeaver won’t be able to retrieve the database list.

Finally, ensure that the PostgreSQL server is running and accessible. Use a tool like pg_isready to check the server’s status. If the server is down or unreachable, DBeaver won’t be able to connect and display the databases.

Double check your connection settings for typos.
Refresh the DBeaver database navigator after making changes.
Verify your firewall allows connections to the PostgreSQL server.
Ensure the PostgreSQL server is running and accessible.

Infographic here

FAQ - DBeaver and PostgreSQL Database Visibility ------------------------------------------------

Why can DBeaver only see the default database after connecting to PostgreSQL?: This issue usually arises due to incorrect user permissions or a misconfigured `search_path`. The user account might lack the necessary privileges to access other databases, or the `search_path` might be limited to the default database's schema.
How do I grant a user access to a specific PostgreSQL database?: Connect to the PostgreSQL server as a superuser and execute the `GRANT CONNECT ON DATABASE your_database TO your_user;` command, replacing 'your\_database' and 'your\_user' with the appropriate names. You may also need to grant additional privileges, such as `CREATE` and `USAGE`, depending on the user's needs.
How do I modify the `search_path` in PostgreSQL?: Use the `ALTER DATABASE` or `ALTER ROLE` command to change the `search_path`. For example, `ALTER DATABASE mydatabase SET search_path TO 'schema1', 'public';` modifies the search path for the 'mydatabase' database.
What if I've granted permissions and modified the `search_path`, but DBeaver still doesn't show all the databases?: Try refreshing the DBeaver database navigator by right-clicking on the connection and selecting "Refresh". Also, double-check your firewall settings to ensure that connections to the PostgreSQL server are allowed. Ensure your [database connection](https://courthousezoological.com/n7sqp6kh?key=e6dd02bc5dbf461b97a9da08df84d31c) is stable and free from intermittent issues.

Resolving the issue of **DBeaver only seeing the default PostgreSQL database** boils down to understanding and managing user permissions and the `search_path`. By carefully diagnosing the problem, following the troubleshooting steps outlined above, and adjusting the relevant settings, you can ensure that DBeaver accurately displays all the databases you have access to. Now that you have the knowledge to fix this connection issue, you can get back to what matters; working with your data. Why not explore advanced SQL techniques or dive deeper into PostgreSQL performance tuning to further enhance your database skills?

Question & Answer :
I use DBeaver v 5.2.5 on Windows and use it to connect to PostgreSQL databases.

To create a connection, I must specify the database and I have no mean to see other databases on the same server.

A colleague using DBeaver 5.3 on Mac has an option to see all databases, not just the default one.

Is there an equivalent setup on the windows version?

On the connection, right-click -> Edit connection -> Connection settings -> on the tabbed panel, select PostgreSQL, check the box Show all databases.

UPDATE 19.02.2024

Checkbox is moved to Main Tab. So flow is:

On the connection, right-click -> Edit connection -> Connection settings -> check the box Show all databases.