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;

Answer 2

Have you tried to revoke the privilege to connect to a database? This should disallow any further operations on the database as well.

REVOKE CONNECT ON DATABASE your_db FROM user;

---

After a little digging, it became obvious, why the above was not working. Maybe the answer by Tom Lane will give you a better solution.

Answer 3

By default all public schemas will be available for regular (non-superuser) users. To prevent this, login as a superuser and issue a command:

REVOKE ALL ON DATABASE somedatabase FROM PUBLIC;

This will revoke all permissions from all users for a given database.

Answer 4

Well the way I always do this is via the pg_hba.conf, although I suspect it should be possible in the way you are trying to as well.

One would expect revoke all privileges to deny anything with the listed entities. So no connections let alone actually selecting. There might be something obvious that we are forgetting here.

Anyhow, this should be easy to resolve using the configuration file. Add the newuser and only list the database you want to allow there. It will effectively deny any connections from that user to any other database. Obviously use this in addition to the privilege setup you already have.