fine grained control of table columns

[iferencik]

Hello folks,

i want to inquire about column based privileges for tables. I will illustrate using and example.

I have a table and would like to expose all all but certain columns

\d zambia.poverty 
                                                Table "zambia.poverty"
         Column          |            Type             | Collation | Nullable |                Default                
-------------------------+-----------------------------+-----------+----------+---------------------------------------
 id                      | integer                     |           | not null | nextval('zambia.zp_id_seq'::regclass)
 country_name            | character varying(6)        |           |          | 
 province                | character varying(254)      |           |          | 
 district                | character varying(254)      |           |          | 
 constituency            | character varying(254)      |           |          | 
 poverty                 | double precision            |           |          | 
 absolute_number_of_poor | double precision            |           |          | 
 geom                    | geometry(MultiPolygon,3857) |           |          | 
Indexes:
    "zp_pkey" PRIMARY KEY, btree (id)
    "zp_geom_geom_idx" gist (geom)

First irevoke the select privilege for tileserver user which is used by the pg_tileserv to ponnect to postgres

REVOKE SELECT ON TABLE zambia.poverty FROM tileserver;

The table is not published

GRANT SELECT( "id", "country_name", "province", "poverty", "absolute_number_of_poor", "geom") ON  zambia.poverty TO tileserver;

The layer is not visible .
After

GRANT SELECT ON TABLE zambia.poverty to tileserver;

The layer is back

This behavior is NOT and issue but as there is no discussion section I decided to post here.

Thanks for doing such a good job with pg_tileserv.

[pramsey]

I think the workaround is to create a view with just the columns you want exposed. I was frankly unaware you could grant select at the column level, so that's a new piece of functionality on the TO DO list. I wonder how to query for the list of columns available in a table given a particular login role.

[iferencik]

hey @pramsey
I use info functions to check if a login has

USAGE on schema

SELECT pg_catalog.has_schema_privilege('{user}', '{schema}', 'USAGE') as "usage";

SELECT on table

SELECT pg_catalog.has_table_privilege('{user}', '{table}', 'SELECT') as "select";

SELECT on each column

SELECT pg_catalog.has_column_privilege('{user}', '{table}', '{column}', 'SELECT') as "select";

or EXEC on a given function

SELECT has_function_privilege('{user}', oid::regproc, 'execute') as execute
FROM pg_proc
WHERE proname = '{function_name}' AND pronamespace::regnamespace::text = '{schema}';