Achieving best practice schema and permissions management on PostgreSQL

Question

I have a PostgreSQL database (12, moving to 13 imminently) with a single (Rails) application running on it, and no other users or systems connecting.

The application uses the postgres user to connect to the database for all purposes - regular querying, creating and altering tables/index etc. - and the application's objects are all in the PUBLIC schema.

I want to allow (human) users to connect to this database to run data analysis queries against the application data, and for those users to be read-only on some parts of the application schema, and have no other privileges on the instance (other than to connect of course).

I think there are some "sub-optimal" practices already in place here, which might be common to inexperienced PostgreSQL users, and this question addresses what they are and how they should best be fixed. This seems like it might be a situation with many out-of-the-box Rails (perhaps others) apps.

I think that these are the suboptimal practices.

1. I don't think the postgres user is appropriate for an application connection, as it is over-privileged for that. A dedicated application user would be better.
2. If I want to create read-only users with access to only some daatabase tables and columns, having the application in the PUBLIC schema is tricky. At the least, privileges need to be revoked, and then it is not really PUBLIC. That seems confusing, so a dedicated application schema would be better

The plan I've come up with is to have:

• a dedicated user for the application.
• a single schema dedicated to the application.
• no public schema - I don't see a need for it, and it not being actually public sounds confusing.
• A read-only role that can be assigned to individual analyst users.

Steps to migrate to it:

1. Rename the current PUBLIC schema to APP_SCHEMA (for example).
2. Create a new user APP_USER (for example).
3. Grant all on APP_SCHEMA to APP_USER.
4. Revoke any privileges that public has on APP_SCHEMA.
5. Change the search path to be APP_SCHEMA.
6. Create APP_READONLY_ROLE.
7. Grant select on required APP_SCHEMA tables/columns to APP_READONLY_ROLE
8. Create analyst users, assigning APP_READONLY_ROLE to them. (This is RDS, so another possibility is to add a APP_READONLY_USER and grant privileges to use it through IAM).

So two questions I guess:

Does that plan seem sensible? Do those migration steps get there?

Answer 1

Renaming the public schema proved to be technically possible, but raised a lot of problems due to coding of "public" into some function code.

Instead I created a new schema, migrated tables, views, and materialised views to it, and that seems to work well.