From: | venkatesh R <venkatesh(dot)ramanujam007(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | PG Role : With Crud Operations without Drop DB user |
Date: | 2024-02-28 03:30:07 |
Message-ID: | CAH_zdiJdBF1oy=Rz4XgXQSmk5kP3v0jvWEpZEzAnc2vA+4K3mQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi All,
We have a use case where we need to create a user with a role to perform
the crud operations, but it should not drop the databases with that user.
We have tried the below command and tried other permutations and
combinations as well.
-- Replace placeholders with your valuesCREATE USER developer WITH
PASSWORD 'your_password';
CREATE ROLE developer_role;
GRANT USAGE ON SCHEMA public TO developer;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO
developer_role;
GRANT developer_role TO developer;
REVOKE CREATE, DROP ON ALL TABLES IN SCHEMA public FROM
developer;REVOKE CREATE, DROP ON ALL SCHEMAS IN DATABASE
your_database_name FROM developer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT,
UPDATE, DELETE ON TABLES TO developer_role;ALTER DEFAULT PRIVILEGES IN
DATABASE your_database_name GRANT USAGE ON SCHEMAS TO developer_role;
Thanks in advance, please share your insights
The commands we used are still able to drop the database. Which it
shouldn't do.
Is this possible in Postgres, we have tried all the commands.
Thanks
Venkat
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2024-02-28 03:45:39 | Re: PG Role : With Crud Operations without Drop DB user |
Previous Message | vignesh kumar | 2024-02-27 19:50:41 | Re: explosive WAL growth |