How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

I want to migrate a fairly simple, internal, database driven application from SQLite3 to PostgreSQL 9.3 and tighten the permissions in the DB as I go.

The application currently consists of a command to update the data; and one to query it. Naturally, I'll also need to maintain the database in other ways (create new tables, views, triggers, etc).

While this application will be the only one hosted on the server at first, I'd prefer to bake in the assumption that it might be hosted on a server with other databases in the future, rather than having to scramble later if that becomes necessary in the future.

I would think that these would be a fairly common set of requirements, but I'm having trouble finding a simple tutorial explaining how to set up a new database in PostgreSQL, with this sort of user/privilege separation. The references go on at length about groups, users, roles, databases,
schemas and domain; but I find them confusing.

Here's what I've tried so far (from within psql as 'postgres'):

CREATE DATABASE hostdb;
REVOKE ALL ON DATABASE hostdb FROM public;
\connect hostdb
CREATE SCHEMA hostdb;
CREATE USER hostdb_admin WITH PASSWORD 'youwish';
CREATE USER hostdb_mgr   WITH PASSWORD 'youwish2';
CREATE USER hostdb_usr WITH PASSWORD 'youwish3';

GRANT ALL PRIVILEGES ON DATABASE hostdb TO hostdb_admin;
GRANT CONNECT ON DATABASE hostdb TO hostdb_mgr, hostdb_usr;
ALTER DEFAULT PRIVILEGES IN SCHEMA hostdb GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO hostdb_mgr;
ALTER DEFAULT PRIVILEGES IN SCHEMA hostdb GRANT SELECT ON TABLES TO hostdb_usr;

But I'm not getting the intended semantics. I want to have it configured so only the hostdb_admin can create (and drop and alter) tables; the hostdb_mgr can read, insert, update and delete on all tables by default; and the hostdb_usr can only read all tables (and views).

When I tried this I found that I was able to create tables in hostdb as any of these users; but, for each user, I could only read or mo

Postgres 14 adds the predefined roles pg_read_all_data and pg_write_all_data, which allow shortcuts for the purposes implied by the names.

Where can I find a decent guide, tutorial or video series on this?

You'll find everything in the manual. Links below.

Granted, the matter is not trivial and sometimes confusing. Here is a recipe for the use case:
Recipe

I want to have it configured so only the hostdb_admin can create
(and drop and alter) tables;

the hostdb_mgr can read, insert, update and delete on all tables by default;

and the hostdb_usr can only read all tables (and views).

As superuser postgres:

CREATE USER schma_admin WITH PASSWORD 'youwish';
-- CREATE USER schma_admin WITH PASSWORD 'youwish' CREATEDB CREATEROLE; -- see below
CREATE USER schma_mgr   WITH PASSWORD 'youwish2';
CREATE USER schma_usr   WITH PASSWORD 'youwish3';

If you want a more powerful admin that can also manage databases and roles, add the role attributes CREATEDB and CREATEROLE above.

Grant each role to the next higher level, so all levels "inherit" at least the set of privileges from the next lower level (cascading):

GRANT schma_usr TO schma_mgr;
GRANT schma_mgr TO schma_admin;

CREATE DATABASE hostdb;
REVOKE ALL ON DATABASE hostdb FROM public;  -- see notes below!

GRANT CONNECT ON DATABASE hostdb TO schma_usr;  -- others inherit

\connect hostdb  -- psql syntax

I am naming the schema schma (not hostdb which would be confusing). Pick any name. Optionally make schma_admin the owner of the schema:

CREATE SCHEMA schma AUTHORIZATION schma_admin;

SET search_path = schma;  -- see notes

ALTER ROLE schma_admin IN DATABASE hostdb SET search_path = schma; -- not inherited
ALTER ROLE schma_mgr   IN DATABASE hostdb SET search_path = schma;
ALTER ROLE schma_usr   IN DATABASE hostdb SET search_path = schma;

GRANT USAGE  ON SCHEMA schma TO schma_usr;
GRANT CREATE ON SCHEMA schma TO schma_admin;

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT SELECT                           ON TABLES TO schma_usr;  -- only read

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO schma_mgr;  -- + write, TRUNCATE optional

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO schma_mgr;  -- SELECT, UPDATE are optional

For and drop and alter see notes below.

Postgres 14 adds the predefined, non-login roles pg_read_all_data and pg_write_all_data to give read-only / write-only access to all objects in all schemas. That goes beyond what's being asked here, but may be useful. See:

• PostgreSQL: Give all permissions to a user on a PostgreSQL database

As things get more advanced I'll also have questions to apply similar
restrictions on TRIGGERS, stored procedures, VIEWS and perhaps other objects.

Views are special.

The manual:

... (but note that ALL TABLES is considered to include views and foreign tables).

And for Updatable Views:

Note that the user performing the insert, update or delete on the view
must have the corresponding insert, update or delete privilege on the
view. In addition the view's owner must have the relevant privileges
on the underlying base relations, but the user performing the update
does not need any permissions on the underlying base relations (see
Section 38.5).

Triggers are special, too. You need the TRIGGER privilege on the table, and:

• What are the privileges required to execute a trigger function in PostgreSQL 8.4?

Important Notes
Ownership

If you want to allow schma_admin (alone) to drop and alter tables, make the role own all objects. The documentation:

The right to drop an object, or to alter its definition in any way, is not treated as a grantable privilege; it is inherent in the owner, and cannot be granted or revoked.
(However, a similar effect can be obtained by granting or revoking membership in the role that owns the object; see below.)
The owner implicitly has all grant options for the object, too.

ALTER TABLE some_tbl OWNER TO schma_admin;

Or create all objects with the role schma_admin to begin with, then you need not set the owner explicitly. It also simplifies default privileges, which you then only have to set for the one role:
Pre-existing objects

Default privileges only apply for newly created objects and only for the particular role they are created with. You'll want to adapt permissions for existing objects, too:

• Permission denied for relation

The same applies if you create objects with a role that does not have DEFAULT PRIVILEGES set, like the superuser postgres. Reassign ownership to schma_admin and set privileges manually - or set DEFAULT PRIVILEGES for postgres as well (while connected to the right DB!):

ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ...  -- etc.

Default privileges

You were missing an important aspect of the ALTER DEFAULT PRIVILEGES command. It applies to the current role unless sp

StackExchange Database Administrators Q#117109, answer score: 130