Re: Restricting user to see schema structure

Oops. I made the unforgivable mistake of saying something without first having run a script to demonstrate what I'd planned to say. I'm embarrassed (again). I confused my memory of the proof-of-concept demo that I'd coded in PG with what, back in the day, I'd coded in Oracle Database. (The visibility notions in ORCL are very much more granular than in PG.)

I re-coded and re-ran my PG proof-of-concept demo. It creates a dedicated database "app" and dedicated users "data", "code", and "api" to own the application objects, each in a schema with the same name as the owning user. These have the purposes that their names suggest. As it progresses, it creates the table "data.t", the function "code.f", and the function "api.f" (as a minimal jacket to invoke "code.f"). Finally, it creates the user "client" with no schema but with "usage" on the schema "api" and "execute" on (in general) each of its functions. The idea is that "client" has been explicitly given only the privileges that are necessary to expose the functionality that has been designed for use by connecting client sessions.

When the setup is done, and when connected as "client". it runs a UNION query using "pg_class", "pg_proc", and "pg_namespace". I restricted it to exclude all the owned by the installation (in my case, an MacOS, "Bllewell").

As you'd all expect, this is the result:

Without the restriction, and again as you'd all expect, the query shows every single schema object in the entire database. Other queries show all the users in the cluster. Queries like the ones I used here allow "\d", "\df", and the like to show lots of the facts about each kind of object in the entire database. And, yes, I did know this.

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).

Here's what the exercise taught me: When connected in psql as "client", and with "\set VERBOSITY verbose", this:

causes this expected error:

But this:

causes this unexpectedly spelled error (with no error code):

Nevertheless, this:

sidesteps the check that "\sf" uses, runs without error and produces this result:

So it seems that the implementation of "\sf" adds its own ad hoc privilege checks and, when needed, outputs an error message that its own code generates. Strange.

I see now that my quest to handle, and sanitize, unexpected errors in PL/pgSQL exception sections has only rather limited value. It can aid usability, for example by changing "unique_violation" (with all sorts of stuff about line numbers and the like) to "This nickname is taken". However, in the case of errors like this:

while again the sanitized "Nickname must be no more than eight characters" is nice, it doesn't prevent the patient hacker who connects as "client" from studying all the application's code, looking at all the table definitions, and working out the scenarios that would lead to this raw error if it weren't prevented from leaking to the client program.

Maybe this entire discussion is moot when hackers can read the C code of PG's implementation…