Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

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

Website Navigation

• home
• Front End
• HTML
• CSS
• JavaScript
• HTML5
• Schema.org
• php.js
• Twitter Bootstrap
• Responsive Web Design tutorial
• Zurb Foundation 3 tutorials
• Pure CSS
• HTML5 Canvas
• JavaScript Course
• Icon
• Angular
• Vue
• Jest
• Mocha
• NPM
• Yarn
• Back End
• PHP
• Python
• Java
• Node.js
• Ruby
• C programming
• PHP Composer
• Laravel
• PHPUnit
• Database
• SQL(2003 standard of ANSI)
• MySQL
• PostgreSQL
• SQLite
• NoSQL
• MongoDB
• Oracle
• Redis
• Apollo GraphQL
• API
• Google Plus API
• Youtube API
• Google Maps API
• Flickr API
• Last.fm API
• Twitter REST API
• Data Interchnage
• XML
• JSON
• Ajax
• Exercises
• HTML CSS Exercises
• JavaScript Exercises
• jQuery Exercises
• jQuery-UI Exercises
• CoffeeScript Exercises
• PHP Exercises
• Python Exercises
• C Programming Exercises
• C# Sharp Exercises
• Java Exercises
• SQL Exercises
• Oracle Exercises
• MySQL Exercises
• SQLite Exercises
• PostgreSQL Exercises
• MongoDB Exercises
• Twitter Bootstrap Examples
• Others
• Excel Tutorials
• Useful tools
• Google Docs Forms Templates
• Google Docs Slide Presentations
• Number Conversions
• Linux Tutorials
• Quizzes
• Articles

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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4