reset-pdb-access-privileges

reset-pdb-access-privileges.txt

 
 Current state of customer's pe-puppetdb privileges as seen in psql via \l:
 Name       |    Owner    | Encoding |   Collate   |    Ctype  |   Access privileges
 -----------------------------------------------------------------------------------
pe-puppetdb | pe-puppetdb | UTF8     | en_US.utf8 | en_US.utf8 | =T/"pe-puppetdb" 
                                                                 "pe-puppetdb"=CTc/"pe-puppetdb" 
                                                                 "pe-puppetdb-migrator"=c*/"pe-puppetdb"
                                                                 "pe-puppetdb"=c/"pe-puppetdb-migrator"
                                                                 
In newer vesions of PE we expect that pe-postgres owns all of the PE database objects. It's possible
that ownership from past releases wasn't updated properly during multiple upgrades from PE 3.7 -> PE 2019.8.

Steps to change pe-puppetdb access privileges to the state PE 2019.8 expects: 
(all commands below assume you're logged into psql as the pe-postgres user unless otherwise noted)

1. double check that pe-puppetdb is still in the state shown above using: \l 
2. confim you're logged in as the pe-postgres user by running: select current_user;
3. run: alter database "pe-puppetdb" owner to "pe-postgres";
4. run: \l 

You should now see what's below for the pe-puppetdb access privileges: 
=T/"pe-postgres"                       
"pe-postgres"=CTc/"pe-postgres"        
"pe-puppetdb-migrator"=c*/"pe-postgres"
"pe-postgres"=c/"pe-puppetdb-migrator"

5. Log out of psql and run: puppet agent -t
(the agent run should produce the following corrective changes)

[root@fast-bridgework ~]# puppet agent -t
Info: Using configured environment 'production'
Info: Retrieving pluginfacts
Info: Retrieving plugin
Info: Retrieving locales
Info: Loading facts
Info: Caching catalog for fast-bridgework.delivery.puppetlabs.net
Info: Applying configuration version '1595969861'
Notice: /Stage[main]/Puppet_enterprise::Profile::Database/Puppet_enterprise::App_database[puppetdb]/Pe_postgresql::Server::Db[pe-puppetdb]/Pe_postgresql::Server::Database_grant[GRANT pe-puppetdb - ALL - pe-puppetdb]/Pe_postgresql::Server::Grant[database:GRANT pe-puppetdb - ALL - pe-puppetdb]/Pe_postgresql_psql[GRANT ALL ON DATABASE "pe-puppetdb" TO "pe-puppetdb"]/command: command changed  to 'GRANT ALL ON DATABASE "pe-puppetdb" TO "pe-puppetdb"' (corrective)
Notice: /Stage[main]/Puppet_enterprise::Profile::Database/Puppet_enterprise::App_database[puppetdb]/Puppet_enterprise::Pg::Migrator_user[pe-puppetdb-migrator]/Puppet_enterprise::Psql[pe-puppetdb revoke pe-postgres's connect grant from pe-puppetdb]/Pe_postgresql_psql[pe-puppetdb revoke pe-postgres's connect grant from pe-puppetdb]/command: command changed  to 'REVOKE CONNECT ON DATABASE "pe-puppetdb" FROM "pe-puppetdb"' (corrective)
Notice: /Stage[main]/Puppet_enterprise::Profile::Database/Puppet_enterprise::App_database[puppetdb]/Puppet_enterprise::Pg::Migrator_user[pe-puppetdb-migrator]/Puppet_enterprise::Psql[SET ROLE pe-puppetdb-migrator; GRANT pe-puppetdb - CONNECT - pe-puppetdb]/Pe_postgresql_psql[SET ROLE pe-puppetdb-migrator; GRANT pe-puppetdb - CONNECT - pe-puppetdb]/command: command changed  to 'SET ROLE "pe-puppetdb-migrator"; GRANT CONNECT ON DATABASE "pe-puppetdb" TO "pe-puppetdb"' (corrective)
Notice: Applied catalog in 17.35 seconds

6. Still logged out of psql run: puppet agent -t 
(this agent run should produce no changes, just double checking pervious problem didn't resurface) 

7. Log back into psql and run: \l
(you should now see the following in the access privileges for pe-puppetdb)

=T/"pe-postgres"                       
"pe-postgres"=CTc/"pe-postgres"        
"pe-puppetdb-migrator"=c*/"pe-postgres"
"pe-postgres"=c/"pe-puppetdb-migrator" 
"pe-puppetdb"=CT/"pe-postgres"         
"pe-puppetdb"=c/"pe-puppetdb-migrator"

Notice there is an extra connect grant from the pe-puppetdb-migrator to pe-postgres
indicated by this line: "pe-postgres"=c/"pe-puppetdb-migrator"

8. In the psql prompt run: set role "pe-puppetdb-migrator"; revoke connect on database "pe-puppetdb" from "pe-postgres";

9. run: \l 
(access privileges for pe-puppetdb should now match what's seen below. This is the state we expect in 2019.8)

 Name       |    Owner    | Encoding |   Collate   |    Ctype  |   Access privileges
 -----------------------------------------------------------------------------------
pe-puppetdb | pe-postgres | UTF8     | en_US.utf8 | en_US.utf8 | =T/"pe-postgres"                       
                                                                 "pe-postgres"=CTc/"pe-postgres"        
                                                                 "pe-puppetdb-migrator"=c*/"pe-postgres"
                                                                 "pe-puppetdb"=CT/"pe-postgres"         
                                                                 "pe-puppetdb"=c/"pe-puppetdb-migrator"