3

Because of a security concern, I need to hide the information about a few IDs from some users in my PostgreSQL database (9.5). The info are distributed in a number of tables, and can be in new tables in future. Can I create a database wide security policy to do that? I checked the row level security command, e.g., CREATE POLICY name ON table_name TO role_name USING id not in (). But seems that only applies to specific tables. Any suggestion?

Luke
  • 720
  • 1
  • 9
  • 22
  • Why do you have the same data in multiple tables? If it is not the same data, it will need specific policies. – Bergi Apr 03 '20 at 18:19
  • Hi Bergi, it's not same data in multiple tables. Say, one table is about the age of the IDs, the other is about the address of the IDs. I want to hide all info about a small list of IDs here. I went through my post again, don't find how to rephrase to improve it. Do you have any suggestions to get rid of the confusion? Thanks again. – Luke Apr 03 '20 at 18:45
  • Ok, but surely there are other tables that don't have information about ids at all? And the age table and the address table have different columns, different layout, different conditions. If you really have globally unique ids, with one `id` column for them in every single of your tables, then you will also have to add a policy to every single of your tables. – Bergi Apr 03 '20 at 19:07
  • Hi Bergi, yes I have a globally unique attribute to use for this purpose, and yes not all tables have this column. So my question is can I apply a global policy like: if this table has column ID then ID can't be in this list, Or I need to alter table one by one, and for each future new tables? – Luke Apr 03 '20 at 19:13
  • There is no such global policy, no. (And notice nothing stops someone from creating a table with an `id` column that has a different purpose). You need to add them one by one. If your database is too large to do this by hand, you could however query the information schema to find the tables that look like they need the policy. – Bergi Apr 03 '20 at 19:16
  • OK, thanks. I actually has read-only users in mind when ask this question, because the write privilege is controlled by me. But anyways, thanks for the help. – Luke Apr 03 '20 at 19:21

2 Answers2

2

With a loop, it is possible to enable RLS for multiple tables at once.

A policy has to be applicable to all those tables, if you want to define it for multiple ones. If you use a custom constraint behind 'public' you can filter the tables to which your policy applies.

DO
$$
DECLARE
    row record;
BEGIN
    FOR row IN SELECT tablename FROM pg_tables AS t
        WHERE t.schemaname = 'public' -- Add custom filter here, if desired.
    LOOP
        EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY;', row.tablename); -- Enable RLS for tables
        EXECUTE format('CREATE POLICY some_policy ON %I TO some_user
                            USING (some_attribute = some_value;', row.tablename); -- Only works if all tables share "some_attribute"
    END LOOP;
END;
$$;

Please note:

  • I was inspired by this answer: How to change schema of multiple PostgreSQL tables in one operation?
  • The combination of loop with RLS may be an insecure practice. I do not think so, but I do not know for sure, because I did not work with loops before.
  • The policy you define has to be able to work on all tables. If it applies to most tables, not all: You may exclude the non-working ones before. Sometimes, there is e.g. a userId which is shared among most tables.
  • In the specific question some_attribute = ... would be similar to the example inside offical docs, where they check for user_name = current_user.
  • To find an example where this answer was useful, see this question of mine.
Paul Smith
  • 299
  • 2
  • 13
0

Yes, row level security has to be enabled per table, and policies have to be defined for each table. There is no way around that.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263