PostgreSQL Database Permission Management: Best Practices for Granting Full User Privileges

Overview of PostgreSQL Permission System

PostgreSQL's permission management system is based on the Role concept, where access to all database objects (tables, views, sequences, functions, etc.) is controlled through permissions. When granting full database privileges to a user, it's essential to understand the hierarchical structure of permissions: database-level, schema-level, and object-level permissions.

Basic Connection Permission Configuration

First, ensure the user can connect to the target database. By default, the PUBLIC role has CONNECT privilege, but for security reasons, explicit granting is recommended:

GRANT CONNECT ON DATABASE my_db TO my_user;

This command ensures the user can establish a connection to the specified database, which is a prerequisite for all subsequent operations.

Simplified Approach for PostgreSQL 14 and Later

PostgreSQL 14 introduced two predefined non-login roles that significantly simplify permission management:

GRANT pg_read_all_data TO my_user;
GRANT pg_write_all_data TO my_user;

The pg_read_all_data role grants SELECT privileges on all tables, views, and sequences, plus USAGE on all schemas. The pg_write_all_data role provides INSERT, UPDATE, and DELETE privileges. These two roles combined cover most data manipulation needs, but note they don't include DDL operation privileges.

Universal Permission Configuration Method

For all PostgreSQL versions, complete control can be achieved through layered permission configuration:

Schema-Level Permissions

Grant operational permissions on target schemas:

GRANT USAGE ON SCHEMA public TO my_user;
GRANT CREATE ON SCHEMA public TO my_user;

Or simplify with ALL privileges:

GRANT ALL ON SCHEMA public TO my_user;

Object-Level Permission Configuration

Grant full privileges on existing tables, sequences, and other objects:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO my_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO my_user;

Note that sequence privileges are particularly important for traditional table designs using serial columns. PostgreSQL 10's IDENTITY columns can avoid this dependency.

Batch Permission Management

For environments with multiple custom schemas, use dynamic SQL for batch authorization:

DO $$
BEGIN
   EXECUTE (
      SELECT string_agg(format('GRANT USAGE ON SCHEMA %I TO my_user', nspname), '; ')
      FROM pg_namespace
      WHERE nspname <> 'information_schema'
      AND nspname NOT LIKE 'pg\_%'
   );
END
$$;

This code automatically excludes system schemas and only authorizes user-defined schemas.

Default Privilege Configuration

To ensure newly created objects automatically inherit permissions, configure default privileges:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO my_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO my_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO my_user;

Default privilege settings are crucial for long-term maintenance, ensuring newly created objects don't become permission vulnerabilities.

Permission Verification and Testing

After configuration, verify permission settings through actual connections and operations:

-- Switch to target user role
SET ROLE my_user;

-- Test various operations
SELECT * FROM existing_table;
INSERT INTO existing_table VALUES (...);
CREATE TABLE new_table (id SERIAL, data TEXT);

Complete permission testing should include core operations like data querying, data modification, and object creation.

Security Considerations

When granting full privileges, security implications must be considered:

• Avoid granting unnecessary privileges in production environments
• Regularly audit permission usage
• Consider using Row-Level Security (RLS) for fine-grained control
• Maintain audit logs for sensitive operations

Version Compatibility Considerations

Different PostgreSQL versions have variations in permission management:

• PostgreSQL 15 tightened CREATE privileges on the public schema
• PostgreSQL 14's predefined roles significantly simplify permission management
• Upgrading to newer versions is recommended for better security and convenience

Practical Application Scenarios

In development/production environment separation scenarios, full privileges can be configured for development databases while restricting access to production databases. This strategy ensures development efficiency while maintaining production environment security and stability.