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.

12 Rarely Used Javascript APIs You Need

Practical examples of some unique Javascript APIs that beautifully demonstrate a practical use-case.