CODEWITHSUNDEEP
postgresqldatabase-administration
leonz
leonz

Reputation: 1127

How to allow certain groups only to create and edit but not delete (drop) tables in PostgreSQL?

I'm building an app using AWS RDS PostgreSQL.

I need to allow one group to use CREATE, INSERT INTO and UPDATE statements, but at the same time not allow them to use DROP TABLE or DROP DATABASE.

Is there a way to do this on the database layer, as I would prefer not to do it on server-side layer? Thank you.

Upvotes: 0

Views: 44

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

You can create one privileged user that can do all these things – let's call it creator – and a normal user luser that is used to log into the database.

creator then defines PL/pgSQL functions with SECURITY DEFINER that perform the required CREATE statements (probably using dynamic SQL with an EXECUTE statement).

Then creator runs

REVOKE EXECUTE ON FUNCTION ... FROM PUBLIC;
GRANT EXECUTE ON FUNCTION ... TO luser;

so that luser can execute the functions.

INSERT and UPDATE privileges are granted to luser inside the functions.

Documentation links:

Upvotes: 1

Related Questions