We have a postgres database on Aptible that has a database endpoint for read-only access. Is there a way to configure the database so that only our read-only database users can be used over the endpoint but not others like the aptible superuser?
In our self-hosted setups, we normally use pg_hba.conf to do this but we don’t have access to this file on Aptible.
Aptible Databases cannot currently be configured to restrict access for certain users from specific IP addresses. Restricting access in this way is a Database-specific feature and not all Database types support it. Instead, all Database types are configured to where, if the Database can be connected to, any user can log in. As such, the Databases rely on the configuration of the Aptible infrastructure to restrict access, i.e. they can only be accessed from within the Stack by default but can be exposed externally via Network Integrations or Database Endpoints.
The decision was made to not allow configuration like this e.g. via PostgreSQL’s pg_hba.conf file likely because it often leads to confusion and users can easily lock themselves out of the Database.
Rather than expose the primary database, you can restrict access by creating a replica and using it instead. PostgreSQL replicas are read-only so even if the aptible superuser’s password is obtained, it isn’t possible to write to the replica. The password will be the same as the primary Database’s aptible user but, assuming it isn’t exposed, they won’t be able to connect to it. This is how we recommend providing read-only access.