While we are creating any new tables, we used to give SELECT privilege on the newly created tables using the below command. But we are seeing now , in case of partitioned tables even if we had given the privileges in the same fashion, the user is not able to query specific partitions but only the table. Commands like "select * from schema1.<partition_name> " are erroring out with the "insufficient privilege" error , even if the partition belongs to the same table.
Grant SELECT ON <table_name> to <user_name>;
Grant was seen as a one time command which needed while creating the table and then subsequent partition creation for that table was handled by the pg_partman extension. But that extension is not creating or copying any grants on the table to the users. We were expecting , once the base table is given a grant , all the inherited partitions will be automatically applied to those grants. but it seems it's not working that way. So is there any other way to handle this situation?
In other databases(say like Oracle) we use to create standard "roles"(Read_role, Write_role etc..) and then provide grants to the user through those roles. And the objects were given direct grants to those roles. Similarly here in postgres we were granting "read" or "write" privileges on objects to the roles and letting the users login to the database using those roles and thus getting all the read/write privileges assigned to those roles. Are we doing anything wrong?
