Hello,
I'm a postgres newb and am trying to set up some privileges. My request goes
like this.
I have 2 schemas:
- external_data
- internal_data
There are various apps reading and writing in those to schemas.
I have two roles, admin and reader.
The purpose of the admin role is to transfer data from the external schema
to the internal schema.
The reader role can only read the tables from the read schema, and noting
else.
My question is how can I read tables made by admin role with the reader role
(via sql), but not to give the admin privileges to the reader.
I've tried to add them to the same parent role, but then it seems postgres
requires that the table is created by that parent role.
Thank you fo any info.
What I have so far..
-- create db
create database priv_test
-- create schemas
create schema external_data;
create schema internal_data;
-- admin ROLE
DROP OWNED BY admin;
DROP ROLE admin;
CREATE ROLE admin WITH ENCRYPTED PASSWORD '123' NONINHERIT LOGIN;
REVOKE ALL PRIVILEGES ON DATABASE priv_test FROM admin;
GRANT USAGE ON SCHEMA public TO admin;
GRANT ALL ON SCHEMA public TO admin;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO admin;
GRANT USAGE ON SCHEMA internal_data TO admin;
GRANT ALL ON SCHEMA internal_data TO admin;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA internal_data TO admin;
GRANT USAGE ON SCHEMA external_data TO admin;
GRANT ALL ON SCHEMA external_data TO admin;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA external_data TO admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA internal_data GRANT ALL ON TABLES TO
admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA external_data GRANT ALL ON TABLES TO
admin;
-- reader ROLE
DROP OWNED BY reader;
DROP ROLE reader;
CREATE ROLE reader WITH ENCRYPTED PASSWORD '123' NONINHERIT LOGIN;
REVOKE ALL PRIVILEGES ON DATABASE priv_test FROM reader;
GRANT USAGE ON SCHEMA internal_data TO reader ;
GRANT SELECT ON ALL TABLES IN SCHEMA internal_data TO reader;
GRANT USAGE ON SCHEMA internal_data TO reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA internal_data GRANT SELECT ON TABLES TO
reader;
ALTER SCHEMA external_data OWNER TO admin;
ALTER SCHEMA internal_data OWNER TO reader;
--test
SET ROLE admin
create table internal_data.test(id serial primary key, name varchar);
insert into internal_data.test(name) VALUES ('HELLO');
SET ROLE reader;
SELECT * FROM internal_data.test; --cant access
--
View this message in context: http://postgresql.nabble.com/Setting-privileges-for-users-tp5881909.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
