Re: Security...

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 table
pg_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 table
pg_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.