Search code examples
sqlpostgresqlpostgresql-11

Postgresql 2 users with different permissions on a specific schema

i have a postgresql 11 database with the public schema. I have a role user1 that has all privileges on schema public.

i would like to do the following :

  • create a schema "some_schema";
  • grant read only privileges to user1 to "some_schema";
  • create role user2 that has all privilegies on schema "some_schema".

I tried the following :

CREATE USER user2 WITH PASSWORD '***';
CREATE schema "some_schema";
GRANT CONNECT ON DATABASE user1 TO some_schema;
GRANT USAGE ON SCHEMA some_schema TO user1;
GRANT ALL ON SCHEMA some_schema to user2;

ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema GRANT SELECT ON TABLES TO user1;

This does not work cause when i login like this and then i create a "test" table and select, it puts that user1 that "relation 'some_schema.test' doesn't exist" :

create table some_shema.test(id int); \q

psql -U user1; select * from some_schema.test;

result => relation some_schema.test doesn't exist.

I also changed the pg_hba.conf to give access to the new schema to both users and restarted the server. I've tried to read the documentation and many things without success. Maybe it's the way i connect to the database ?

thank you.

Solution

  • Thank you @JGH, that was it. : the user2 had to give himself access to user1 to schema some_shema. What i did :

    psql
CREATE USER user2 WITH PASSWORD '***';
\c my_database
CREATE SCHEMA some_schema AUTHORIZATION user2;
\c my_database user2
create table some_schema.test(id int);
insert into some_schema.t(100);
GRANT USAGE ON SCHEMA some_schema TO user1;
GRANT SELECT ON ALL TABLES IN SCHEMA some_schema TO user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema GRANT SELECT ON TABLES TO user1;

    then :

    \c my_database user1
select * from some_schema.t;

    => shows result 100 :)