Login into postgres terminal first. It is important to specify database name you want to grant permissions to.
su postgres -c "/usr/pgsql-15/bin/psql your_database"Than create new user:
CREATE ROLE readonly_user LOGIN ENCRYPTED PASSWORD 'readonly_password';Then grant permissions to select from all tables in database that have already been created:
GRANT CONNECT ON DATABASE your_database TO readonly_user;GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;That’s all.
If you want read only user to automatically receive select permissions on newly created tables in future, than execute:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;Sources:
- https://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql