Question 113862 in PostgreSQL Question posted in
The official documentation can be found here.

How to read / list security labels on columns in postgreSQL

I’ve set up PostgreSQL Anonymizer on my database with security labels and everything works fine.

I’m trying to regularly ceck if there is missing security labels on the columns of my database to telle the developers to add them in the next release but I can’t fin a way to read the security labels.

Can anyone know how to do this ?

EDIT on 10/11/2022

Thanks to @Shiva, I’ve end up doing this query :

select cl."oid", col.ordinal_position, col.table_schema, col.table_name, col.column_name
FROM information_schema.columns col
join pg_catalog.pg_class cl on cl.relname = col.table_name
WHERE col.table_schema = 'XXXX'
and not exists (select objoid FROM pg_seclabel where provider = 'anon' and objsubid = col.ordinal_position and objoid = cl."oid");
Tags:

2

Answers


  1. 0

    You have to query pg_seclabel catalog to get list of security labels.

    SELECT objsubid, provider, label FROM pg_seclabel WHERE objoid::regclass = 'mytable'::regclass

    objsubid is the column number whose corresponding column name can be found by querying information_schema.columns catalog.

    SELECT column_name FROM information_schema.columns WHERE table_name = 'mytable' AND ordinal_position = <column_number>

    You can combine the above two queries to find columns that do not have the required security labels.

    Login or Signup to reply.
  2. 0

    I have nothing to add to the answers above to the specific question posted…but for anyone else coming here and looking only at the subject line

    How to read / list security labels on columns in postgreSQL

    a more convenient, possibly workable solution may be in looking only at pg_seclabel, without the join, e.g.:

    select objtype,objname,label from pg_seclabels 
 where objtype in ('table','column') 
   and objname LIKE '%mytablename%';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search