Re: Security... - Mailing list pgsql-hackers

Steve Holdoway wrote:

> Hi folks,
>
> I'm trying to seriously restrict what a particular user can see within 
> a database. Using various schemas, rules, groups and grants, I've 
> managed to restrict them to only being able to _do_ what I want them 
> to, but they can still _see_ the rest of the schemas, system tables, 
> etc. I've tried revoking everything on public, pg_catalogs, etc, but 
> you can still describe tables.
>
> Anyone know how to stop this, or if it's even possible??


Here's an extract from a half-written article on this subject. No 
guarantees - use at your own risk! Enjoy.

cheers

andrew

We start off by defining a user group called pspublic, which will 
contain the DBA and APIowner users.

We'll revoke any privileges on the key schemas and certain critical 
system catalog tables from public (which means any user) and then grant 
them back only to the pspublic group.

This is security by obscurity to some extent. Security professionals 
tend to sneer at such measures. If we were relying on this as the main 
means of ensuring our security it would indeed be a poor set of 
measures. However, it is not our main security defense, and it does 
provide some protection against intruders who are unable to obtain the 
information by other means (e.g. by bribing a former member of your 
development staff). The relations I have chosen to hide are those 
catalog tables and views that contain the names of database objects.

Still running as the superuser, connect to the new database and run 
these commands:

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;           

Now webuser can see nothing of any value, unless we grant explicit 
access, as you should be able to verify.