Situation
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.
Code
Let say the user you want to create is webapp_user
:
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.
Code
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. currval
and nextval
)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public to webapp_user;
Example of an auto-increment field: the id of the table
Extra
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;
Reference:
Top comments (0)