0

I have inherited a PostgreSQL (13.x) legacy DB I am working with. Original creation was with single admin role w/ownership and all rights that services connected to. We are looking to make this less permissive. I'd just like to confirm that my initial logic is correct.

We want this user (newacct_rw) to be able to read, and insert new rows but not update certain columns. Create new role;

  CREATE ROLE newacct_rw WITH
  NOLOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  NOBYPASSRLS;

Create defaults for new role;

ALTER DEFAULT PRIVILEGES FOR ROLE oldowner
GRANT REFERENCES, INSERT, SELECT ON TABLES TO newacct_rw;

ALTER DEFAULT PRIVILEGES FOR ROLE oldowner
GRANT ALL ON SEQUENCES TO newacct_rw;

ALTER DEFAULT PRIVILEGES FOR ROLE oldowner
GRANT EXECUTE ON FUNCTIONS TO newacct_rw; 

And here is where it gets sticky. We have a column we want to protect. So on existing tables we give them update only to the columns we want them to be able to update;

REVOKE ALL ON TABLE public.oaddress FROM newacct_rw;
GRANT INSERT, REFERENCES, SELECT ON TABLE public.oaddress TO newacct_rw;
GRANT UPDATE(order_id) ON public.oaddress TO newacct_rw;
GRANT UPDATE(address_type) ON public.oaddress TO newacct_rw;

Do you see anything I might be missing on this?

0

Your Answer

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

Browse other questions tagged or ask your own question.