How to Create a Read Only User in PostgreSQL

To create a read only user in PostgreSQL, first create a new user with CREATE USER. Then grant SELECT permission on the desired tables or entire database using GRANT SELECT ON. This restricts the user to only read data without making changes.

Syntax

Creating a read only user involves two main steps:

• Create User: Use CREATE USER username WITH PASSWORD 'password'; to add a new user.
• Grant Permissions: Use GRANT SELECT ON table_name TO username; to allow read-only access on specific tables.

You can also grant SELECT on all tables in a schema or database.

sql

CREATE USER username WITH PASSWORD 'password';
GRANT SELECT ON table_name TO username;

Example

This example creates a read only user named readonly_user with password readonlypass. It grants SELECT permission on all tables in the public schema of the mydb database.

sql

CREATE USER readonly_user WITH PASSWORD 'readonlypass';

-- Connect to the database where tables exist
\c mydb

-- Grant USAGE on schema to allow access
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- Optional: To allow future tables to be accessible
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

Common Pitfalls

Common mistakes when creating a read only user include:

• Not granting USAGE on the schema, which prevents access to tables.
• Forgetting to grant SELECT on all existing tables, so the user cannot read data.
• Not setting default privileges, so new tables are not accessible to the read only user.
• Granting excessive permissions like INSERT or UPDATE by mistake.

Always verify permissions by connecting as the read only user and trying to query and modify data.

sql

/* Wrong: Missing USAGE on schema */
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

/* Right: Include USAGE on schema */
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Quick Reference

Command	Purpose
CREATE USER username WITH PASSWORD 'password';	Create a new database user
GRANT USAGE ON SCHEMA schema_name TO username;	Allow user to access schema objects
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;	Allow read-only access to all tables
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO username;	Grant read access on future tables

Key Takeaways

Create a user with CREATE USER and a secure password.

Grant USAGE on the schema to allow access to tables.

Grant SELECT on tables to provide read-only permissions.

Set default privileges to cover future tables automatically.

Test the user permissions by connecting and querying data.