2

I wanted a work-around for the PostgreSQL limitation (Hide unrelated schema/tables structure for a user).

I decided to go with the Row-Level-Security(RLS). The RLS seems working as expected for the user tables but did work for the internal tables such as pg_catalog.pg_namespace.

pg_namespace table data before RLS.

The screenshot shows the original data in the pg_catalog.pg_namespace table without RLS enbled.

--logged-in as a SUPERUSER(postgres)

SET allow_system_table_mods=on;
ALTER TABLE pg_catalog.pgnamespace ENABLE ROW LEVEL SECURITY;
ALTER TABLE pg_catalog.pg_namespace FORCE ROW LEVEL SECURITY;

REVOKE ALL ON TABLE pg_catalog.pg_namespace FROM public, utkarsh;
GRANT ALL ON TABLE pg_catalog.pg_namespace TO utkarsh;

CREATE POLICY ensure_right_table ON pg_catalog.pg_namespace 
FOR ALL
USING ((pg_catalog.pg_namespace.nspacl::text = '{postgres=UC/postgres,utkarsh=UC/postgres}'));
--nspacl is not an unique column.

SET ROLE utkarsh;

SELECT * FROM pg_catalog.pg_namespace;
--I can still see all the rows

RLS enabled pg_namespace data.

3
  • Try to connect as utkarsh instead of using SET ROLE Commented Dec 13, 2022 at 9:14
  • No, this didn't work. Commented Dec 13, 2022 at 9:24
  • I doubt you can mess with the system tables like that. Commented Jan 16, 2023 at 14:06

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.