PostgreSQL GRANT ALL PRIVILEGES ON DATABASE: Why It Doesn’t Grant Table Permissions and How to Fix


3 views

Many PostgreSQL users assume that GRANT ALL PRIVILEGES ON DATABASE database_name TO username; grants full access to all tables within that database. However, this command actually grants permissions at the database level, not the table level. Here's what it really does:

  • Allows connection to the database (CONNECT)
  • Permits schema creation (CREATE)
  • Enables temporary table creation (TEMPORARY)

PostgreSQL implements a security model where database-level privileges and table-level privileges are separate. Even with ALL privileges on the database, users still need explicit permissions on:

-- This grants database-level privileges but not table access
GRANT ALL PRIVILEGES ON DATABASE my_db TO new_user;

To grant full access to all current and future tables in a database, you need to:

-- Connect to the target database as owner/admin
\c my_db

-- Grant usage on schema (usually public)
GRANT USAGE ON SCHEMA public TO new_user;

-- Grant all privileges on all tables
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user;

-- Grant privileges on future tables (requires PostgreSQL 9.0+)
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT ALL PRIVILEGES ON TABLES TO new_user;

Imagine you're setting up a new analytics user that needs full access to a reporting database:

-- As postgres superuser
CREATE USER analytics_user WITH PASSWORD 'secure123';

-- Database-level access
GRANT CONNECT ON DATABASE reporting_db TO analytics_user;

-- Schema and table permissions
\c reporting_db
GRANT USAGE ON SCHEMA public TO analytics_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO analytics_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO analytics_user;

-- Future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO analytics_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT ALL PRIVILEGES ON SEQUENCES TO analytics_user;

After granting privileges, verify them with:

-- Check database privileges
SELECT * FROM pg_database WHERE datname = 'my_db';

-- Check table privileges in schema
SELECT * FROM information_schema.role_table_grants 
WHERE grantee = 'new_user';
  • Default privileges only affect objects created after running ALTER DEFAULT PRIVILEGES
  • For multiple schemas, you must repeat the commands for each schema
  • Consider using roles for permission management across multiple users

Many PostgreSQL users assume that GRANT ALL PRIVILEGES ON DATABASE my_db TO new_user; grants full access to all tables within that database. This is a common misunderstanding. Let's examine what this command actually does:

-- This grants database-level privileges only:
GRANT ALL PRIVILEGES ON DATABASE my_db TO new_user;

This command specifically grants the following privileges:

  • Ability to connect to the database (CONNECT)
  • Ability to create temporary tables (TEMP)
  • Ability to create schemas (CREATE)

Even after running the above command, you'll encounter permission errors when trying to access tables because:

  • Database privileges don't automatically cascade to schema objects
  • Tables have their own separate permission system
  • The public schema has default restrictive permissions

To grant full access to all current tables in all schemas:

-- Grant usage on schema (required before table access)
GRANT USAGE ON SCHEMA public TO new_user;

-- Grant all privileges on all tables in schema
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user;

-- Include sequences if needed
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO new_user;

To ensure new tables created in the future also have the proper permissions:

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES 
    FOR ROLE current_owner_user
    IN SCHEMA public
    GRANT ALL PRIVILEGES ON TABLES TO new_user;

-- Optionally include sequences
ALTER DEFAULT PRIVILEGES 
    FOR ROLE current_owner_user
    IN SCHEMA public
    GRANT ALL PRIVILEGES ON SEQUENCES TO new_user;

Here's a comprehensive example for setting up a new user with full access:

-- As postgres or database owner:
CREATE USER new_user WITH PASSWORD 'secure_password';

-- Database-level access
GRANT CONNECT ON DATABASE my_db TO new_user;

-- Schema permissions
GRANT USAGE ON SCHEMA public TO new_user;

-- Existing tables
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO new_user;

-- Future tables
ALTER DEFAULT PRIVILEGES 
    IN SCHEMA public
    GRANT ALL PRIVILEGES ON TABLES TO new_user;

ALTER DEFAULT PRIVILEGES 
    IN SCHEMA public
    GRANT ALL PRIVILEGES ON SEQUENCES TO new_user;