How to Create a Read-Only user in PostgreSQL?

How to Create a Read-Only user in PostgreSQL?

Creating a read-only user in PostgreSQL is useful when you want to allow certain users to view data in a database without making any modifications. This is particularly beneficial for reporting, analytics, or providing limited access to external stakeholders.

In PostgreSQL, setting up a read-only user involves:

• Creating a new user.
• Granting access to the necessary database.
• Assigning permissions on specific tables (or the entire schema) for read-only access.

Steps to Create a Read-Only user

1. Create the Read-Only user

Code:

-- Create a new user with a password
CREATE USER readonly_user WITH PASSWORD 'password';

Explanation:

• CREATE USER readonly_user: Defines a new user named readonly_user.
• WITH PASSWORD 'password': Sets the password for the new user.

2. Grant Access to the Database

Code:

-- Grant the CONNECT privilege on the database to the user
GRANT CONNECT ON DATABASE your_database TO readonly_user;

Explanation:

• GRANT CONNECT ON DATABASE your_database: Allows readonly_user to connect to your_database.

3. Grant Usage on the Schema

-- Grant usage on the public schema, enabling access to the schema's structure
GRANT USAGE ON SCHEMA public TO readonly_user;

Explanation:

• GRANT USAGE ON SCHEMA public: Provides schema-level access so the user can see table structure but not modify data.

4. Grant Select Privileges on All Tables

To provide read-only access to all tables in a schema, you can use the following command:

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

Explanation:

• GRANT SELECT ON ALL TABLES IN SCHEMA public: Grants read-only access to every table in the specified schema (here, public).

5. Automate Future Table Permissions

To ensure that readonly_user retains read-only access to any new tables created in the future, set default privileges:

-- Set default privileges for future tables created in the schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

Explanation:

• ALTER DEFAULT PRIVILEGES IN SCHEMA public: Sets default privileges on any new tables created in the public schema.

Example Code and Explanation

Suppose you want to create a read-only user named report_viewer for a database called sales_data.

1. Create the User

Code:

-- Step 1: Create the read-only user with a password
CREATE USER report_viewer WITH PASSWORD 'securepassword';

2. Grant Database Access

Code:

-- Step 2: Grant CONNECT privilege on the 'sales_data' database
GRANT CONNECT ON DATABASE sales_data TO report_viewer;

3. Grant Usage on the Schema

Code:

-- Step 3: Allow user to access the public schema's structure
GRANT USAGE ON SCHEMA public TO report_viewer;

4. Grant Select Privileges on Tables

Code:

-- Step 4: Grant SELECT privilege on all tables in the schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_viewer;

5. Set Default Privileges for Future Tables

Code:

-- Step 5: Ensure the user has read access to new tables created in the schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO report_viewer;

Important Notes:

• Privileges: Only SELECT privileges are granted to maintain the read-only state.
• Default Privileges: Setting default privileges ensures any new tables created in the schema are also accessible to the read-only user.
• Schema Specific: This example assumes all tables are within the public schema. For other schemas, replace public with the relevant schema name.

All PostgreSQL Questions, Answers, and Code Snippets Collection.