Create a writer role and user in PostgreSQL

This article is a follow-up on PostgreSQL user creation with restricted privileges.

Create a new database

First, login with the system user (usually postgres), to the postgres database.

psql -p <port> -U <user> <database>

Which gives, if the instance is listening on the default post (5432):

psql -p 5432 -U postgres postgres

Create the database exampledb:

CREATE DATABASE exampledb;

Now, quit the psql console, by typing \q, quit or exit. The last two commands work since PostgreSQL 11.

Connect to the newly created database

From this point onwards, the commands will be executed against the exampledb database. We connect to it with the system user:

psql -p 5432 -U postgres exampledb

Revoke default privileges

We need to revoke the existing privileges on the newly created database and on the public schema:

REVOKE ALL ON DATABASE exampledb FROM public; -- this will revoke default database privileges (CREATE, CONNECT ...) from roles in 'PUBLIC' (all roles). 
REVOKE ALL ON SCHEMA public FROM public; -- this will revoke default schemas privileges from roles in 'PUBLIC' (all roles).

Create the writer role

-- reader role
CREATE ROLE exampledb_write_role NOINHERIT;
GRANT CONNECT ON DATABASE exampledb TO exampledb_write_role;
GRANT USAGE ON SCHEMA public TO exampledb_write_role;
ALTER DEFAULT PRIVILEGES GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO exampledb_write_role;
ALTER DEFAULT PRIVILEGES GRANT USAGE ON SEQUENCES TO exampledb_write_role;
-- Adjust if you need to alter default privileges on other objects, e.g. on functions, views, data types, indexes

Note that in the above, we are granting privileges on tables of exampledb in an ALTER DEFAULT PRIVILEGES statement. Altering the default privileges is important, since, at this point, the newly created database does not have any table. This will make sure that the privileges are present for tables created in the future, in that database. If you are applying the above recipe to a database with already existing tables, you need, in addition to the above, the equivalent GRANT statements without ALTER DEFAULT, to be applied "ON ALL" existing tables or sequences in the public schema:

GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public TO exampledb_write_role;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO exampledb_write_role;
-- Adjust if you need to grant privileges on other existing objects, e.g. on functions, views, data types, indexes

Create a write user

-- user
CREATE USER exampledb_write_user INHERIT IN ROLE exampledb_write_role;

That's it. Now, creating additional write users is as simple as creating a user inheriting the role exampledb_write_role.