Create an admin 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_admin_role NOINHERIT;
GRANT CONNECT ON DATABASE exampledb TO exampledb_admin_role;
GRANT USAGE, CREATE ON SCHEMA public TO exampledb_admin_role;  -- CREATE: allows the creation of schema objects like tables, views, indexes, functions, etc.
ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO exampledb_admin_role;
ALTER DEFAULT PRIVILEGES GRANT ALL ON SEQUENCES TO exampledb_admin_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 ALL ON ALL TABLES IN SCHEMA public TO exampledb_admin_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO exampledb_admin_role;
-- Adjust if you need to grant privileges on other existing objects, e.g. on functions, views, data types, indexes

Create the admin user

-- user
CREATE USER exampledb_admin_user INHERIT IN ROLE exampledb_admin_role;