I am in the process of creating a pre-prod Postgres database, and want to ensure the accessing user has limited access.
I want to setup tables in such a way that the accessing user can't
ALTER objects. Currently the only way I can see it to change the ownership to another user, such as
postgres and then somehow grant only the permissions needed:
ALTER TABLE mytable SET OWNER TO postgres; REVOKE connect ON DATABASE mydatabase FROM PUBLIC; GRANT USAGE, SELECT, UPDATE ON ALL TABLES IN SCHEMA public TO dbuser; GRANT connect ON DATABASE mydatabase TO dbuser;
The issue is when I do the above I am unable to do a simple
SELECT when I connect with the user the grant was applied to. For example:
select * from mytable;
ERROR: permission denied for relation mytable
How to get this right?
Additionally, should I use a named schema other than