On 14-Sep-2022, tgl@xxxxxxxxxxxxx wrote: *BACKGROUND* I'm starting a new thread here. What I wrote, and Tom's response, are taken from a longish thread that I started with the subject "Is it possible to stop sessions killing each other when they all authorize as the same role?", here:
*ANYWAY...* Tom's "I flatly reject" has been troubling me for the past couple of weeks. I wonder if what I wrote was unclear. I'll try a different way. First, w.r.t. Tom's the main point of a database is to store your data I think that more needs to be said, thus:
This implies a carefully designed within-database regime that takes advantage of established notions: for encapsulating the implementation of business functions; and for access control. This, in turn, implies a minimum of two distinct roles: one to own the entire implementation. And another to allow exactly and only the specified business functions to be performed by client-side code. In a real use case, user-defined functions or procedures define the business function API. And there'd be typically several roles that share the implementation and that take advantage of access control notions among themselves. My code example, below, reduces this paradigm as far as I could manage to allow a convincing demo of the principles. It relies on this: — People who implement client-side code to access the database are given *only* the credentials to connect as one particular user, "client", that exposes the business function API. — All other connect credentials, including but not at all limited to superuser credentials, are kept secret within a manageably small community of server-side engineers. — Self-evidently, this relies on carefully designed and strictly implemented human practices. But so, too, does any human endeavor where security matters. In our domain, this implies that the overall design has a carefully written prose specification and that the development shop delivers a set of install scripts. Then a trusted person whose job is to administer the deployed app scrutinizes the scripts and runs them. In the limit, just a single person knows the deployment site passwords and can set "rolcanlogin" to "false" for every role that owns the implementation artifacts once the installation is done. My demo seems to show that when a program connects as "client", it can perform exactly and only the database operations that the database design specified. Am I missing something? In other words, can anybody show me a vulnerability? *THE DEMO* The code example models the simplest form of "hard shell encapsulation" that I could manage. (I now realize that, with some very manageable effort, I can revoke all privileges on every object in the "pg_catalog" schema from public and then re-grant as needed to whatever roles need them—following the famous principle of least privilege. So none would be granted to "client" with the result that it can't see metadata about anything. A prose document would suffice for communicating what client-side engineers need to know.) The idea is that "client" should see an "insert and select, only" view and be unable to do any DDLs. This relies on the fact that a view is non-negotiably "security definer". There are just two "vanilla" roles, "client" and "u1", thus: declare expected_roles constant name[] := array['client', 'u1']; roles constant name[] := ( select array_agg(rolname order by rolname) from pg_roles where rolname in ('u1', 'client') and rolcanlogin and not rolsuper and not rolinherit and not rolcreaterole and not rolcreatedb and not rolreplication and not rolbypassrls and has_database_privilege(rolname, current_database(), 'connect') ); begin assert roles = expected_roles, 'Unexpected'; end; $body$; I'm leaving out of what I show here the code that creates "client" and "u1" and that allows them to connect to (and in the case of "u1" only, create and change objects) in a suitable purpose-created database. The database starts off empty with no schemas 'cos "public" has been dropped. This bootstrap can easily be done by a superuser. With a bit more effort, it can be done by a non-superuser with "createrole" and some privileges (with "grant option") on the database. *SETUP* create schema s authorization u1; revoke all on schema s from public; create table s.t( k bigint generated always as identity primary key, c1 text not null constraint t_chk check(c1 = lower(c1))); revoke all on table s.t from public; create view s.v as select k, c1 from s.t; revoke all on table s.v from public; grant usage on schema s to client; grant insert, select on table s.v to client; *POSITIVE TESTS* (these succeed) Can a session authorized as "client" do everything that's intended? insert into s.v(c1) values ('dog'), ('cat'), ('frog'); select k, c1 from s.v order by k; *NEGATIVE TESTS* (the block finishes silently without error) Can a session authorized as "client" « drop your tables, or corrupt the data in those tables to an arbitrary extent »? declare n bigint; begin -- Try to do any operation on "s.t". begin insert into s.t(c1) values ('mouse'); assert false, 'Unexpected'; exception when insufficient_privilege then null; end; begin update s.t set c1 = 'bird' where c1 = 'frog'; assert false, 'Unexpected'; exception when insufficient_privilege then null; end; begin delete from s.t; assert false, 'Unexpected'; exception when insufficient_privilege then null; end; begin drop table s.t; assert false, 'Unexpected'; exception when insufficient_privilege then null; end; begin select nextval('s.t_k_seq') into n; assert false, 'Unexpected'; exception when insufficient_privilege then null; end; -- Try to do outlawed operations on "s.v". begin update s.v set c1 = 'bird' where c1 = 'frog'; assert false, 'Unexpected'; exception when insufficient_privilege then null; end; begin delete from s.v; assert false, 'Unexpected'; exception when insufficient_privilege then null; end; begin drop view s.v; assert false, 'Unexpected'; exception when insufficient_privilege then null; end; begin create table s.x(n int); assert false, 'Unexpected'; exception when insufficient_privilege then null; end; -- Try to "corrupt" the data -- i.e. to do DMLs that -- would break the rules. begin insert into s.v(c1) values('Rat'); assert false, 'Unexpected'; exception when check_violation then null; end; begin insert into s.v(k, c1) values(42, 'Rat'); assert false, 'Unexpected'; exception when generated_always then null; end; end; $body$; |
