How to restrict a user to access a specific database?

[osman7king]

We are trying to switch from Oracle 10g to Postgre but I am having few technical issues which I am listing here and I hope someone can help.

Server Status:
- I am installing “postgresql-9.0.2-1-windows.exe” package on Windows Server 2003 SP2 with 2 LAN Cards
- The server is part of a domain and I am installing using the Administrator login
- Before installation we have removed completely the AV software

Installation:
- We have chose the following directories during installation:
D:\PostgreSQL\9.0
D:\PostgreSQL\9.0\data
- The PG user was created in the domain users with a pw that was accepted
- During installation, The locale is English, United States and not to [Default locale]
- During installation, starting the database server, this error shows
Warning: Problem running post-install step. Installation may not complete correctly
Failed to start the database server.
I clicked OK and continue
- After installation completion, the service "postgresql-9.0 - PostgreSQL Server 9.0" will fail. It is setup to start Automatically
Services: could not start the postgresql-9.0 service on Local Computer.
Error 1069: The service did not start due to a login failure.
- When checking the service login, I found out the user to be .\postgres so browse for it in the domain and changed it to DOMAIN_NAME\postgres, re-start the service it worked

Configuration Problems:
1. In postgresql.conf file when I changed the listen address to one of my LAN card to channel all traffic through it by doing: listen_addresses = '192.168.1.212' instead of listen_addresses = '*', re-starting the service will not work, changing the entry back and re-starting the service will not work, changing the entry back and re-start the whole server will work and the service will start

2. In pg_hba.conf I have the following entries:
host all all 127.0.0.1/32 md5
host all all 192.168.1.211/32 md5
host all all 192.168.1.212/32 md5
host all all 192.168.1.4/32 md5

using PGAdmin from the server itself, I created 2 New login Role “1” and “2” , 2 Tablespace “1” and “2” as this
D:\tablespace\1
D:\tablespace\2
which is next to the PG directory D:\PostgreSQL
and had to gave permission to the user: DOMAIN_NAME\postgres to D:\tablespace directory
then created the database “1” and “2” with the prospective user as owner and the tablespace

Logging from the remote machine with IP: 192.168.1.4 using the user 1, I can see both 1 and 2 dbs
however when I changed the pg_hba.conf to
host 1 all 192.168.1.4/32 md5
restarting the service, I can’t login at all. This entry does not work

however; these entries will work:
host all all 192.168.1.4/32 md5
host all 1 192.168.1.4/32 md5
But still can see all the dbs
I need to have each user sees its own DB, how can I do that

[rski]

I need to have each user sees its own DB, how can I do that

If you want each user sees only its own database that would hard to do, because all database info are stored in pg_catalog 'schema' visible to all.
If you want each user to be able to connect only to its own database you can revoke from him CONNECT privilege for the other databases

REVOKE CONNECT ON DATABASE <db> FROM <user>;
 

[osman7king]

Thank you rski,

the second problem has been solved, but what about the problem number 1

thanks.