Re: Restricting user to see schema structure - Mailing list pgsql-general
| From | Bryn Llewellyn |
|---|---|
| Subject | Re: Restricting user to see schema structure |
| Date | |
| Msg-id | A0348203-9026-46A0-B53E-3720EA0401F6@yugabyte.com Whole thread Raw |
| In response to | Re: Restricting user to see schema structure ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Responses |
Re: Restricting user to see schema structure
Re: Restricting user to see schema structure |
| List | pgsql-general |
david.g.johnston@gmail.com wrote:adrian.klaver@aklaver.com wrote:bryn@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
-------+-------------+-------------+-------------
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'
));
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$ +
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…
pgsql-general by date: