0

Say a user is created in postgres 9.6 with some specific privilege set:

CREATE USER foo_user WITH ENCRYPTED PASSWORD 'foo_password';
GRANT CONNECT ON DATABASE bar_db TO foo_user;
GRANT USAGE ON SCHEMA public TO foo_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO foo_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public to foo_user;

(Copied and modified from here.)

If that user is created on a primary database, and there is a follower synced with WAL streaming replication, will that user be created with an identical set of privileges on the follower automatically?

It looks like the answer is yes, because users and permissions are represented with tables, and those special tables are probably treated the same as any other table for write ahead logging. But I can't confirm this in any of these docs pages. Can confirm or debunk my thinking? And ideally point me at some related documentation that I can't seem to find?

2

If you're doing WAL streaming, you're dealing with physical replication. That means your secondary data cluster is a total clone of the primary.

Your data cluster is made of files. With physical replication your files will be totally identical bit by bit between the primary and the secondary node.

So yes, roles, permission, etc are replicated.

  • Although they can have different pg_hba.conf files. This does allow you create roles (on the master) which can't log on to the master, but still have them be able to log on to the replica. – jjanes Apr 19 '17 at 16:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.