Before moving on to real-world, practical examples, let's briefly focus on the various layers of security that we will be dealing with. When dealing with security, it makes sense to keep these levels in mind in order to approach security-related issues in an organized way.
Here is my mental model:
- Bind addresses: listen_addresses in the postgresql.conf file
- Host-based access control: The pg_hba.conf file
- Instance-level permissions: Users, roles, database creation, login, and replication
- Database-level permissions: Connecting, creating schemas, and more
- Schema-level permissions: Using schema and creating objects inside a schema
- Table-level permissions: Selecting, inserting, updating, and more
- Column-level permissions: Allowing or restricting access to columns
- RLS: Restricting access to rows
In order to read a value, PostgreSQL has to ensure that we have sufficient permissions on every level. The entire chain of permissions has to be correct. My little model...