How to create a Read Only User in Postgres

Managing database security is a critical part of maintaining data integrity and controlling access. One of the most common use cases is setting up a read-only user in PostgreSQL. This type of user can query data without the ability to modify it, making it ideal for analytics, reporting, or external stakeholders who need access to data without posing any risk to its integrity.

In this guide, we will walk you through the simple and effective steps to create a read-only user in PostgreSQL. Whether you’re managing an enterprise database or working on a smaller project, ensuring the right access permissions can save time, prevent errors, and enhance overall database security.

Steps to Create a Read-Only User in PostgreSQL

1. Create a New User

To begin, create a new user account in your PostgreSQL database. This will serve as your read-only user:

CREATE USER username WITH PASSWORD 'your_password';

• Replace username with the desired username.
• Use a secure and unique password for your_password.

2. Grant Database Connection Access

Ensure the user can connect to the target database:

GRANT CONNECT ON DATABASE database_name TO username;

• Replace database_name with the name of your database.

This grants the user the ability to establish a connection to the database, but no permissions to access or modify its data yet.

3. Grant Schema Usage

Next, allow the user to access a specific schema within the database:

GRANT USAGE ON SCHEMA schema_name TO username;

• Replace schema_name with the name of your schema, such as public.

This permission lets the user view the structure of the schema but not the data it contains.

4. Grant SELECT Permissions

Now, give the user the ability to query data from tables within the schema.

For a Specific Table:

GRANT SELECT ON table_name TO username;

• Replace table_name with the name of the table you want to grant access to.

For All Tables in the Schema:

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;

This ensures the user can query all tables within the specified schema.

5. Grant Access to Future Tables (Optional)

If you plan to create new tables in the schema and want the user to automatically gain read-only access, set default privileges:

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name
GRANT SELECT ON TABLES TO username;

This step saves you from manually granting permissions for every new table.

Why Create a Read-Only User in PostgreSQL?

1. Data Security: Read-only users can access data without the risk of accidental or unauthorized modifications.
2. Controlled Access: Perfect for external clients, analytics teams, or reporting tools that only need to view data.
3. Simplified Management: Automating permissions for future tables ensures a consistent and secure configuration.

Enhance Your Data Analytics with Sequel

For seamless data access and analytics, Sequel is a powerful tool that transforms how you interact with your PostgreSQL database. With its AI-powered natural language querying, anyone can ask questions in plain English and get instant results—no SQL expertise needed. Sequel simplifies data visualization, generates actionable insights, and supports multiple databases, all while ensuring enterprise-grade security.

Whether you’re managing reporting or exploring trends, Sequel makes analytics faster, smarter, and more accessible for everyone. Visit the website to learn more!

Conclusion

Creating a read-only user in PostgreSQL is a simple yet powerful way to manage database access and ensure data security. By following the steps above, you can grant selective access to your database and maintain strict control over who can modify your data. Whether for reporting, analytics, or external collaboration, a read-only user is an essential part of any secure database setup.

For more tips on PostgreSQL, be sure to explore additional resources and guides tailored to database administrators and developers.