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

   • Replace 'readonly_user' with your desired username and 'your_password' with a strong password.

2. Grant Connection Permissions:

   • The user needs permission to connect to the database. Use the GRANT command to allow the user to connect to a specific database. For example:

     GRANT CONNECT ON DATABASE database_name TO readonly_user;
     

     Replace 'database_name' with the actual name of the database the user should access.

3. Grant Schema Permissions:

   • By default, users can only access objects in the 'public' schema. Use GRANT USAGE to allow the user to access this schema.

     GRANT USAGE ON SCHEMA public TO readonly_user;
     

4. • Grant the user permission to select data from tables. You can grant access to individual tables or all tables within a schema. Here's granting access to all tables in the 'public' schema:

     GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
     

   • Additionally, grant access to sequences if your tables use them for auto-incrementing IDs. You can use a similar GRANT SELECT command for sequences.

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;
     

   • This ensures new tables are also accessible by the read-only user.

Creating the User and Granting Connection:

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;
   

   Replace table1 and table2 with the actual names of the tables you want to grant access to.

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.