![]() We’ll do this by adding retention policies to certain relations after doing a bit of analysis. This is part of a series about PostgreSQL & TimescaleDB – in the next one I will show how to deal with a TimescaleDB/PostgreSQL instance that is running out of disk space. ![]() Here, at NTC Hosting, we use the stable and proven phpPgAdmin, which is very similar to the phpMyAdmin interface, used for managing MySQL databases. There are several web based interfaces for PostgreSQL - pSQL, pgAdmin, phpPgAdmin. Do you know other reasons for which it may be valuable to know about schemas and their nuances? The first method is by using a web based interface. Create a new user: CREATE USER username WITH PASSWORD yourpassword 2. I was relatively ignorant about the role of schemas before, but here we can see it is the mechanism by which future table privileges can be indicated. How to add a Read-Only User to a PostgreSQL Database 1. Databases contain schemas and schemas contain tables. GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess ĪLTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess ĬREATE USER tomek WITH PASSWORD 'secret' īy understanding this I was able to upgrade my mental model about PostgreSQL to the following: the world must be composed of users and groups called roles which have certain privilege to databases, schemas, and tables (also known as relations). ![]() GRANT USAGE ON SCHEMA public TO readaccess Rather than post my own notes, I prefer the comments on this gist from Tomek, which I will archive the main gist here now and link to the GitHub hosted gist down below which is worth a look for the excellent follow-up discussions. I realized that adding a read-only user forced a bit more understanding than what’s necessary to use PostgreSQL from an application or conduct performance analysis, the typical developer tasks, so I would not be surprised if many devs, like me, haven’t bothered, so I really recommend doing it. In Grafana users can write arbitrary SQL directly to the DB so for safety reasons a readonly user makes sense. I needed to do this in order to make a readonly user to consume TimescaleDB from Grafana.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |