Only Allow Login to Read-Replicas and Standbys
When you're working with large databases in production, it is incredibly common to use read-replicas to improve performance. These read-replicas are a copy of your primary (main) database and let your applications offload read-heavy queries, which in-turn reduces strain on your primary database, effectively making the application faster and snappier.
Sometimes, you may want to restrict specific database users so they can connect ONLY to these read-replicas, and not to the primary database server. This can be tricky to implement, since any permissions configured for this use-case, whether on the user-level, the database level, the schema-level or even the table level would be quickly replicated to the read-replicas and thus would not work as expected.
This guide will show how to configure a database user to only login successfully on a read-replica. The only requirement is to enable the pg_tle extension [3] on your PostgreSQL database. This is simple to do on your Ubuntu based Laptop (see how to do that here [2]) or virtual-machines offered by your favourite cloud-provider. Furthermore, you could apply your login rules using Pl/PgSQL, PL/v8 or even PL/Rust - See here[1].
Why Restrict Access?
There are many good reasons for restricting users to read-replicas:
Performance: You can dedicate your primary database server to handling write operations (like updating data), ensuring those operations happen as fast as possible.
Reporting / Analytics: Production environments often have dedicated users for ancillary tasks, such as monitoring, reporting dashboards, read-only tenants etc. Restricting these database users to read-replica helpsreducing extra load on the primary database.
Security: In some cases, granting direct access to the primary database might be considered a security risk. Further, you may not be able to force login hygeine for all your database users, and then having a lockdown system to reject those database users to login to primary is crucial for application rollout.
Prerequisites
- An existing PostgreSQL database instance with at least one read-replica.
- You could also try this on your own Postgres database with
pg_tleextension. Read here [2] for more on how to install pg_tle on your Ubuntu system.
- You could also try this on your own Postgres database with
- Basic understanding of users and permissions within a database.
Steps
- Identify Target Database and Users: First we need to define how to implement the restriction. i.e. Which users (and database) are to be restricted to login only to read-replica. In the example below, we would restrict the user
standby_only_userto only be able to login to Standbys / Read-Replicas on databaseprod_db.
psql <<SQL
\c prod_db
CREATE EXTENSION pg_tle;
SQL - Ensure that
shared_preload_librariesis properly set to allowpg_tle. Also make sure that thepgtle.clientauth_db_nameis appropriately set to the desired database (hereprod_db):
cat <<EOL >> data/postgresql.conf
shared_preload_libraries='pg_tle'
pgtle.enable_clientauth=require
pgtle.clientauth_db_name=prod_db
pgtle.clientauth_users_to_skip=robins
pgtle.clientauth_databases_to_skip=''
EOL
- Secret Sauce:
Next we create the key pg_tle function that restricts the user standby_only_user to login successfully only if this is a standby / read-replica:
SELECT pgtle.install_extension (
'standbyusercheck',
'1.0',
'Allow some users to login only to standby / read-replicas',
$_pgtle_$
CREATE SCHEMA standbycheck_schema;
REVOKE ALL ON SCHEMA standbycheck_schema FROM PUBLIC;
GRANT USAGE ON SCHEMA standbycheck_schema TO PUBLIC;
CREATE OR REPLACE FUNCTION standbycheck_schema.standbycheck_hook(port pgtle.clientauth_port_subset, status integer)
RETURNS void AS $$
DECLARE
is_standby bool := TRUE;
BEGIN
IF port.user_name = 'standby_only_user' THEN
SELECT pg_is_in_recovery()
INTO is_standby;
IF is_standby THEN
RAISE NOTICE 'User allowed to login';
ELSE
RAISE EXCEPTION 'User can only login to Standby / Read-Replicas';
END IF;
END IF;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION standbycheck_schema.standbycheck_hook TO PUBLIC;
SELECT pgtle.register_feature('standbycheck_schema.standbycheck_hook', 'clientauth');
REVOKE ALL ON SCHEMA standbycheck_schema FROM PUBLIC;
$_pgtle_$
);
And now that the function is defined,CREATE EXTENSION would install the function and bind it to future login attempts.
CREATE EXTENSION standbyusercheck;
SHOW pgtle.clientauth_db_name;
- Test Connection:
- Attempting to connect as a privileged user (here
robins) to either of primary or read-replica should succeed.
Logging into Replica as robins
login | current_database | pg_is_in_recovery
--------+------------------+-------------------
robins | prod_db | t
(1 row)
Logging into Primary as robins
login | current_database | pg_is_in_recovery
--------+------------------+-------------------
robins | prod_db | f
(1 row)
- However, the user
standby_only_usershould NOT be able to login to the primary.
Logging into Primary as standby_only_user
psql: error: connection to server at "localhost" (127.0.0.1), port 6432 failed: FATAL: User can only login to Standby / Read-Replicas
- While the user (
standby_only_user)should only be able to login to any read-replica.
Logging into Replica as standby_only_user
login | current_database | pg_is_in_recovery
-------------------+------------------+-------------------
standby_only_user | prod_db | t
(1 row)
Other important aspects of this feature
You could force
clientauthfor all logins by setting the parameterpgtle.enable_clientauth = requireYou could configure some users to always be allowed to login to either of Primary / Read-replica in cases of emergency, by adding that user to the
pgtle.clientauth_users_to_skip. Ideally you would want your admin database roles to this list.Orthogonally, you could configure some databases to always allow users to skip
clientauthby setting thepgtle.clientauth_databases_to_skipfeature.Note, that both
clientauth_databases_to_skipandclientauth_databases_to_skipcan be utilised together. This is a good way to ensure that some set of database users (and some databases) are exempt from such a login restriction.If
pgtle.enable_clientauthis set toonorrequireand if the database mentioned inpgtle.clientauth_db_nameis not configured correctly, postgres would complain with the messsageFATAL: pgtle.enable_clientauth is set to require, but pg_tle is not installed or there are no functions registered with the clientauth feature. This is a good engine check, helping us avoid basic misconfigurations.If you're anticipating connection storms, you can also increase the workers (that would help enforce the login restriction) by setting the
pgtle.clientauth_num_parallel_workersparameter to greater than 1.
Conclusion
By following the above steps, you've now successfully configured your PostgreSQL environment to restrict certain users to only login to the read-replicas. This helps not just optimize your database performance, but also bolster security.
Let me know if you'd like to explore more advanced scenarios or discuss IAM integration for fine-grained access control!
Reference
- Clientauth Hook Documentation - https://github.com/aws/pg_tle/blob/main/docs/04_hooks.md'
- Install
pg_tleOn Ubuntu - https://www.thatguyfromdelhi.com/2024/04/installing-pgtle-on-ubuntu-quick-guide.html - Unlock PostgreSQL Super Powers with
pg_tle- https://www.thatguyfromdelhi.com/2024/04/unlock-postgresql-superpowers-with-pgtle.html
