[nix-shell:~]$ psql postgres://postgres:pg_test_init_privs@localhost:5555 <<EOF CREATE ROLE test_role; GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO test_role; CREATE EXTENSION pg_stat_statements; DROP ROLE test_role; EOF
CREATE ROLE GRANT ALTER DEFAULT PRIVILEGES CREATE EXTENSION ERROR: role "test_role" cannot be dropped because some objects depend on it DETAIL: privileges for default privileges on new relations belonging to role postgres in schema public privileges for view pg_stat_statements_info privileges for view pg_stat_statements
Is there some difference in the configuration that I'm not accounting for?
First time trying to configure a PG cluster by the book, I want to create a
role with read permissions on all current and future tables in the current
db. It looks smth like this
CREATE ROLE test_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO test_role;
I've been trying out different scenarios for the future, and currently having
a problem when trying to remove "test_role" after adding an extension.
Hm, I have checked your example, it works as expected:
postgres@postgres(16.9)=# CREATE ROLE test_role; CREATE ROLE postgres@postgres(16.9)=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role; GRANT postgres@postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO test_role; ALTER DEFAULT PRIVILEGES postgres@postgres(16.9)=# CREATE EXTENSION pg_stat_statements; CREATE EXTENSION
postgres@postgres(16.9)=# REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM test_role; REVOKE postgres@postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM test_role; ALTER DEFAULT PRIVILEGES postgres@postgres(16.9)=# DROP ROLE test_role; DROP ROLE postgres@postgres(16.9)=# DROP EXTENSION pg_stat_statements; DROP EXTENSION
In any case, since v14 you can use the predefined role pg_read_all_data.
