Re: revoked permissions on table still allows users to see table's structure

Bob Lunney <bob_lunney@yahoo.com> wrote:

> That is what schemas, permissions and search paths are for.

I don't think those do as much as you're giving them credit for:

test=> set session authorization dee_ny;
SET
test=> \d
        List of relations
 Schema  | Name | Type  |  Owner
---------+------+-------+---------
 public  | a    | table | kgrittn
 public  | b    | table | kgrittn
 seethat | open | table | kgrittn
(3 rows)

test=> \dt seewhat.*
         List of relations
 Schema  |  Name  | Type  |  Owner
---------+--------+-------+---------
 seewhat | closed | table | kgrittn
(1 row)

test=> \d seewhat.closed
     Table "seewhat.closed"
  Column   |  Type   | Modifiers
-----------+---------+-----------
 closed_id | integer |

I don't think I've used any database where the *structure* of
database objects was hidden from someone connected to the database.
There are typically system tables of some sort to which all
authorized users in the database have read-only access.  The
parallel I would draw in the "real world" is that the format of the
forms which are required for adoption in the Wisconsin court system
are a matter of public record -- anybody can see the blank forms.
Getting a look at data which has been entered onto such forms is a
very different matter.

If you want to hide the structure of the tables from a person, you
need to deny that person authority to connect to the database.  You
can always allow such a person to connect to an application which
you are running in a trusted environment.

-Kevin