Let's say that I have four tables in my Postgres database, two of which are private and contain highly sensitive information (private1 and private2), and two that contain information I want to allow anyone in the world to be able to query arbitrarily (public1 and public2). I'm aware that this is a very poor design, but bear with me.

I want to set up a user that can solely run SELECTs on the two public tables, but can in no way do anything else even remotely malicious with the other two tables (or the database more generally).

My naive approach would be to do something like set up a new user public_querier, run a REVOKE ALL ON private1, private2, public1, public2 FROM public_querier; and then a GRANT SELECT ON public1, public2 TO public_querier;.

I suspect that this does not fulfill my security desideratum because of some subtleties that I don't have knowledge of, and I'd greatly appreciate (1) hearing if my suspicion is true and (2) any references that would help guide me in the right direction if my suspicion is false.


  • 3
    Did you try it? Did it not work? If not, what was the outcome, and what did you expect instead?
    – mustaccio
    May 20 '21 at 22:03

So you want some users to be able to query just two of your tables but other users to be able to query all four?

A Role Based Permission Model fits that bill perfectly.

create role public_data_role ; 
grant select on public1 to public_data_role ; 
grant select on public2 to public_data_role ; 

create role private_data_role ; 
grant select on private1 to private_data_role ; 
grant select on private2 to private_data_role ; 

grant public_data_role to fred, wilma, barney, betty ; 
grant private_data_role to fred, wilma ; 

Of course, you first have to remove any and all privileges on these from public - that trumps all other granted permissions and is a pain to try and work around. Wipe slate clean and build up increasingly "open" permissions to reach your goal.

I'm aware that this is a very poor design, but bear with me.

I disagree. I see nothing whatever wrong with this.
A Database may contain thousands of tables. Users will be blissfully ignorant of many (or most) of them.


One issue is that the base metadata about the private tables in the pg_catalog schema cannot be hidden. Especially:

  • pg_class (list of tables)
  • pg_attribute (list of columns)

But pg_stats contents are not available for tables without read permission (otherwise some data could be leaked).

Generally speaking, the permission system is designed to achieve what you want, but most DBAs would not feel comfortable with only that layer of isolation between non-trusted readers and sensitive data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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