Postgres: making a schema restricted/unchangable?

Question

We like our production environment with a restricted/unchangable schema -- the development side can be owned by the developers and changed as they like -- and we like to vet changes as they are promoted.

I'm wondering if this may be a solution to making that happen:

postgres% create proddb with owner=postgres;

unixside% pg_restore --dbname=devdb [--schema-only] --no-owner proddb
/* grants to users on schema objects appear to remain intact */

/* here's the magic, I hope... */
postgres% revoke create on schema public from public;
postgres% grant usage on schema public to produser(s);

Some testing seems to show that a user in this new proddb can interact with tables normally (with appropriate grants) and cannot alter the schema (alter table, create table, drop table, etc). But I'm paranoid and very new to Postgres, so...

Q: Is this correct?

Q: Am I missing anything?

Thanks muchly.

Answer 1

Yes, that is correct. The only addition is that the owner of a table can always delete or modify it. So it may not work if you have existing tables in the schema.

Answer 2

Discovered a missing element: sequences.

The user was finding errors in his scripts; similar errors appeared in the logs:

ERROR:  permission denied for sequence <sequence>

The production schema showed that although sequences were created, they were owned by postgres and no explicit grants were given to the users. As per the GRANT documentation:

Granting permission on a table does not automatically extend permissions to any sequences used by the table, including sequences tied to SERIAL columns. Permissions on sequence must be set separately.

Our fix (verbose for this demonstration) was to find all sequences:

unixside% pg_dump --schema-only proddb > proddb.schema
unixside% grep -i 'create sequence' proddb.schema

...and apply appropriate grants (select to prevent table scans, update to prevent the above errors):

postgres% grant select,update on <sequence> to produser(s);

So far, the user says it's working and errors to the log have stopped...