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

Bob Lunney <bob_lunney(at)yahoo(dot)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