Re: Restricting user to see schema structure - Mailing list pgsql-general

From Pavel Stehule
Subject Re: Restricting user to see schema structure
Date
Msg-id CAFj8pRCnXoEt8vDLemD6bXbZUiuje13_hm5EFa5YoFi7KHsoNg@mail.gmail.com
Whole thread Raw
In response to Re: Restricting user to see schema structure  (Bryn Llewellyn <bryn@yugabyte.com>)
List pgsql-general


pá 13. 5. 2022 v 5:42 odesílatel Bryn Llewellyn <bryn@yugabyte.com> napsal:
david.g.johnston@gmail.com wrote:

bryn@yugabyte.com wrote:

However, the design decision that, way back when, leads to this outcome does surprise me. The principle of least privilege insists that (in the database regime) you can create users that can do exactly and only what they need to do. This implies that my "client" should not be able to list all the objects in the database (and all the users in the cluster).

If there was any motivation to improve PostgreSQL on this front I'd like them to start with "routine bodies" being hidden away from inspection. I'm much less concerned about pg_class or even knowing the names of things.

This has been discussed a number of times, probably every few years or so. My quick search failed to find any relevant links/threads in the archives, though I didn't try that hard.

Thanks (again) David. Yes, there is an argument that when app developers know that hackers can read every minute detail of their implementation (but, with a sound user/schema/privileges discipline cannot change any of this), it cautions them to be extra scrupulous. SQL injection is maybe a good example. It's probably easier and quicker to scan PL/pgSQL source code looking for obvious patterns (like "any use of dynamic SQL?", "If yes, any concatenation of literals into the to-be-executed statement?", and so on) than it is to send robotically generated values via browser-UI screens in the hope of provoking tell-tale errors.

any developer can run this check before an attacker.

plpgsql_check  https://github.com/okbob/plpgsql_check does this check.

Regards

Pavel


It certainly helps to know that nothing in how PG works in the space that's relevant here is going to change in my lifetime.

pgsql-general by date:

Previous
From: Bryn Llewellyn
Date:
Subject: Re: Restricting user to see schema structure
Next
From: Bryn Llewellyn
Date:
Subject: Re: Restricting user to see schema structure