New User in PostgreSQL

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.

psql -U postgres

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.

CREATE USER readonly_user WITH PASSWORD 'secure_password';

Replace readonly_user with your desired username and secure_password with a strong password.

You need to grant the new user permission to connect to the specific database.

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.

Typically, you’d want the user to read data from specific schemas (commonly the public schema).

GRANT USAGE ON SCHEMA public TO readonly_user;

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.

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.

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

\q