How to create a read-only user in PostgreSQL

If we only grant CONNECT to a database, the user can connect but has no other privileges. We have to grant USAGE on namespaces (schemas) and SELECT on tables and views individually like so:

1. Create User.

create user r2admin with password 'admin@123';

2. Grant permissions(read-only) to the user created above:

GRANT CONNECT ON DATABASE r2schools TO r2admin;
-- This assumes you're actually connected to r2schools..
GRANT USAGE ON SCHEMA public TO r2admin;
GRANT SELECT ON t_test TO r2admin;

How to create a read-only user in PostgreSQL

Read-only user have Connect, Usage and select permissions in PostgreSQL Server.

Related Posts:

  1. How to find currently logged in users in PostgreSQL
  2. How to find the number of active database connections in PostgreSQL
  3. How to rename PostgreSQL User
  4. Set PostgreSQL concurrent connection limit