Tell me more ×
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. It's 100% free, no registration required.
up vote 3 down vote favorite

I am trying on the server side to restrict a user connecting on LAN to be able to see and access 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 pg_hba.conf file and even after doing it still does not work.

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

share|improve this question
 
This is kind of important, what's in the rest of pg_hba.conf before that? pg_hba.conf lines are run in order on a connection, and it takes the first match and ignores the rest. –  Scott Marlowe Nov 8 '11 at 18:24

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

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

3 Answers

up vote 4 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;
share|improve this answer
 
Thanks for the response but would revoking mean that each client connecting to server can be configured to have access to different databases?? –  SQLDev Nov 7 '11 at 11:38
 
@SQLDev: In PostgreSQL, each role (user or group) can be granted permission to access different databases if that's what you want to do. See postgresql.org/docs/current/static/user-manag.html –  Mike Sherrill 'Catcall' Nov 7 '11 at 12:08
 
Thanks...I have tried these but it just gives fatal error and is not allowing me to login to the database to see the required database.Can you please help with an example?? –  SQLDev Nov 7 '11 at 12:09
 
@SQLDev: Terminology? You login to a PostgreSQL server. Having logged in to a PostgreSQL server, you connect to one of that server's databases. There's a working example (except for the literals "database-name" and "fishy_user") in my answer. If you can't login to a PostgreSQL server, or if you can't connect to a particular database, you can't manage permissions for that database. Could there be a problem with your changes to pg_hba.conf? –  Mike Sherrill 'Catcall' Nov 7 '11 at 12:18
2  
A couple of corrections. a) "If you make changes to pg_hba.conf, you need to reload PostgreSQL"; b) Logging to Postgres always involves connecting to a database. You may later connect to a different database but you have to be connected to one at any given time. –  Milen A. Radev Nov 7 '11 at 13:13
show 1 more comments
up vote 1 down vote

Only chanage 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
up vote 0 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

Your Answer

 
discard

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