0

My question is about database permissions and rights in Postgres. How can I create reader rights by default when objects are uploaded into PGadmin 4 schemata?

I created the following schema in Postgres called test_schema:

CREATE SCHEMA test_schema
    AUTHORIZATION jessica_admin;

GRANT ALL ON SCHEMA test_schema TO group_members;

ALTER DEFAULT PRIVILEGES IN SCHEMA test_schema
GRANT SELECT ON TABLES TO group_members


CREATE TABLE test_schema.test_table
(
)

WITH (
    OIDS = FALSE
);

ALTER TABLE test_schema.test_table
    OWNER to jessica_admin;
GRANT SELECT ON TABLE test_schema.test_table TO group_members

Now the question is the following: how can I include the following code

ALTER TABLE test_schema.test_table
    OWNER to jessica_admin;
GRANT SELECT ON TABLE test_schema.test_table TO group_members

into the default settings of PgAdmin4? I don't want to do this setting everytime when I add new shapefiles into the scheme, but I want that user (here called group_members) can read the shapefile in PostgreSQL and add this simply into the GIS Interface.

  • What exactly do you mean with "objects are uploaded into PGAdmin"? – a_horse_with_no_name Aug 7 at 6:57
  • The alter default privileges should do exactly what you want. You just need to make sure you connect with a role (user) that is a member of the group_members role then the user will be able to select from any table in that schema. – a_horse_with_no_name Aug 7 at 6:58
  • I created a schemata in the Postgres (PGadmin environment). Objects are for instance shapefiles or tables. I added those objects to the schemata . – JessicaRjes Aug 7 at 7:00
  • Yes, and the default privileges will allow (read) access to all tables in the schema test_schema. If you have other "objects" in that schema (views, sequences, ...) , the default privileges you defined do not cover access to those. – a_horse_with_no_name Aug 7 at 7:02
  • in my case the following code is essential so that other users (in group_members) can read the object, If I don't add the following code sql ALTER TABLE test_schema.test_table OWNER to jessica_admin; GRANT SELECT ON TABLE test_schema.test_table TO group_members then other user cannot read the object. – JessicaRjes Aug 7 at 7:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.