How to completely restrict a postgres role to a single schema

Question

I'd like to create a readonly role for a third party to access a handful of tables from the public schema without being able to view the rest of the tables in public.

My initial idea was to create a new schema 'readonly' and then create views in that schema:

create view readonly.table1 as select * from public.table1

Then alter the search path for the readonly user to limit it only to the 'readonly' schema. However, it looks like I can still view the public schema as that role (though I can't select anything from any tables in schema public).

Is there a way to remove all visibility into the public schema from a role? Unfortunately, moving everything to a different schema is not possible.

Answer 1

You have to revoke the users rights to the schema

Its kinda hard to revoke public schema as its default is well public which is granted automatically to any user granted access to database and all users are added to the public role.

Commands like this have to be run

REVOKE ALL PRIVILEGES ON public FROM public;

There are lots of gotchas with this
here are a couple of articles going over how to do this READ them CAREFULLY and TEST before doing it on a production machine

https://wiki.postgresql.org/images/d/d1/Managing_rights_in_postgresql.pdf

https://blog.dbi-services.com/avoiding-access-to-the-public-schema-in-postgresql/

EDIT FORGET TO STATE

I have never tried to grant access from one schema that reads a table from another schema that user does not have access to.. This may not work as security should be passed up the chain blocking the read. I DO NOT KNOW

Answer 2

This actually seemed to work fairly well:

REVOKE ALL ON SCHEMA public FROM readonlyuser

But hey, time will tell :)