Re: system catalog permissions

[Melvin Davidson]

On Mon, Feb 26, 2018 at 7:50 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Feb 26, 2018 at 4:55 PM, Paul Jungwirth <
> p...@illuminatedcomputing.com> wrote:
>
>> On 02/26/2018 03:47 PM, Tom Lane wrote:
>>
>>> PropAAS DBA  writes:
>>>
 We have a client which is segmenting their multi-tenant cluster
 (PostgreSQL 9.6) by schema, however if one of their clients connects via
 pgadmin they see ALL schemas, even the ones they don't have access to
 read.

>>> PG generally doesn't assume that anything in the system catalogs is
>>> sensitive.  If you don't want user A looking at user B's catalog
>>> entries, give them separate databases, not just separate schemas.
>>>
>>
>> I'm sure this is what you meant, but you need to give them separate
>> *clusters*, right? Even with separate databases you can still get a list of
>> the other databases and other roles in the cluster. I would actually love
>> to be mistaken but when I looked at it a year or two ago I couldn't find a
>> way to lock that down (without breaking a lot of tools anyway).
>>
>
> ​Yes, both the database and role namespace is global to an individual
> cluster.  Its another level of trade-off; database and role names could
> realistically and easily be done UUID-style so knowing the labels doesn't
> really tell anything except a vague impression of host size.
>
> Assuming clients don't want to see their log files...
>
> David J.
>
>
>... both the database and role namespace is global to an individual
cluster



*Slight correction to that.*





*https://www.postgresql.org/docs/10/static/runtime-config-connection.html
by
defaultdb_user_namespace = off *
*However, if set = on, then " you should create users as username@dbname "
which makes role names specific to each database.*

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: system catalog permissions

[David G. Johnston]

On Mon, Feb 26, 2018 at 4:55 PM, Paul Jungwirth  wrote:

> On 02/26/2018 03:47 PM, Tom Lane wrote:
>
>> PropAAS DBA  writes:
>>
>>> We have a client which is segmenting their multi-tenant cluster
>>> (PostgreSQL 9.6) by schema, however if one of their clients connects via
>>> pgadmin they see ALL schemas, even the ones they don't have access to
>>> read.
>>>
>> PG generally doesn't assume that anything in the system catalogs is
>> sensitive.  If you don't want user A looking at user B's catalog
>> entries, give them separate databases, not just separate schemas.
>>
>
> I'm sure this is what you meant, but you need to give them separate
> *clusters*, right? Even with separate databases you can still get a list of
> the other databases and other roles in the cluster. I would actually love
> to be mistaken but when I looked at it a year or two ago I couldn't find a
> way to lock that down (without breaking a lot of tools anyway).
>

​Yes, both the database and role namespace is global to an individual
cluster.  Its another level of trade-off; database and role names could
realistically and easily be done UUID-style so knowing the labels doesn't
really tell anything except a vague impression of host size.

Assuming clients don't want to see their log files...

David J.

Re: system catalog permissions

[Paul Jungwirth]

On 02/26/2018 03:47 PM, Tom Lane wrote:

PropAAS DBA  writes:

We have a client which is segmenting their multi-tenant cluster
(PostgreSQL 9.6) by schema, however if one of their clients connects via
pgadmin they see ALL schemas, even the ones they don't have access to
read.

PG generally doesn't assume that anything in the system catalogs is
sensitive.  If you don't want user A looking at user B's catalog
entries, give them separate databases, not just separate schemas.


I'm sure this is what you meant, but you need to give them separate 
*clusters*, right? Even with separate databases you can still get a list 
of the other databases and other roles in the cluster. I would actually 
love to be mistaken but when I looked at it a year or two ago I couldn't 
find a way to lock that down (without breaking a lot of tools anyway).


Thanks!

--
Paul  ~{:-)
p...@illuminatedcomputing.com

Re: system catalog permissions

[Tom Lane]

PropAAS DBA <d...@propaas.com> writes:
> We have a client which is segmenting their multi-tenant cluster 
> (PostgreSQL 9.6) by schema, however if one of their clients connects via 
> pgadmin they see ALL schemas, even the ones they don't have access to 
> read. I assume pgadmin is pulling the list from the system catalogs.

> What's the right/best practice approach? revoke all from public on 
> specific system catalog tables? Which tables?

Messing with the system catalog permissions is likely to break stuff
you'd rather not break.

PG generally doesn't assume that anything in the system catalogs is
sensitive.  If you don't want user A looking at user B's catalog
entries, give them separate databases, not just separate schemas.

regards, tom lane

Re: system catalog permissions

[Joshua D. Drake]

On 02/26/2018 03:11 PM, PropAAS DBA wrote:

All;


We have a client which is segmenting their multi-tenant cluster 
(PostgreSQL 9.6) by schema, however if one of their clients connects 
via pgadmin they see ALL schemas, even the ones they don't have access 
to read. I assume pgadmin is pulling the list from the system catalogs.



What's the right/best practice approach? revoke all from public on 
specific system catalog tables? Which tables?


AFAIK, you can not hide the list of schemas but you can prevent people 
from entering them.



JD




Thanks in advance




--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

system catalog permissions

[PropAAS DBA]

All;


We have a client which is segmenting their multi-tenant cluster 
(PostgreSQL 9.6) by schema, however if one of their clients connects via 
pgadmin they see ALL schemas, even the ones they don't have access to 
read. I assume pgadmin is pulling the list from the system catalogs.



What's the right/best practice approach? revoke all from public on 
specific system catalog tables? Which tables?



Thanks in advance