0

I have multiple databases in postgreSQL. I have created unique users with the intention of giving them access to a unique database. After creating the users, the first thing I did was use the following command:

GRANT ALL PRIVILEGES ON DATABASE dbname to username;

Then all the created users could connect to all the existing databases. After this I tried with a new user without granting any permissions. Instead I created a new role with connect on privilege to a particular database and attached this role to the new user. But the result was same, the new user could connect to any database. I then tried revoking all privileges with the command:

REVOKE ALL ON DATABASE dbname FROM PUBLIC;

The issue remains.

adityabhuvanraj
  • 25
  • 1
  • 13
  • Does this answer your question? [Restrict user privileges to one particular database only](https://stackoverflow.com/questions/11012441/restrict-user-privileges-to-one-particular-database-only) – Francisco Puga Mar 16 '22 at 09:28

1 Answers1

1

Look at the permissions on the database:

SELECT datacl FROM pg_database WHERE datname = 'dbname';

If that is NULL, then the default permissions apply: the owner of the database has all privileges, and PUBLIC can CONNECT and TEMP.

In that case, your REVOKE statement would prevent username from connecting to that database, unless username is the owner of the database (or a member of the owner).

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263