DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’» is a community of 967,911 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
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 Author

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

🌚 Browsing with dark mode makes you a better developer.

It's a scientific fact.