Want to access a psql database as readonly?
Following are the steps:
-
Login to the database:
psql -d yourdbname
-
Create a user:
CREATE USER username WITH ENCRPTED PASSWORD 'yourpassword';
-
Assign privilege select to the user:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;
-
Update
pg_hba.conf
file:Open
pg_hba.conf
file, it shall be available in /etc/postgres//main
Add lines for new user as shown below
-
Restart postgres
Restart using command
systemctl restart postgresql.service
Usingpg_lsclusters
command check if service started OK. -
Check if new user can login:
Login to psql using command
psql -d yourdbname -U username
Top comments (2)
hello. is there any chance to get the same result without modifing pg_hba and without restart?
It shall be possible by controlling the use rights in psql itself.