How to grant permissions on all tables to a user in PostgreSQL

In this article, we will see how to grant permissions on all tables to a user in PostgreSQL.

Syntax:

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public 
TO user_name; 

Examples:

1. First grant CONNECT to database using below syntax.

GRANT CONNECT
ON DATABASE database_name 
TO user_name;

2. Grant all DML permissions to single user in PostgreSQL database ‘r2schools’;

\c r2schools

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public 
TO mike;

3. Grant all DML permissions to multiple users in PostgreSQL database ‘r2schools’;

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public 
TO bob,karunakar;