This post showing how to create a user with limited privileges in PostgreSQL.
I created a web app. It reads data from a PostgreSQL database. I want to create a user with read-only privilege and connect to the database.
Let say the user you want to create is
CREATE USER webapp_user WITH ENCRYPTED PASSWORD 'secure_passw0rd';
Grant the right to all public tables:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO webapp_user;
Connect the database using
webapp_user in the web app.
I want more power…
Now my web app is not read-only anymore. Users can create/delete some entries in the tables too.
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO webapp_user;
Looks simple and cool! But why I got the
permission denied for sequence XXX_id_seq to YYY error while inserting new records to the tables?
If your table has an auto-increment field then you need some extra privileges: the privileges to use the sequence-related functions (e.g.
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public to webapp_user;
Example of an auto-increment field: the id of the table
What if I just want to apply the privilege(s) to a special table?
GRANT SELECT, INSERT, UPDATE, DELETE ON special_table TO webapp_user;
(this blog post also available on dev.to)