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

Image of Datadog

How to Diagram Your Cloud Architecture

Cloud architecture diagrams provide critical visibility into the resources in your environment and how they’re connected. In our latest eBook, AWS Solution Architects Jason Mimick and James Wenzel walk through best practices on how to build effective and professional diagrams.

Download the Free eBook

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 Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more