postgreSQL readonly user is not working

648 views Asked by At

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?

0

There are 0 answers