Message-ID: <4E6E24C2.5050901@ringerc.id.au>
Date: Mon, 12 Sep 2011 23:26:58 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:6.0.2) Gecko/20110902 Thunderbird/6.0.2
MIME-Version: 1.0
To: Rainer Leo <leo@workfile.de>
CC: pgsql-admin@postgresql.org
Subject: Re: Best procedure for restricted access
References: <746856965.20110912151609@workfile.de>
In-Reply-To: <746856965.20110912151609@workfile.de>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

On 12/09/2011 9:16 PM, Rainer Leo wrote:
> Hello,
>
> I have been asked to configure a database role
> to be used for ODBC access.
>
> So far I have done this:
>
> CREATE ROLE odbc_user LOGIN
>    ENCRYPTED PASSWORD 'bar'
>    NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
>
> REVOKE ALL ON DATABASE foo FROM odbc_user;
> REVOKE CREATE ON SCHEMA public FROM public;
> GRANT SELECT ON v_sales TO odbc_user;
>
> When I try:
>
> foo->  SELECT * FROM customers;
> access is denied as expected
>
> foo->\d baz
> I see table definitions.

You'd have to mess with permissions on the pg_catalog tables and the 
INFORMATION_SCHEMA views. This may have unexpected side-effects or cause 
some clients that expect to be able to use those schema to get metadata 
to cease functioning correctly.

I don't think denying access to table definitions is part of the 
security model's goals at the moment; it's about limiting access to 
_data_ not DDL or definitions. You'll note that function sources are 
also available via pg_catalog, though it seems to be reasonably safe 
(from what I hear, having not tested it) to change permissions to deny 
access to those.

--
Craig Ringer