Postgres Grant SELECT & UPDATE privilege ONLY to a user

Question

Can anyone kindly tell me how I can grant a user (username: restricted_user) with only SELECT AND UPDATE privilege on postgresql? I'm running centos.

At the moment the user cannot select anything. I've logged in to phpPgMyAdmin and i can only seem to grant the SELECT and UPDATE privilege per table for the user which works but I would like to apply this rule for all tables across all databases.

thanks

Answer 1

You can grant to all tables in a schema at once:

GRANT SELECT, UPDATE ON ALL TABLES IN SCHEMA public TO restricted_user;

If there are serial columns or other SEQUENCES in use, you need also:

GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO restricted_user;

Run for every relevant schema. More in the fine manual for GRANT. You also need is the USAGE privilege on each schema:

GRANT USAGE ON SCHEMA public TO restricted_user;

If you have another user creating more objects, you probably want to grant the same privileges for future objects by default, too. That's where DEFAULT PRIVILEGES come in:

ALTER DEFAULT PRIVILEGES FOR creating_user IN SCHEMA public
GRANT SELECT, UPDATE ON TABLES TO restricted_user;

ALTER DEFAULT PRIVILEGES FOR creating_user IN SCHEMA public
GRANT USAGE ON SEQUENCES TO restricted_user;