I have a "production" user and database on which I want to give right access on all the users belonging to a production group.
Unfortunately, I don't succeed, keeping the access denied error.
Can you have a look at the example script below and tell me what I missed (production user/database is toto and production group is toto_group)?
Some comments:
- The use case is: The data loading is already in production. Developpers (
titi, etc.) now only have to read the data, and we want them to read it from the production database in order to avoid having to duplicate everything. - I've initially tried on Linux, but I did the test script on windows. Same results.
- I'm using postgresql 9.5
- I have also tried to grant the access to the tables instead of the schema. Same results.
Thanks a lot.
\c postgres postgres DROP DATABASE IF EXISTS toto; DROP TABLESPACE IF EXISTS toto_ts; DROP ROLE IF EXISTS toto; DROP DATABASE IF EXISTS titi; DROP TABLESPACE IF EXISTS titi_ts; DROP ROLE IF EXISTS titi; DROP ROLE IF EXISTS toto_group; CREATE ROLE toto_group NOLOGIN; CREATE ROLE toto WITH PASSWORD 'toto' LOGIN; CREATE TABLESPACE toto_ts OWNER toto LOCATION 'd:/pg_ts/toto'; CREATE DATABASE toto TABLESPACE=toto_ts TEMPLATE=template0 OWNER=toto; GRANT toto_group TO toto; CREATE ROLE titi WITH PASSWORD 'titi' LOGIN; CREATE TABLESPACE titi_ts OWNER titi LOCATION 'd:/pg_ts/titi'; CREATE DATABASE titi TABLESPACE=titi_ts TEMPLATE=template0 OWNER=titi; GRANT toto_group TO titi; COMMIT; \c toto DROP SCHEMA public; CREATE SCHEMA s_test; SET SCHEMA 's_test'; GRANT SELECT ON ALL TABLES IN SCHEMA s_test TO toto_group; CREATE TABLE t_test (id INTEGER); COMMIT; \c titi DROP SCHEMA public; CREATE SCHEMA s_test; SET SCHEMA 's_test'; GRANT SELECT ON ALL TABLES IN SCHEMA s_test TO toto_group; CREATE TABLE t_test (id INTEGER); COMMIT; 
