I am trying on the server side to restrict a user connecting on LAN to seeing and accessing only 1 database from the list of databases I have in my system.

How can I achieve this?

I have tried

host    db_name     all       (connecting_ip)/32          krb5

host    db_name     all       (connecting_ip)/32          ident

host    db_name     all       (connecting_ip)/32          crypt

host    db_name     all       (connecting_ip)/32          trust

These parameters have been changed in the pg_hba.conf file, and even after that, it still does not work.

Please help with a solution on how to fix this issue so that any user accessing a database in a server with a list of databases can see and access only the required database.

migrated from stackoverflow.com Nov 7 '11 at 12:20

This question came from our site for professional and enthusiast programmers.

  • BTW You probably don’t want to use ident, crypt or trust for client connections. – eckes Jun 16 at 19:50

If you make changes to pg_hba.conf, you need to reload PostgreSQL.

But I think you'd be better off revoking the CONNECT privilege for that user. (Or possibly revoking ALL privileges.) Skeleton syntax is

    ON DATABASE database_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]

Something like

REVOKE CONNECT ON DATABASE database-name FROM fishy_user;

In PostgreSQL, each role (user or group) can be granted permission to access different databases if that's what you want to do. See Database Roles


3 approaches to the problem

Separate cluster for that db This sets up a separate server instance on a different port number; this is cheaper than it seems because the OS will reuse (read-only) memory pages from the other cluster(s). This option will force a very clean separation. It will, however, increase the amount of work in doing upgrades, setting up backups, replication. Setting up common users.

pg_hba.conf I'm noting you're restricting by IP rather than role name. If there's another line permitting access to other databases, and ip ranges that include that ip (such as, they will be allowed to connect through that. Given a username valid for that db.

Connect permission on DB Requires that you're separating users based on their given username rather than (or in addition to) their IP address. It also requires that all the other databases have their "public" connect permissions revoked.

The latter two can be combined for a flexible separation of per-ip and per-user permissions.


Only change the database parameter in the pg_hba.conf file to the desired database_name you want the user to access.
And reload the pg_hba.conf file (pg_ctl reload -D data_dir).
Then the user will be able to see the list of databases but will not be able to connect.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy