Avoid managing privileges per table in PostgreSQL

When creating a database some prefer to separate the administrative role from the day to day service role that utilizes the database. This means that the service role has no permissions by default. To avoid managing privileges for the service role on a table by table basis, we set the privileges at the schema level. Here is an example of how to implement this.

CREATE ROLE admin_role;
CREATE ROLE service_role;

CREATE DATABASE blah OWNER admin_role;

\connect blah

GRANT USAGE, SELECT, UPDATE, INSERT ON ALL TABLES in SCHEMA public TO service_role;
GRANT SELECT, USAGE ON ALL SEQUENCES in SCHEMA public TO service_role;

CREATE ROLE admin_user LOGIN CONNECTION LIMIT 50 PASSWORD 'xx' IN ROLE admin_role;
CREATE ROLE service_user LOGIN CONNECTION LIMIT 50 PASSWORD 'xx' IN ROLE service_role;

The public schema is specific to the specific database, so these grants would not affect other databases on the server cluster. You will notice that all ownership and privileges are granted to the roles. The users are then assigned to roles and granted login access.