current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 5 down vote favorite

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.

share|improve this question

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

This question came from our site for professional and enthusiast programmers.
up vote 5 down vote

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 

REVOKE [ GRANT OPTION FOR ]
    { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

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

share|improve this answer
up vote 1 down vote

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 0.0.0.0/0), 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.

share|improve this answer
up vote 1 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.