How to Set Up PostgreSQL Read-Only User Permissions

Are you struggling with PostgreSQL read-only user permissions? This step-by-step guide will show you exactly how to create and configure read-only users in PostgreSQL 16. Whether you’re a database administrator or developer, you’ll learn how to properly set up schema-level permissions and avoid common pitfalls.

The Challenge: PostgreSQL Read-Only Access Not Working

Many developers face this common PostgreSQL challenge: you’ve created a read-only user and granted what seems like the correct permissions, but the user still can’t access the tables. Here’s what typically doesn’t work:

ALTER DEFAULT PRIVILEGES IN SCHEMA dv_s GRANT SELECT ON TABLES TO dm_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA dv_s to dm_ro;

If you’re experiencing this issue, you’re not alone. Let’s dive into why this happens and how to fix it.

Understanding the Problem: PostgreSQL Permission Layers

PostgreSQL’s security model uses a layered permission system. Think of it like a building – you need keys to enter the building (database), access the floor (schema), and enter the room (table). Missing any of these permissions blocks access to your data.

You need three key permission levels:

• Database-level access
• Schema-level permissions
• Table-level permissions

Step-by-Step Solution: Setting Up PostgreSQL Read-Only Access

Step 1: Connect as Superuser

First, connect to PostgreSQL as a superuser:

psql -U postgres

Step 2: Create Read-Only User

Set up your read-only user:

CREATE USER dm_ro WITH PASSWORD 'your_secure_password';

Step 3: Grant All Required Permissions

Execute these commands in order:

-- Grant database connection permission
GRANT CONNECT ON DATABASE dataverse TO dm_ro;

-- Grant schema access
GRANT USAGE ON SCHEMA dv_s TO dm_ro;

-- Grant table read permissions
GRANT SELECT ON ALL TABLES IN SCHEMA dv_s TO dm_ro;

-- Set permissions for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA dv_s GRANT SELECT ON TABLES TO dm_ro;

Verifying Your PostgreSQL Permissions

Confirm your setup with these commands:

-- Switch to dm_ro user
\c dataverse dm_ro

-- Check granted permissions
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'dm_ro';

-- List accessible tables
SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'dv_s';

Essential PostgreSQL Permission Tips

1. ✅ Always start with database CONNECT permission
2. ✅ Don’t forget schema USAGE permission
3. ✅ Run permission commands as superuser
4. ✅ Remember that ALTER DEFAULT PRIVILEGES only affects new tables

Troubleshooting PostgreSQL Permissions

If you’re still having issues, check these common problems:

1. Missing database connection rights
2. Forgotten schema USAGE permission
3. Incorrect permission execution context
4. Assumptions about ALTER DEFAULT PRIVILEGES

Conclusion: Mastering PostgreSQL Read-Only Access

By following this guide, you’ve learned how to properly set up read-only users in PostgreSQL 16. Remember that proper permission configuration is crucial for database security and functionality.

Quick Reference Checklist

• [ ] Database CONNECT permission granted
• [ ] Schema USAGE permission set
• [ ] SELECT permissions on tables configured
• [ ] Future table permissions established
• [ ] Verification steps completed

Need to troubleshoot specific PostgreSQL permission issues? Try the verification queries above and double-check each permission layer.