DEV Community

abbazs
abbazs

Posted on • Updated on

How to access postgresql database as readonly?

Want to access a psql database as readonly?
Following are the steps:

  1. Login to the database:

    psql -d yourdbname

  2. Create a user:

    CREATE USER username WITH ENCRPTED PASSWORD 'yourpassword';

  3. Assign privilege select to the user:

    GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;

  4. 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
    Alt Text

  5. Restart postgres

    Restart using command systemctl restart postgresql.service
    Using pg_lsclusters command check if service started OK.Alt Text

  6. Check if new user can login:

    Login to psql using command psql -d yourdbname -U username

Top comments (2)

Collapse
 
kostyanius profile image
Kostyantyn Khomko

hello. is there any chance to get the same result without modifing pg_hba and without restart?

Collapse
 
abbazs profile image
abbazs

It shall be possible by controlling the use rights in psql itself.