Enforce conditions on queries

Question

Currently I have a simple schema, a sites table and an items table.

Sites own many items, items has a siteId column to store the reference of the site which owns it.

I'd like to be able to enforce a condition when querying items, meaning returning an error if it is not queried with a siteId - in practice:

• select * from items would fail
• select * from items where siteId = 1 would succeed, albeit a site record with id 1 exists

This can easily be achieved on an application level.

I recently started investigating further into row level policies, roles, functions, etc… which I found very interesting as they enable lower level setups. That is, I'm still new about it and lack understanding/knowledge. I was wondering if the use case described above could simply be solved within SQL (via a combination of policies, functions, etc… or any other Postgres builtin tools)?

Answer 1

One possibility (one that you feel intuitively, apparently) is to centralize your query into a function and disable querying otherwise.

Let's say the tables are owned by a role called owner, and there is a reader to run SELECT queries against the tables. Then there is an application role which can connect to the DB and is able to call functions. All this can be achieved by:

-- log in as a superuser, for example
CREATE ROLE owner;
CREATE ROLE reader;
CREATE USER application WITH PASSWORD 'a';

SET ROLE TO owner;

CREATE SCHEMA data;

CREATE TABLE data.important (i_id serial PRIMARY KEY);  -- will be owned by owner

GRANT SELECT ON data.important TO reader;

At this point, whoever can assume the reader role (by being a member and/or using SET ROLE) will be able to run any SELECT against the table. Note that application doesn't have any rights here.

Then create another schema for your functions:

CREATE SCHEMA api; -- well, you can call it something else, too

RESET ROLE; -- to switch back to your superuser self

CREATE FUNCTION api.select_important(id integer) 
RETURNS data.important
LANGUAGE sql
SECURITY DEFINER -- the important bit
AS $$
SELECT * FROM data.important WHERE i_id = id;
$$;

GRANT USAGE ON SCHEMA api TO application;
GRANT EXECUTE ON FUNCTION api.select_important(integer) TO application;

The SECURITY DEFINER clause will make the function inherit the rights of the superuser to read from the table (there is no need to grant it). Now if you log in as application, you can call the function:

\c - application 
Password for user application: 

SELECT * FROM api.select_important(1);
 i_id 
──────

(1 row)

However, you cannot read from the table directly:

my=> SELECT * FROM data.important WHERE i_id = 1;
ERROR:  permission denied for schema data
LINE 1: SELECT * FROM data.important WHERE i_id = 1;

Note: to spare GRANTs all the time you create a table or function, you can set default privileges. See the bottom part of an earlier answer of mine about how to do that.