DEV Community

Cover image for Creating a user with limited privileges in Postgres
Bemn
Bemn

Posted on

Creating a user with limited privileges in Postgres

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';
Enter fullscreen mode Exit fullscreen mode

Grant the right to all public tables:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO webapp_user;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Reference:

Top comments (0)