Question: How do you grant permissions on partitioned tables in PostgreSQL?

Answer

In PostgreSQL, granting permissions on partitioned tables and their partitions is essential for database security and access management. You can use the GRANT statement to provide specific types of access to a user or role for the parent partitioned table as well as its individual partitions.

When you grant permissions on the parent partitioned table, those permissions do not automatically apply to its existing or future partitions. Permissions need to be explicitly granted on each individual partition if required. However, starting with PostgreSQL 10, there's a feature that simplifies permission management for partitioned tables using the ALTER DEFAULT PRIVILEGES command.

Granting Permissions on the Partitioned Table

To grant SELECT permission on a partitioned table to a user:

GRANT SELECT ON table_name TO user_name;

Replace table_name with the name of your partitioned table and user_name with the name of the user or role.

Granting Permissions on Individual Partitions

You must grant permissions on each partition individually. Here’s how to grant SELECT permission on a single partition:

GRANT SELECT ON table_name_partition1 TO user_name;

Replace table_name_partition1 with the name of the partition and user_name with the name of the user or role.

Using ALTER DEFAULT PRIVILEGES

To automate the granting of permissions on future partitions, use the ALTER DEFAULT PRIVILEGES command. This command specifies the privileges that will be automatically applied to objects created in the future.

For example, to ensure that a user has SELECT permission on all future partitions of a specific partitioned table:

ALTER DEFAULT PRIVILEGES FOR ROLE role_name IN SCHEMA schema_name
GRANT SELECT ON TABLES TO user_name;

Replace role_name with the role that will create the future partitions, schema_name with the name of the schema where the partitioned table exists, and user_name with the user who should have the SELECT permission.

Note: ALTER DEFAULT PRIVILEGES does not affect already-existing partitions. Permissions for those must be granted separately.

In summary, managing permissions in PostgreSQL requires careful consideration, especially with partitioned tables. While permissions granted on a parent table do not cascade to its partitions, the ALTER DEFAULT PRIVILEGES command offers a way to manage permissions for future partitions more efficiently.