Re: zero knowledge users

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 database viewer 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 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