Granting Read-Only Access in PostgreSQL: A Step-by-Step Guide

1. Create the User:

   • Use the CREATE ROLE command to create a new user, often called a role in PostgreSQL. For example:

     CREATE ROLE readonly_user WITH LOGIN PASSWORD 'your_password';
     

2. Grant Connection Permissions:

3. Grant Schema Permissions:

4. • GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
     

5. Optional: Set Default Privileges (For New Tables):

   • The above steps grant access to existing tables. To automatically grant read access to new tables created in the 'public' schema, use:

     ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
     

CREATE ROLE readonly_user WITH LOGIN PASSWORD 'your_strong_password';
GRANT CONNECT ON DATABASE my_database TO readonly_user;

Granting Schema and Read Permissions:

\c my_database  -- Connect to the specific database

GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_user;

Setting Default Privileges (Optional):

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

Explanation:

• Replace 'your_strong_password' with a secure password for the user.
• Replace my_database with the actual name of the database you want to grant access to.
• The \c my_database command connects you to the specific database for granting schema and read permissions.

1. Granting SELECT on Specific Tables:

   Instead of granting access to all tables in a schema, you can be more granular and grant SELECT permission on specific tables the user needs to access. This might be useful if you have a large database and want to restrict access to sensitive data.

   GRANT SELECT ON TABLE table1, table2 TO readonly_user;
   

2. Roles with Limited Permissions:

   PostgreSQL allows creating custom roles with pre-defined permissions. You can create a role specifically for read-only access and assign it to users. This approach involves some additional setup but can be helpful for managing user permissions more efficiently, especially if you have many read-only users.

   Here's a basic example:

   CREATE ROLE read_only_role;
   
   GRANT CONNECT ON DATABASE my_database TO read_only_role;
   GRANT USAGE ON SCHEMA public TO read_only_role;
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_role;
   GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only_role;
   
   GRANT read_only_role TO readonly_user;
   

   This first creates a role named read_only_role with the necessary permissions. Then, the readonly_user is assigned this role, inheriting its permissions.