0

I want to allow a users assigned to a role to delete their own content in postgres but not content made by admins. I am new to database management and thought that the below would work but doesn't.

GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO analyst;

GRANT ALL ON ALL TABLES IN SCHEMA public TO analyst;
mapping dom
  • 1,737
  • 4
  • 27
  • 50
  • Please define how "their own content" can be recognized. If it is by some column values, row level security may be the answer. – Laurenz Albe Jan 07 '19 at 16:17
  • Thanks, if users creates table user can delete table otherwise they may not. – mapping dom Jan 07 '19 at 16:42
  • If I understand you right, that's how it works by default. Only a superuser and the table owner can run `DROP TABLE`. – Laurenz Albe Jan 07 '19 at 20:44
  • Thanks, that how I thought it should work. But it isn't, I was testing as a role with login rather than a user assigned to role – mapping dom Jan 08 '19 at 15:15
  • I don't understand your latest comment. Maybe you should add some SQL code to your question that demonstrates the problem. As it is, the question is unclear. – Laurenz Albe Jan 08 '19 at 16:25

1 Answers1

0

You should use row level security. Docs : https://www.postgresql.org/docs/10/ddl-rowsecurity.html

Ex : 

CREATE POLICY analyst_policy
ON public.mytable
USING (user = CURRENT_USER);
Rémi Desgrange
  • 868
  • 1
  • 6
  • 20