w3resource

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

-- 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

-- 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

-- 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

-- 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

-- 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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/PostgreSQL/snippets/create-read-only-user-postgresql.php