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.

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