Postgres - Revoke access to public schema for a user

Question

As a DBA, I have created a user user1 to access abcd schema as per the request.

But, the user I have created is also getting read access to the schema public in the database. An application owner requested me to REVOKE the access to public schema from the user user1 that I have created. For that, I am using the following REVOKE statements, which are not revoking the access to public schema for the user user1.

REVOKE ALL ON schema public FROM user1;

...and then tried:

REVOKE USAGE ON SCHEMA public FROM PUBLIC;

I am still able to read a table in the public schema from user1 user.

Am I missing something here?

I have also taken the pg_dump of the database with schema-only to see if there any other grants that are assigned to the user user1 but found none.

Answer 1

The public schema is unique in that anyone that connects to the database inherits a public role. Unlike other user-defined roles, the public role cannot be dropped -- it's a "reserved" role -- but its permissions can be modified.

Removing Permissions

The solution might be a little more cumbersome than you'd think. First, you were close with revoking the users permissions. That is an ability, but you never explicitly granted the user permission, they only inherited by default. So remove the permissions from the default:

REVOKE ALL ON SCHEMA public FROM public;

At this point the only users that can see the public schema and its objects are database owners and superusers. Remember, permissions are additive (not restrictive).

Granting Permissions

Since you removed the ability for everyone to access, if you'd like others to be able to access public you'll have to be explicit and grant them access.

Command	Description
GRANT USAGE ON SCHEMA public TO <user>;	Allows to see and select from tables in public
GRANT CREATE ON SCHEMA public TO <user>;	Allows to create in public schema; however, they must also have usage permission

This task can be tedious and although it may not save you any text, it might easier to have a way to quickly restrict everyone from public at some point in the future -- let's say there's a security breach or heavy misuse or a big mistake and you need time to address. For that, you create a new role.

Roles

Roles can be instrumental in granting access and permissions. Since permissions are additive, it's good to think about your role strategy. One potential solution is to create a general role (or a public role that you can control).

CREATE ROLE rw_public;
GRANT ALL ON SCHEMA public TO rw_public;

For your new users you can usually grant them access through your automation script. Manually the process would look like:

-- grant permission
GRANT rw_public TO <user>;

-- remove permission
REVOKE rw_public FROM user;


-- NOTE: users need to reconnect for the roles to go into effect

So, if something happens permissions can be quickly revoked from the role and the database can be restarted with minimum downtime. Of course, there are other ways to kick users out without shutting down the server and you can target accounts that inherit from that role.