zero knowledge users

I have been doing some experimentation for a series of articles I am 
writing, and want to create a user with as little privilege as possible 
who can still do the things I explicitly want him/her to be able to do.

In particular, I wanted to be able to deny any useful access to the 
metadata contained in catalogs and the information schema.

It can be argued that this is security by obscurity - and thus a Bad 
Thing (tm) - and if this were the only security measure being considered 
I would agree that it is less than 100% effective. Nevertheless, I think 
it is still a possibly useful measure against some unwanted intruders, 
for example, even if ways around it can be discovered by some. In any 
case, the policy question is not the purpose of my writing here ;-)

The problem I encountered in implementing this is that many of the 
catalogs have public access, and it seems impossible to designate a 
level of access lower than those for public. Also, to my surprise, 
removing public usage on the pg_catalog scheme didn't stop access to its 
contents.  In my testing, I have 3 users - accountsdba, apiowner and 
webuser, and want to make webuser a zero knowledge user. The approach I 
took was to create a new group called pspublic, containing the first 2 
users but not webuser, and then to replace all the public privileges on 
critical objects with equivalent privileges for pspublic. The commands I 
gave to do this are shown below - the tables and views chosen were those 
with "name" type objects, on the assumption that if the user can't see 
any names anything else should be fairly useless. I might well have 
included some I shouldn't have, though, or missed some I should have 
included.

So far the results have been reasonably promising - i.e. all users can 
do what I want/expect them to be able to do, and I have not discovered a 
way for the zero knowledge user to gain knowledge I want to deny access to.

The downsides to this approach are 1) it won't survive across a 
dump/reload, and 2) it will break on catalog structure changes. Some 
steps could be taken to alleviate these disadvantages, but it is would 
still be somewhat fragile. This set me thinking that maybe it would be 
worthwhile to provide a flag on create database which significantly 
reduces public privileges, so one would not have to go through such 
contortions.

Comments welcome

cheers

andrew






create group pspublic with user accountsdba, apiowner;
revoke all on schema pg_catalog, public, information_schema from public;
grant  usage on schema pg_catalog,information_schema to group pspublic;
grant all on schema public to group pspublic;
revoke select on tablepg_am, pg_attribute, pg_class, pg_constraint, pg_conversion, pg_database,pg_group, pg_indexes,
pg_language,pg_listener, pg_namespace, pg_opclass,pg_operator, pg_proc, pg_rewrite, pg_rules,
pg_stat_activity,pg_stat_all_indexes,pg_stat_all_tables, pg_stat_database,pg_stat_sys_indexes, pg_stat_sys_tables,
pg_stat_user_indexes,pg_stat_user_tables,pg_statio_all_indexes, pg_statio_all_sequences,pg_statio_all_tables,
pg_statio_sys_indexes,pg_statio_sys_sequences,pg_statio_sys_tables, pg_statio_user_indexes,
pg_statio_user_sequences,pg_statio_user_tables,pg_stats, pg_tables, pg_trigger, pg_type, pg_user,pg_views
 
from public;
grant select on tablepg_am, pg_attribute, pg_class, pg_constraint, pg_conversion, pg_database,pg_group, pg_indexes,
pg_language,pg_listener, pg_namespace, pg_opclass,pg_operator, pg_proc, pg_rewrite, pg_rules,
pg_stat_activity,pg_stat_all_indexes,pg_stat_all_tables, pg_stat_database,pg_stat_sys_indexes, pg_stat_sys_tables,
pg_stat_user_indexes,pg_stat_user_tables,pg_statio_all_indexes, pg_statio_all_sequences,pg_statio_all_tables,
pg_statio_sys_indexes,pg_statio_sys_sequences,pg_statio_sys_tables, pg_statio_user_indexes,
pg_statio_user_sequences,pg_statio_user_tables,pg_stats, pg_tables, pg_trigger, pg_type, pg_user,pg_views
 
to group pspublic;
revoke select, update on table pg_settings from public;
grant select,update on table pg_settings to group pspublic;