Обсуждение: 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;     

On Tue, 2004-04-06 at 10:23, Andrew Dunstan wrote:
> 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.

Out of curiosity, why would you deny them access to the information schema?

Rod Taylor wrote:

>On Tue, 2004-04-06 at 10:23, Andrew Dunstan wrote:
>  
>
>>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.
>>    
>>
>
>Out of curiosity, why would you deny them access to the information schema?
>
>  
>

It might be overkill. Basically I was operating on the principle of 
"everything not explicitly allowed is forbidden". I will experiment some 
more.

cheers

andrew

Hello Andrew (from back in 2004),

Have things improved at all regarding Postgres and the configurability of reduced public privileges for databases?

We have a goal to hide unneeded database objects from our database-challenged users - as they might see in a simple
databaseviewer application - by removing their privileges on such objects. 

Regards,

Scott Langley
Systems Analyst/Programmer
Statistical Center for HIV/AIDS Research and Prevention (SCHARP)
Fred Hutchinson Cancer Research Center
Seattle, Washington


From:     Andrew Dunstan <andrew(at)dunslane(dot)net>
To:
Cc:     Postgresql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject:     Re: zero knowledge users
Date:     2004-04-06 15:50:46

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