> david.g.johns...@gmail.com wrote:
>
>> adrian.kla...@aklaver.com wrote:
>>
>>> b...@yugabyte.com wrote:
>>>
>>> I've prototyped this scheme. It seems to work as designed. A client that
>>> connects with psql (or any other tool) can list the API functions and
>>> whatever \df and \sf show. (notice that \d will reveal nothing.)But doing
>>> this reveals only the names of the functions that are called (which will be
>>> identical to the jacket names—so no risk here) and the name(s) of the
>>> schema(s) where they live (so a minor theoretical risk here).
>>>
>>> Full disclosure: I've never done this in anger.
>>
>> Try select * from pg_class or select * from pg_attribute or any of the other
>> system catalogs.
>
> Which is exactly what most GUI applications that provide object browsing and
> viewing are going to use.
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:
owner | schema_name | object_kind | object_name
-------+-------------+-------------+-------------
api | api | function | f
code | code | function | f
data | data | index | t_pkey
data | data | sequence | t_k_seq
data | data | table | t
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:
select * from data.t;
causes this expected error:
ERROR: 42501: permission denied for schema data
But this:
sf code.f
causes this unexpectedly spelled error (with no error code):
ERROR: permission denied for schema code
Nevertheless, this:
select pg_catalog.pg_get_functiondef((
select p.oid
from pg_catalog.pg_proc p
join pg_catalog.pg_namespace n
on p.pronamespace = n.oid
where
p.proowner::regrole::text = 'code' and
n.nspname::text = 'code' and
p.prokind = 'f' and
p.proname::text = 'f'
));
sidesteps the check that "\sf" uses, runs without error and produces this
result:
CREATE OR REPLACE FUNCTION code.f() +
RETURNS integer +
LANGUAGE plpgsql +
SECURITY DEFINER +
AS $function$ +
begin +
return (select count(*) from data.t);+
end; +
$function$ +
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:
22001: value too long for type character varying(8)
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…
