Evaluate alternatives for table lookups

[mmd-osm]

Evaluate if PostgreSQL CREATE FUNCTION could be used for table lookups.

https://www.postgresql.org/docs/current/sql-createfunction.html

(...)

CREATE OR REPLACE FUNCTION lookup(t character varying)
  RETURNS SETOF mytable AS 
  ' SELECT * FROM public.mytable WHERE mytable.t = t; '
LANGUAGE SQL
IMMUTABLE
SECURITY DEFINER
SET search_path = public, pg_temp;

select * from lookup('...');

[tomhughes]

What on earth would be the point of that? Encapsulating database access in functions is full on enterprise nonsense...

[mmd-osm]

I want to revoke SELECT permissions for the cgimap user on one particular table. With the database function in place, you need to provide the correct lookup value. Otherwise, you have no access to any of the table contents.
Maybe there are other ways to accomplish the same.

[tomhughes]

Surely if you revoke select on the table (and why would you want to do that?) then the function will be no more able to read it than a direct select would. Plus you will need us to create functions on the master database for you!

[tomhughes]

Ah I see that's the point of SECURITY DEFINER to elevate permissions but that then means the function needs to be defined as a different user.

[mmd-osm]

Yes, exactly, that's the point... the db function needs to be created outside of cgimap by another user, and could then be consumed by cgimap instead of directly reading from the table.

[tomhughes]

What table exactly is it that you don't want to be able to read, except that you do because you're going to create a function to read it? Is the goal to limit what columns you can read or something?

[mmd-osm]

The idea was to limit access to oauth_access_tokens. This may seem a bit far fetched at first. However, since tokens are stored in a particular way, we should probably avoid uncontrolled read access that could happen due some programming mistake, or something similar.