I am trying to create a read only user but I having some issues because this user can also create tables and some other staff with permission. I want to create a read only user:
CREATE USER readonly WITH PASSWORD 'readonlypassword';
GRANT CONNECT ON DATABASE database_name TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public to readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
Then I execute this query in order to check my new readonly user has only read access:
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='any_table_name';
grantee | privilege_type
----------+----------------
admin_user | INSERT
admin_user | SELECT
admin_user | UPDATE
admin_user | DELETE
admin_user | TRUNCATE
admin_user | REFERENCES
admin_user | TRIGGER
readonly | SELECT
(8 rows)
Everything looks ok but I am able to create tables...why? The grant permission tables said just SELECT
.
What do I need to do in order to just enable the read only permission?