Restrict Other User's Access From Your Database

Sometimes we like to keep our database from other users. By default, all databases are associated with a PUBLIC group. Any user can read your database even though you didn't assign any permission to the user.

Photo by Charlie Hammond on Unsplash

Postgresql:

Case #1:

Revoke all permissions for users on the specific databases but still, user can have read access to the Database

Create two different users:

-- User for mydb1
CREATE USER db1user WITH ENCRYPTED PASSWORD 'password';
-- User for mydb2
CREATE USER db2user WITH ENCRYPTED PASSWORD 'password';

Create two other Databases:

CREATE DATABASE mydb1;
CREATE DATABASE mydb2;

Revoke All Privileges:

REVOKE ALL PRIVILEGES ON database mydb1 FROM db1user;
REVOKE ALL PRIVILEGES ON database mydb2 FROM db2user;

But still, we can access the databases by two users with read access. This happens because, by default, databases are associated with a PUBLIC group.

Revoke connect privileges from PUBLIC and allow specific users to connect to the databases.