Postgres How to securely initialize the database?

Question

I'm initializing a Postgres DB and would like to know what is the best way to do it.
Objectives:
1. We would like to use default database (postgres) unless there's a strong reason not to do.
2. We would like to use default schema (public) unless there's a strong reason not to do.
3. We would like to apply the Principle of Least Privilege.
4. No one (apart from those created below) should be able to anything in the DB.

One thing I know, public is public, fix it:

REVOKE ALL ON DATABASE postgres FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;

1. Is it enough? Did I miss anything? Do I need to revoke anything else?
2. I read somewhere, creating some object automatically grants some privilege to PUBLIC (sorry for too many somes, I hardly remember the exact information). Should I be worried about this?

And then I create some roles and grants appropriate privileges:

-- Creating DB admin user to be used only by Flyway
CREATE USER db_admin WITH PASSWORD passwd CONNECTION LIMIT 5;
GRANT CONNECT ON DATABASE postgres TO db_admin;
GRANT ALL ON SCHEMA public TO db_admin;
GRANT ALL ON ALL TABLES IN SCHEMA public TO db_admin;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO db_admin;

-- Creating DB application users with read/write permissions
CREATE ROLE db_writers;
GRANT CONNECT ON DATABASE postgres TO db_writers;
GRANT USAGE ON SCHEMA public TO db_writers;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO db_writers;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO db_writers;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO db_writers;

ALTER DEFAULT PRIVILEGES FOR ROLE db_admin IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO db_writers;
ALTER DEFAULT PRIVILEGES FOR ROLE db_admin IN SCHEMA public GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO db_writers;

CREATE USER db_writer WITH PASSWORD passwd IN ROLE db_writers;

-- Creating DB application users with read-only permissions
CREATE ROLE db_readers;
GRANT CONNECT ON DATABASE postgres TO db_readers;
GRANT USAGE ON SCHEMA public TO db_readers;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_readers;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO db_readers;

ALTER DEFAULT PRIVILEGES FOR ROLE db_admin IN SCHEMA public GRANT SELECT ON TABLES TO db_readers;
ALTER DEFAULT PRIVILEGES FOR ROLE db_admin IN SCHEMA public GRANT SELECT ON SEQUENCES TO db_readers;

CREATE USER db_reader WITH PASSWORD passwd IN ROLE db_readers;

1. Any comment on the above?
2. I couldn't make it working with db_admins role and db_admin user. Everytime db_admin creates some object, I have to change the object owner to db_admins. I don't like it. (If there's a better way to do it, it'd be awesome. If not, we're good with one user without any role.)

Answer 1

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.