It would be a nice security feature to allow `postgres-language-server check to be able to optionally detect any non schema/table qualified references. Any reference that is not schema qualified will go to the search path to look up a given object. This is the source of a number of security vulnerabilities in postgres SQL code. Full compliance with all of these checks may be too verbose for some purposes but in highly secure environments it is a nice defense in depth measure when combined with explicitly setting search path, preferring secrurity invoker functions over security definer functions, etc. The checks that I am thinking about are listed below.
Let me know what you think.
table schema qualified
-- Good, schema qualified table name
SELECT *
FROM pg_catalog.pg_statio_all_tables;
-- Bad, pg_statio_all_tables will be looked up in search path
SELECT *
FROM pg_statio_all_tables;
function schema qualified
-- Good, function schema qualified
SELECT pg_catalog.abs(1);
-- Bad, function not schema qualified
SELECT abs(1);
column table qualified
-- Good, column name is table qualified
SELECT at.relid
FROM pg_catalog.pg_statio_all_tables as at;
-- Bad, column name is not table qualified
SELECT relid
FROM pg_catalog.pg_statio_all_tables;
type schema qualified
-- Good, type schema qualified
SELECT 'hello'::pg_catalog.text;
-- Bad, type looked up in search path
SELECT 'hello'::text;
operator schema qualified
-- Good, operator '=' fully qualified
SELECT *
FROM pg_catalog.pg_stat_io
WHERE backend_type OPERATOR(pg_catalog.=) 'client backend';
-- Bad, operator '=' will be looked up in search path
SELECT *
FROM pg_catalog.pg_stat_io
WHERE backend_type = 'client backend';
Andrew Jackson suggests a security feature for the postgres-language-server check command to detect non schema/table qualified references. This feature aims to prevent security vulnerabilities in PostgreSQL by enforcing schema qualification. He provides examples of good and bad practices for schema qualification in SQL code.