To create a user in PostgreSQL and give them read-only permissions to a specific database, you can follow these steps.
This process involves using SQL commands within your PostgreSQL environment
1

Log into your PostgreSQL server

You can do this by using a terminal or an SQL client, connecting with a user that has sufficient privileges to create other users and manage permissions, such as the postgres superuser.
sql
psql -U postgres
2

Create the user

Use the CREATE ROLE or CREATE USER command to create a new user. CREATE USER is effectively the same as CREATE ROLE but with the LOGIN ability enabled by default.
sql
CREATE USER readonly_user WITH PASSWORD 'secure_password';
Replace readonly_user with your desired username and secure_password with a strong password.
3

Grant connect permissions to the database

You need to grant the new user permission to connect to the specific database.
sql
GRANT CONNECT ON DATABASE target_database TO readonly_user;
Replace target_database with the name of the database to which you want to grant access.
4

Grant usage permissions on schemas

Typically, you’d want the user to read data from specific schemas (commonly the public schema).
sql
GRANT USAGE ON SCHEMA public TO readonly_user;
5

Grant select permissions on tables and views

Finally, grant the SELECT permission on all tables and views within the schema. To automatically apply this to new tables as well, you can set up default privileges.
sql
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
This gives the readonly_user the ability to read data from all existing and future tables in the public schema of target_database.
6

Exit PostgreSQL session

After you’re done, you can exit the PostgreSQL command line by typing.
sql
\q