Re: Restricting user to see schema structure

pá 13. 5. 2022 v 5:42 odesílatel Bryn Llewellyn <bryn(at)yugabyte(dot)com> napsal:

> *david(dot)g(dot)johnston(at)gmail(dot)com <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:*
>
> *bryn(at)yugabyte(dot)com <bryn(at)yugabyte(dot)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.
>
>