Postgres: To grant access to database objects

1. Grant Access to All Tables in a Schema

To grant access to all tables in a specific schema, you can use the following command:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA 

To ensure the user gets access to future tables created in the schema, use:

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user_name;

2. Grant Access to Specific Tables

If you want to grant access to specific tables, use:

GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO user_name;

• Replace table_name with the name of the table.

3. Grant Read-Only Access

For read-only access to all tables in a schema:

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO user_name;

And for future tables:

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

4. Using Predefined Roles (PostgreSQL 14+)

PostgreSQL 14 introduced predefined roles for easier management:

• pg_read_all_data: Grants read-only access to all tables.
• pg_write_all_data: Grants read and write access to all tables.

To assign these roles:

GRANT pg_read_all_data TO user_name;
-- Or
GRANT pg_write_all_data TO user_name;

These roles apply globally across all databases.

Always ensure the user has USAGE privileges on the schema to access its objects:

GRANT USAGE ON SCHEMA schema_name TO user_name;

This setup ensures the user can interact with the tables as intended.