How to create a read-only PostgreSQL user

create-read-only-postgres-user.md

How to create a read-only PostgreSQL user

1. Create a new user in PostgreSQL

   CREATE USER <username> WITH PASSWORD '<password>';
   

2. GRANT CONNECT access

   GRANT CONNECT ON DATABASE <database_name> TO <username>;
   

3. GRANT USAGE on schema

   GRANT USAGE ON SCHEMA <schema_name> TO <username>;
   

4. GRANT SELECT access using any of the options below

   • Grant SELECT for a specific table:

     GRANT SELECT ON <table_name> TO <username>;
     

   • Grant SELECT for multiple tables

     GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <username>;
     

   • To grant access to the new tables in the future automatically, the default needs to be altered

     ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO <username>;