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 :)

https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path — JustBeingHelpful, Apr 29 '22 at 01:12
https://www.cvedetails.com/vulnerability-list/vendor_id-336/product_id-575/Postgresql-Postgresql.html — JustBeingHelpful, Apr 29 '22 at 01:12

score 13 · Answer 1 · edited Nov 12 '20 at 08:48

13

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

REVOKE ALL PRIVILEGES ON DATABASE database_name FROM username;

edited Nov 12 '20 at 08:48

zb226

9,586
6
49
79

answered Nov 05 '15 at 21:14

Jonathan Haar

574
4
10

Neurotransmitter · Answer 2 · 2020-10-13T08:39:43.347

7

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.

edited Oct 13 '20 at 08:39

answered Mar 12 '17 at 14:58

Neurotransmitter

6,289
2
51
38

DrColossos · Answer 3 · 2012-06-13T11:01:06.953

6

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.

edited Jun 13 '12 at 11:01

answered Jun 13 '12 at 10:34

DrColossos

12,656
3
46
67

@MAHI did a little research, check out the link with an answer in the mailing list. – DrColossos Jun 13 '12 at 11:01
I think that is a logical explanation indeed @DrColossos. In any case it's worth a try. – pyrocumulus Jun 13 '12 at 11:02
@MAHI: You also need to revoke connect from public – Jun 13 '12 at 14:07

score 1 · Answer 4 · edited Sep 16 '17 at 17:41

1

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.

edited Sep 16 '17 at 17:41

ian

12,003
9
51
107

answered Jun 13 '12 at 10:06

pyrocumulus

9,072
2
43
53

thanks for your reply, is this only the option to do so, it will be difficult to configure for every new user..... – MAHI Jun 13 '12 at 10:12
I do not know, I would not expect it to be the only option. When I have some spare time tonight, I'll build a test situation on my local Postgres server. If I get it to work w/o the pg_hba.conf change, I'll let you know :) – pyrocumulus Jun 13 '12 at 10:16

Restrict user privileges to one particular database only

4 Answers4

Linked