Complete Guide to Creating Read-Only Users in PostgreSQL

Overview of PostgreSQL Permission System

PostgreSQL's permission management system is based on the concept of roles, which differs significantly from other database systems like MySQL. In PostgreSQL, both users and groups are implemented through roles, providing a unified design that makes permission management more flexible and consistent. Understanding PostgreSQL's permission hierarchy is crucial for properly configuring read-only users.

Basic Permission Configuration Process

The first step in creating a read-only user is to establish the role and set connection permissions. The following code demonstrates the complete configuration process:

-- Create read-only user role
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'secure_password';

-- Connect to target database
\c target_database;

-- Grant database connection permission
GRANT CONNECT ON DATABASE target_database TO readonly_user;

-- Grant schema usage permission
GRANT USAGE ON SCHEMA public TO readonly_user;

-- Grant query permission on existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Detailed Permission Hierarchy

PostgreSQL's permission system employs a hierarchical structure, with permissions refined layer by layer from the database level down to the object level. The GRANT CONNECT permission at the database level only allows users to connect to the database, while actual table access permissions must be granted separately at the schema and table levels. This design ensures fine-grained permission control but also increases configuration complexity.

Bulk Permission Management Techniques

For databases containing a large number of tables, granting permissions individually is impractical. PostgreSQL version 9.0 and above provide bulk permission management functionality:

-- Grant query permission on all tables in schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- Grant read permission on sequences (optional)
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_user;

Default Permission Configuration

To ensure that newly created tables automatically inherit permission settings, use the ALTER DEFAULT PRIVILEGES command:

-- Set default query permissions for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly_user;

It's important to note that default permissions only affect objects created by the user executing the command. In team development environments, it's recommended that the database owner uniformly sets default permissions.

Legacy Version Compatibility Solutions

For PostgreSQL versions prior to 9.0, dynamic SQL can be used to generate permission grant statements:

SELECT 'GRANT SELECT ON ' || relname || ' TO readonly_user;'
FROM pg_class 
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname = 'public' 
    AND relkind IN ('r', 'v', 'S');

This method generates corresponding GRANT statements by querying system catalog tables, making it suitable for situations with a large number of tables.

Permission Verification and Testing

After configuration is complete, it's essential to verify the correctness of permission settings. Testing can be performed through the following steps:

-- Connect as read-only user
psql -U readonly_user -d target_database

-- Test query permissions
SELECT * FROM some_table LIMIT 1;

-- Test write permissions (should fail)
INSERT INTO some_table VALUES (1, 'test');

Security Best Practices

When configuring read-only users, consider the following security measures:

• Use strong passwords and change them regularly
• Restrict network access range for read-only users
• Regularly audit permission settings
• Avoid storing sensitive data in public schema
• Consider using row-level security policies for further data access restrictions

Common Issues and Solutions

In practical applications, you might encounter issues where permission configurations don't take effect. Common causes include:

• Not properly switching to the target database when granting permissions
• Misspelled schema names
• Permission cache not refreshed
• Improperly configured role inheritance relationships

Through systematic permission configuration and rigorous testing procedures, you can ensure that read-only user functionality meets expectations.