In some cases we want to create a readonly user (for example) for our database access, so let’s walk through the steps etc. to create such a use in Postgresql

Before we start, let’s see how we can list the existing users

SELECT rolname
FROM pg_roles
ORDER BY rolname;

• Create the user

  CREATE USER readonly_user WITH PASSWORD '<password>';
  

• Allow the user to connect to the database using

  GRANT CONNECT ON DATABASE <database-name> TO readonly_user;
  

• Grant usage on the schema

  GRANT USAGE ON SCHEMA <schema-name> TO readonly_user;
  

• Grant SELECT on all existing tables

  GRANT SELECT ON ALL TABLES IN SCHEMA <schema-name> TO readonly_user;
  

• Ensure future tables are also readable

  ALTER DEFAULT PRIVILEGES IN SCHEMA <schema-name>
  GRANT SELECT ON TABLES TO readonly_user;
  

If we want to apply a readonly user to specific tables then we can do the following. Assume we’ve create the user as above and connected to a database as well as granting access on a schema, then…

• Grant access to your tables

  GRANT SELECT ON TABLE <schema-name>.<table-name>TO readonly_user;