How to GRANT SELECT PRIVILEGES on table with wild card with psql postgres

user_10297364 0
Viewed 1144

I have tables' names that start with underscore '_XXXXXXX' among other tables. I need to create a user that can only do a query on these '_XXXX" tables (nothing else) without the possibility of viewing/finding the other tables (not starting with '_XXXXX').

How can i do that in postgres psql?:

I tried 

  GRANT SELECT ON TABLE "_*" TO username;

i get the following:

ERROR: relation "_*" does not exist

Any help is appreciated.

Thank you

when I execute this code in PgAdmin4 query editor:

DO
$$
DECLARE
  r record;
BEGIN
  FOR r IN SELECT c.relname,
                  n.nspname
                  FROM pg_class c
                       INNER JOIN pg_namespace n
                                  ON n.oid = c.relnamespace
                  WHERE n.nspname = 'Schemas'
                        AND c.relkind = 'r'
                        AND c.relname LIKE '$_%'
                                           ESCAPE '$' LOOP
    EXECUTE 'GRANT SELECT ON "' || r.nspname || '"."' || r.relname || '" TO kpidata;';
  END LOOP;
END;
$$
LANGUAGE plpgsql;

i get the following response and nothing changes (still have the same access rights to all for the created user 'kpidata'). I am sure it is me who is not understanding how things work

enter image description here

my db structure is the following:

enter image description here

psql postgresql
1 Answers

You could use a DO block looping over all table with names beginning with underscore, build the statement for it an execute the statement.

DO
$$
DECLARE
  r record;
BEGIN
  FOR r IN SELECT c.relname,
                  n.nspname
                  FROM pg_class c
                       INNER JOIN pg_namespace n
                                  ON n.oid = c.relnamespace
                  WHERE n.nspname = 'public'
                        AND c.relkind = 'r'
                        AND c.relname LIKE '$_%'
                                           ESCAPE '$' LOOP
    EXECUTE 'GRANT SELECT ON "' || r.nspname || '"."' || r.relname || '" TO username;';
  END LOOP;
END;
$$
LANGUAGE plpgsql;
user_96614240
Related