Documentation on required readyset user privileges

[cygann]

I'm looking to set up a new user on a RDS postgres database such that its credentials can be distributed to a ReadySet instance.

However, it is not clear to me what permissions/privileges this user's role needs to have in order to work. Could someone clarify what these should be? Thank you!

2023-03-01T21:16:54.003709Z ERROR replicators: Error in replication, will retry after timeout context=LogContext({"deployment": "brain_interfaces_readyset"}) error=Error during replication: PostgreSQL: db error: ERROR: permission denied for database <my database name> timeout_sec=30

[jseldess]

Hi @cygann. This is a great question that should definitely be answered in our documentation. We'll work on that. In the meantime, here's some guidance for RDS Postgres.

The ReadySet user requires certain privileges to create the replication event triggers on your behalf and also requires privileges for operations you will send through ReadySet (e.g., reads, writers, DDL). There are a few ways to accomplish this:

• Convenient: Use your RDS master user (which is postgres by default). It has all required privileges.

• Recommended: Create a new user for ReadySet and grant it the following privileges so it can create event triggers and start the replication process on initial connection:

  create user <user> password '<password>';
  grant rds_superuser to <user>; 
  grant connect on database <database> to <user>; 
  grant create on database <database> to <user>; 

  Also grant the privileges required for the operations you will send through ReadySet. For example, if you’ll just be sending reads to ReadySet, you’d need:

  grant select on all tables in schema <schema> to <user>;

  If you’ll be sending inserts, deletes, updates, and/or schema changes, you’ll need to grant the user privileges for those as well.

• Cautious: After starting ReadySet, revoke rds_superuser status so your ReadySet user can’t do things like create roles and create databases (see here for a list of what rds_superuser can do) and assign the rds_replication status so your ReadySet user can continue consuming the replication stream:

  revoke rds_superuser from <user>; 
  grant rds_replication to <user>;.

Does that help? Please let us know if you have follow-up questions.

[houqp]

@jseldess confirming that all of these has to be done on the master node and not a read replica?

Also is there a way to cleanly "uninstall" all the changes that readyset added to the database in the future?

[jseldess]

@houqp, yes, that's right. ReadySet must be connected to the primary database, not a read replica: https://docs.readyset.io/guides/production-notes/#amazon-rds-for-postgres.

In terms of cleanly uninstalling, we're working on a command to help with that. We'll also document the manual process soon. In the meantime, here's the basic guidance:

1. Stop ReadySet.
2. If you revoked rds_superuser from the ReadySet user, connect psql as your master user and regrant that status to the user:

   grant rds_superuser to <user>;

3. Connect psql with the ReadySet user. This is important because you have to drop a bunch of resources created/owned by that user.

4. Identify and then drop the ReadySet replication slot:

   SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS replicationSlotLag, active FROM pg_replication_slots ;
    slot_name | replicationslotlag | active
    -----------+--------------------+--------
    readyset  | 13 GB              | f
   (1 row)
   
   readyset=> SELECT pg_drop_replication_slot('readyset');
    pg_drop_replication_slot
   --------------------------
   
   (1 row)
   
   readyset=> SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS replicationSlotLag, active FROM pg_replication_slots ;
    slot_name | replicationslotlag | active
   -----------+--------------------+--------
   (0 rows)

5. Drop the schema, functions, and event triggers created by ReadySet:

   readyset=> drop schema readyset cascade;
   NOTICE:  drop cascades to 15 other objects
   DETAIL:  drop cascades to function readyset.is_pre14()
   drop cascades to function readyset.replicate_create_table()
   drop cascades to event trigger readyset_replicate_create_table
   drop cascades to function readyset.replicate_alter_table()
   drop cascades to event trigger readyset_replicate_alter_table
   drop cascades to function readyset.replicate_create_view()
   drop cascades to event trigger readyset_replicate_create_view
   drop cascades to function readyset.replicate_drop()
   drop cascades to event trigger readyset_replicate_drop
   drop cascades to function readyset.replicate_create_type()
   drop cascades to event trigger readyset_replicate_create_type
   drop cascades to function readyset.pre_alter_type()
   drop cascades to event trigger readyset_pre_alter_type
   drop cascades to function readyset.replicate_alter_type()
   drop cascades to event trigger readyset_replicate_alter_type
   DROP SCHEMA

6. Drop the ReadySet publication. This is the only drop that requires rds_superuser, based on my testing.

   readyset=> SELECT * FROM pg_publication;
     oid  | pubname  | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
   -------+----------+----------+--------------+-----------+-----------+-----------+-------------+------------
    16992 | readyset |    16908 | t            | t         | t         | t         | t           | f
   (1 row)
   
   readyset=> drop publication readyset; 
   DROP PUBLICATION
   readyset=> SELECT * FROM pg_publication;
    oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
   -----+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
   (0 rows)

You might then also want to connect psql as your master user, remove the privileges from the ReadySet user and drop the user. You might want to disable logical replication as well if it was enabled only for ReadySet.

Does that help?

[houqp]

Yes, this is very helpful, thank you!