Restrict user privileges to one particular database only

Question

PostgreSQL Version 9.1,

i am logging into database with default user: "postgres" and my database contains default role "public"

list of database i have,

1.database1

2.database2

3.database3

now, i need to create a user "newuser" which will have only privilege to "database2", it should not login into other databases.

i tried using this syntax

create role newuser with login nosuperuser nocreatedb nocreaterole noinherit password 'newpassword';
revoke all privileges on database database1, database3 from newuser;

but still the "newuser" can login into other database(database1/database3) and it can select tables from other schema's. (tables in public schema is not listed)

please, anyone explain me the correct procedure to create a user and grant privileges to them.

i need a user who can have all privileges on a particular database only, he should not login to other database :)

Answer 1

You can remove privileges from users on the database by running:

REVOKE ALL PRIVILEGES ON DATABASE database_name FROM username;