Limiting the operations that client-side code can perform upon its database backend's artifacts

> On 14-Sep-2022, tgl(at)sss(dot)pgh(dot)pa(dot)us wrote:
>
> …. Therefore, if you don't trust another session that is running as your userID, you have already lost. That session can drop your tables, or corrupt the data in those tables to an arbitrary extent, and the SQL permissions system will not squawk even feebly… So if you're not happy with this hazard, you should not be accepting the idea that actors you don't trust are allowed to submit queries under the same userID as you. And if you're using a client-side software stack that forces that situation on you, it's time to look for another one.
>
> Or in other words, I flatly reject the claim that this:
>
>> bryn(at)yugabyte(dot)com wrote:
>>
>> It's common to design a three tier app so that the middle tier always authorizes as just a single role—say, "client"—and where the operations that "client" can perform are limited as the overall design specifies.
>
> is in any way sane or secure. There is not very much that the database server can do to clean up after insecure client-side stacks.

*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:

www.postgresql.org/message-id/10F360BB-3149-45E6-BFFE-10B9AE31F1A6@yugabyte.com

<aside>That thread is "case closed" now. (My question arose from my basic misunderstanding of what's hard-wired and what is simply a default privilege regime that can be changed. And then I compounded my embarrassment by revoking "execute from public" on a "pg_catalog" function when "current_database()" had one value—and then not seeing the effect of this when "current_database()" had a different value.)

I wandered off topic with a claim about three tier app design. And that prompted Tom's response here:

https://www.postgresql.org/message-id/3100447.1663213208%40sss.pgh.pa.us

</aside>

*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:

« The main point of a database is to store your data, to keep it in compliance with all the specified data rules, and to allow authorized client-side code to modify the data by using only a set of specified business functions. »

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:

do $body$
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*

\c - u1
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?

\c - client
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 »?

do $body$
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$;