Rational
When you create a user in postgresql, even with only select only permission, your user has a lot more privileges.
Solution
Connect with root user to the desired database you will be giving permissions for:
mydb=# psql -h 10.10.10.10 -U postgres mydb
Now lets create the user:
mydb=# create user readonly with encrypted password 'readonly';
Revoke everything so we can explicitly allow what we need:
mydb=# revoke all on schema public from readonly;
Grant only what you need.
First give connection privilege to the actual database:
mydb=# GRANT CONNECT ON DATABASE mydb TO readonly;
Then grant usage and select:
mydb=# GRANT USAGE,SELECT ON SCHEMA public TO readonly;
mydb=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
If you want to grant access to the new tables in the future automatically, you have to alter default:
mydb=# ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly;
Fin
Thats all!