Comprehensive Guide to PostgreSQL Read-Only User Permissions: Resolving SELECT Permission Denied Errors

Problem Context and Error Analysis

In PostgreSQL database administration, configuring read-only users is a common operational requirement. However, many administrators encounter a typical issue: users can successfully connect to the database and view table structures, but receive permission errors when attempting simple SELECT queries. The error message typically appears as follows:

ERROR: permission denied for relation mytable
SQL state: 42501

This error indicates that the user lacks SELECT permission on the specific table. Although administrators may have executed authorization statements like GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;, the permission system still denies access requests. This situation usually stems from insufficient understanding of PostgreSQL's permission hierarchy.

PostgreSQL Permission System Architecture

PostgreSQL employs a multi-layered permission control model. Understanding this model is crucial for resolving permission issues. The permission system primarily consists of the following levels:

1. Database Connection Permissions: Users must first obtain permission to connect to specific databases
2. Schema Usage Permissions: Users need permission to create objects in specific schemas (for read-only users, mainly USAGE permission)
3. Object Access Permissions: Users require appropriate operation permissions on specific tables, sequences, and other objects
4. Default Privilege Settings: Affect permission assignments for future created objects

Common configuration errors often occur when administrators skip the first two permission levels and directly configure object access permissions.

Complete Solution Analysis

Based on PostgreSQL 9+ versions, here is the correct workflow for creating fully functional read-only users. This solution considers all necessary levels of the permission system:

-- Step 1: Create read-only user
CREATE USER readonly WITH ENCRYPTED PASSWORD 'readonly';

-- Step 2: Grant usage permission on public schema
GRANT USAGE ON SCHEMA public TO readonly;

-- Step 3: Set default privileges (affects future created tables)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

-- Step 4: Repeat the following configuration for each database
GRANT CONNECT ON DATABASE foo TO readonly;
\c foo
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Detailed Explanation of Key Configuration Steps

1. Database Connection Permission Configuration

The GRANT CONNECT ON DATABASE foo TO readonly; statement grants the user permission to connect to a specific database. This is the first link in the permission chain. Without this permission, users cannot even establish database connections. Many configuration schemes overlook this step, resulting in users existing but being unable to access target databases.

2. Schema Permission Management

GRANT USAGE ON SCHEMA public TO readonly; grants the user permission to look up objects in the public schema. USAGE permission allows users to access objects within the schema but does not permit creating or modifying objects. For read-only users, this is the minimum necessary schema permission.

3. Granting Permissions on Existing Objects

The GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; statement grants the user SELECT permission on all existing tables in the schema. Similarly, GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly; grants query permission on sequences. These two statements together ensure users can access all relevant existing objects.

4. Default Privilege Settings

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly; is a crucial but often overlooked configuration. This statement sets default permissions for tables created in the specified schema in the future. Without this setting, even with correctly configured permissions for existing tables, newly created tables remain inaccessible to read-only users.

Comparison with Common Misconfigurations

Many simplified configurations attempted by administrators often omit critical steps:

-- Example of incorrect configuration (incomplete)
CREATE USER readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
-- Missing: database connection permission, schema usage permission, default privilege settings

This configuration results in users being able to connect to the database (if connections are allowed by default), see table lists, but unable to execute SELECT queries due to missing necessary schema usage permissions.

Permission Verification and Testing

After configuration, permissions should be verified through the following steps:

-- Connect as read-only user
psql -U readonly -d foo

-- Verify connection permission
SELECT current_database();

-- Verify table access permission
SELECT * FROM mytable LIMIT 1;

-- Verify sequence access permission (if applicable)
SELECT nextval('mysequence');

If all queries execute successfully, the permission configuration is complete and correct.

Cross-Database Configuration Considerations

In actual production environments, it's often necessary to configure the same read-only user permissions across multiple databases. PostgreSQL doesn't provide a command to configure permissions for all databases at once, so configuration steps must be repeated for each database. This process can be automated through scripting:

-- Example script framework
DO $$
DECLARE
    dbname text;
BEGIN
    FOR dbname IN SELECT datname FROM pg_database WHERE datistemplate = false
    LOOP
        EXECUTE format('GRANT CONNECT ON DATABASE %I TO readonly', dbname);
        -- Other permission configuration statements
    END LOOP;
END $$;

Security Best Practices

1. Use strong passwords and change them regularly
2. Limit connection source IP addresses for read-only users (configured via pg_hba.conf)
3. Regularly audit permission settings to ensure no over-privileging
4. Create different users for different read-only purposes to achieve permission separation
5. Consider using roles (ROLE) to manage permission groups for better maintainability

Conclusion

PostgreSQL's permission system, while powerful, is relatively complex. When configuring read-only users, it's essential to comprehensively consider four levels: connection permissions, schema permissions, object permissions, and default permissions. Omitting any level may result in incomplete permission configuration, leading to "permission denied" errors. The complete solution provided in this article has been practically verified and ensures read-only users function correctly in all relevant scenarios. Understanding and correctly applying these permission configuration principles forms the foundation of PostgreSQL database security management.