PostgreSQL – Grant Permission on Database

After a long time, when I tried to create a user and grant him all permissions on a certain database using-

GRANT ALL PRIVILEGES ON DATABASE myDB TO newUser;
the command succeeded but the user received the permission refused error.

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schemaName TO newUser;
is another operation that only requires the schema name and not the database name.

Went through the document, and found the following information.

GRANT ALL DATABASE Access grants a role the CREATE, CONNECT, and TEMPORARY privileges on a database to users which are properly referred to as roles. None of those privileges allows a role to read data from a table; that requires the table’s SELECT privilege.

So, finally, my solution is-
GRANT ALL PRIVILEGES ON DATABASE myDB TO newUser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO newUser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO newUser;

Hope you find this information helpful.

Please subscribe for more interesting updates.