How can I grant all privileges on a database in PostgreSQL

Question

We have a template database with schemas, tables, sequences etc. We create a new database from it and provide ownership of the new database to a user (a new user created as part of this workflow). The new user still lacks the privileges to select data from existing tables. Which is the best way to handle this? PG version is 16.3. The new user need not get any privilege on any database other than the new one created, but should have the privileges to do pretty much anything on the new database.

Answer 1

1. Create the new database from the template and specify the new user as owner:

   CREATE DATABASE newdb WITH OWNER newuser TEMPLATE templatedb;

2. Connect to the new db as superuser

3. Run:

   REASSIGN OWNED BY old_owner TO newuser;