I'm initializing a Postgres DB and would like to know what is the best way to do it.
Objectives:
- We would like to use default database (
postgres
) unless there's a strong reason not to do. - We would like to use default schema (
public
) unless there's a strong reason not to do. - We would like to apply the Principle of Least Privilege.
- 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;
- Is it enough? Did I miss anything? Do I need to revoke anything else?
- I read somewhere, creating some object automatically grants some privilege to
PUBLIC
(sorry for too manysome
s, 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;
-- Must run it as db_admin
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;
- Any comment on the above?
- I couldn't make it working with
db_admins
role anddb_admin
user. Everytimedb_admin
creates some object, I have to change the object owner todb_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.)