1

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.

2 Answers 2

-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

3
  • Thanks, I have some reading to do :) I guess I'm not looking for an aggressively-secure option - just something that hides general visibility (for example, so that if the readonly role runs \du, they only see the views created in the readonly schema). It'd also be nice to prevent the role from running CREATE/ALTER queries. Jan 23, 2020 at 0:56
  • @guinanseyebrows I made an edit forgot to state something
    – zsheep
    Jan 23, 2020 at 0:59
  • Theoretically this should work since the view is generated by the owner (so as long as I create the view as a superuser/anyone else with select access to that schema/table), running SELECT on the view as the readonly user works, while directly running SELECT on the target table fails. Jan 23, 2020 at 1:02
-1

This actually seemed to work fairly well:

REVOKE ALL ON SCHEMA public FROM readonlyuser

But hey, time will tell :)

1
  • 1
    That will not work at all as long as the privilege is granted to PUBLIC. Revoking a privilege that was never granted is a no-operatoin in SQL. Jan 23, 2020 at 7:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.