DEV Community

abbazs
abbazs

Posted on • Edited on

4 1

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

Heroku

Built for developers, by developers.

Whether you're building a simple prototype or a business-critical product, Heroku's fully-managed platform gives you the simplest path to delivering apps quickly — using the tools and languages you already love!

Learn More

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.

Image of DataStax

AI Agents Made Easy with Langflow

Connect models, vector stores, memory and other AI building blocks with the click of a button to build and deploy AI-powered agents.

Get started for free

👋 Kindness is contagious

Engage with a wealth of insights in this thoughtful article, valued within the supportive DEV Community. Coders of every background are welcome to join in and add to our collective wisdom.

A sincere "thank you" often brightens someone’s day. Share your gratitude in the comments below!

On DEV, the act of sharing knowledge eases our journey and fortifies our community ties. Found value in this? A quick thank you to the author can make a significant impact.

Okay