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??

Cheers,


Steve.

Tue, 06 Jul 2004 10:27:06 +1200
Steve Holdoway <steve@treshna.com>
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.





Mon, 05 Jul 2004 19:04:12 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: Security...
On Mon, 2004-07-05 at 23:27, 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??
> 

I think there was some discussion on this on the ODBC list.

Teradata and Oracle use views that have a subselect in them that only
displays objects that a user has at least one privilege on/over.

In Oracle, they're called ALL and USER views, so there are multiple
versions of the schema depending upon your (security) needs. Teradata
gives you the option at system init time.

Currently, psql issues complex SQL directly against the catalog, though
I did once have plans to rework that so the same commands would be
available from any interface.

Best regards, Simon Riggs


Tue, 06 Jul 2004 00:30:22 +0100
Simon Riggs <simon@2ndquadrant.com>
Re: Security...