So you've finished modeling your entities and for whatever reason you've decided to enforce domain boundaries or just group similar stuff together. You'll probably end up with a DDL like this:
CREATE SCHEMA platform
CREATE TABLE IF NOT EXISTS platform.services (...);
CREATE TABLE IF NOT EXISTS platform.agents (...);But here's the catch: roles that are not the current user role executing those commands won't be able to read or modify those tables. So if you've got separate user roles it's better you don't forget to add the following snippets in your SQL script:
-- Grants privileges on current tables
GRANT ALL ON ALL tables IN SCHEMA platform TO John, Bob;
GRANT ALL ON ALL sequences IN SCHEMA platform TO John, Bob;
-- Grants privileges on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA platform GRANT ALL ON tables TO John, Bob;
ALTER DEFAULT PRIVILEGES IN SCHEMA platform GRANT ALL ON sequences TO John, Bob;If you don't know whether there are other user roles, you can leverage PostgreSQL' pg_roles View to know them:
SELECT rolname FROM pg_roles;Last but not least. PostgreSQL offers the PUBLIC keyword that effectively makes it easier to grant privileges to all roles instead of listing them one-by-one in the SQL commands. But doing that violates the Principle of least privilege, so make sure its appropriate before throwing that in your scripts.