postgresql - user privilege for a particular database
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,
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 :)
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.
You can remove privileges from users on database by running.
REVOKE ALL PRIVILEGES ON DATABASE "database_name" from username;
By default all public scemas 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;
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.