In PostgreSQL, a common point of confusion for new administrators is how to handle permissions for tables that haven’t been created yet. While the command GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO new_user; is a powerful shortcut, it only applies to tables that already exist within the schema at the exact moment the command is executed.
To ensure a user has CRUD (Create, Read, Update, Delete) capabilities on future tables, you must modify the default behavior of the database using a specific administrative command. This guide explains how to manage both existing and future permissions to maintain a seamless access control workflow.
Table of Contents
Key takeaways for granting permissions on future tables in PostgreSQL
- Standard GRANT command → This statement only affects objects that currently exist in the database. It does not provide access to any tables or sequences created after the command is executed.
- ALTER DEFAULT PRIVILEGES command → The primary tool for automating access to future objects. It allows administrators to define a set of pre-configured rights that are automatically applied to every new table, sequence, or function at the moment of its creation.
- Schema USAGE privilege → An essential structural prerequisite for data interaction. Even if a user has perfected default permissions for future tables, they will remain blocked unless they have the right to “look up” objects within the schema container.
Method 1: Granting Rights on Existing Tables
If you need to provide immediate access to data that is currently stored in your schema, you use the mass-grant syntax. This is highly efficient for managing large schemas without issuing individual commands for every table.
The Command: GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO new_user;
What it does: This command identifies every table currently inside the “public” schema and attaches the specified Access Control List (ACL) flags to them.
However, the moment a developer creates a new table, new_user will have no access to it because the ACL for that new object will be empty by default. For a deeper look at managing these statements, see the PostgreSQL GRANT Statement: A Complete Guide.
Method 2: Automating Access for Future Tables
To solve the “future table” problem, PostgreSQL provides the ALTER DEFAULT PRIVILEGES command. This statement allows you to define a set of permissions that will be automatically applied to any object created from that point forward.
The Command: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO new_user;
Critical Details:
- Ownership Matters: By default, this command only applies to tables created by the user who runs the command.
- Targeting specific creators: If an administrator wants to define defaults for tables created by a specific “web_app” role, they must specify:
ALTER DEFAULT PRIVILEGES FOR ROLE web_app IN SCHEMA public .... - Verification: You can verify if these defaults are set by checking the
pg_default_aclsystem catalog.
The Role of Schema “USAGE”
Regardless of whether you grant permissions on current or future tables, the user will still face “permission denied” errors if they cannot access the “container” (the schema) itself. You must explicitly permit the user to “look up” objects within the namespace.
See also: Mastering the Linux Command Line — Your Complete Free Training Guide
Required Command: GRANT USAGE ON SCHEMA public TO new_user;
Without this, the user cannot see the tables to interact with them, even if the table-level GRANT was successful. Understanding this multi-layered requirement is essential for Understanding PostgreSQL Access Control: Login Roles vs. Connect Privileges.
Summary of Permission Scope
| Target Object Type | Recommended Command | Duration of Effect |
|---|---|---|
| Existing Tables | GRANT ... ON ALL TABLES | Immediate; applies only to current objects. |
| Future Tables | ALTER DEFAULT PRIVILEGES | Permanent; applies to all new objects. |
| Namespace Access | GRANT USAGE ON SCHEMA | Required to “see” any table in the schema. |
FAQs
Does GRANT ALL ON DATABASE give access to future tables? No, in the database context, ALL only grants the right to connect, create new schemas, and create temporary tables. It does not provide any rights to individual tables or data.
How can I check what permissions a user currently has? Use the \dp meta-command in psql to see the ACL strings for specific tables. For more detailed auditing, consult How to view access privileges in PostgreSQL.
Can I set default privileges for an entire database? Yes, if you omit the IN SCHEMA clause, the ALTER DEFAULT PRIVILEGES command will apply to the entire current database.
What if I want to remove these automatic permissions later? You use the REVOKE version of the command: ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM new_user;.
Do these commands affect Row-Level Security (RLS)? No, standard GRANTs manage table and column access, while RLS is handled through separate Policies that filter the horizontal rows of data.
