Postgres How to securely initialize the database?
Your SQL script looks good, except that you shouldn't use

CREATE USER ... PASSWORD '...';


to give a user a password. The problem is that the password goes across the line in clear text and might end up in the server log.

Either hash the password on the client side and send it that way, or use psql's \password command to change the password interactively.

You should not use the postgres database for user data. It is intended for administrative purposes only (a place to connect if you want to run CREATE DATABASE or DROP DATABASE).

You can use the public schema; the only problem I see is that if you create an extension, the extension's objects end up in the same schema as the application objects, which I believe is not nice. I prefer to create a schema for the application and leave public for extension owned objects.

I am not sure what you mean when you say that you cannot get it to work with db_admins. I assume that you want to have a group of administrators so that each of them can ALTER and DROP the objects the others created.

That is not possible, because only the object owner (and a superuser) can do that. If you want several administrative users, you could proceed as follows:


Only db_admins (a NOLOGIN role) can create objects in the schema.
The administrative users are created with NOINHERIT and belong to db_admins.
Whenever an administrative user wants to create or modify an object, he or she has to first run:

SET ROLE db_admins;



This is a bit cumbersome, but a solution similar to not allowing remote logins as root on UNIX and using su to become root instead.