— React-Native Programmierer . Patrick Tresp

Archive
postgres

While working with reports for a service running on postgres, you want to make sure, that you can also run statistics on the data from that service. Most of the time, reports where made with same users, als the data was written ( experienced this in multiple companies ).

So lets take a step back and think of what reports are. It is analysis on data that is stored to create something humans can understand. Analysis does not change data, so we actually do not need any rights on that data – except reading.

To prevent a user that creates the analysis to change any data from the service, we will need to create a read-only user in postgres. This is pretty straight-forward, but also very often neglected

# log into postgres
sudo -upostgres psql postgres
 
 
# create user
CREATE ROLE readonly WITH LOGIN ENCRYPTED PASSWORD 'safe-password-here';
 
 
# connect to desired database
 
\connect 'database-name'
 
 
# grant select access and set default permissions
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

With this, the user shall be able to SELECT any of the tables in this schema, but not able to UPDATE any.

Read More