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?
Best Answer
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.