Thread

1. default privileges are npt working

   Atul Kumar <akumar14871@gmail.com> — 2024-08-30T11:14:12Z

   Hi,
   
   I have a postgres instance running on version 15 in centos7.
   
   I have created a custom database and revoked all public privileges from
   that database.
   
   Then I have created a custom schema in that custom database.
   
   Now I have created one writer *user* and one reader *user *by postgres
   superuser and then granted connect privileges on the database.
   
   Then I have given all privileges of schema level and table level to the
   writer *user *so that it can create tables and insert data in the tables in
   that schema.
   
   And for reader *user * I have granted usage only privileges on schema level
   and select privileges on table level so that it can only read the data of
   tables.
   
   Then I granted default "select" privileges to reader *user *to read data of
   all tables created by writer *user* using below command:
   
   alter default privileges in schema <custom schema> grant select on tables
   to <reader user>.
   
   but when I am connected to the reader user I am not able to read the data
   inserted by the writer *user* and getting permission denied error.
   
   I can only see the list of tables created by the writer user, not the data.
   
   Am I missing something here? Please let me know.
   
   *My Goal: To read the data by reader user inserted by writer user.*
   
   
   Regards.
   

2. Re: default privileges are npt working

   Muhammad Usman Khan <usman.k@bitnine.net> — 2024-08-30T11:23:30Z

   Hi.
   I think the ALTER DEFAULT PRIVILEGES command affects only tables that are
   created after the command is executed. Tables created by the writer user
   before you executed the ALTER DEFAULT PRIVILEGES command would not
   automatically have select privileges granted to the reader user.  You can
   try by explicitly granting select privileges on the existing tables to the
   reader user.
   
   
   
   On Fri, 30 Aug 2024 at 16:14, Atul Kumar <akumar14871@gmail.com> wrote:
   
   > Hi,
   >
   > I have a postgres instance running on version 15 in centos7.
   >
   > I have created a custom database and revoked all public privileges from
   > that database.
   >
   > Then I have created a custom schema in that custom database.
   >
   > Now I have created one writer *user* and one reader *user *by postgres
   > superuser and then granted connect privileges on the database.
   >
   > Then I have given all privileges of schema level and table level to the
   > writer *user *so that it can create tables and insert data in the tables
   > in that schema.
   >
   > And for reader *user * I have granted usage only privileges on schema
   > level and select privileges on table level so that it can only read the
   > data of tables.
   >
   > Then I granted default "select" privileges to reader *user *to read data
   > of all tables created by writer *user* using below command:
   >
   > alter default privileges in schema <custom schema> grant select on tables
   > to <reader user>.
   >
   > but when I am connected to the reader user I am not able to read the data
   > inserted by the writer *user* and getting permission denied error.
   >
   > I can only see the list of tables created by the writer user, not the data.
   >
   > Am I missing something here? Please let me know.
   >
   > *My Goal: To read the data by reader user inserted by writer user.*
   >
   >
   > Regards.
   >
   

3. Re: default privileges are npt working

   Christoph Moench-Tegeder <cmt@burggraben.net> — 2024-08-30T12:49:01Z

   ## Atul Kumar (akumar14871@gmail.com):
   
   > Then I granted default "select" privileges to reader *user *to read data of
   > all tables created by writer *user* using below command:
   > 
   > alter default privileges in schema <custom schema> grant select on tables
   > to <reader user>.
   
   "ALTER DEFAULT PRIVILEGES allows you to set the privileges that
   will be applied to objects created in the future. (It does not affect
   privileges assigned to already-existing objects.)"
   That's the first two sentences from
   https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html
   If you want to grant/revoke access on existing objects, use plain
   old GRANT/REVOKE: https://www.postgresql.org/docs/current/sql-grant.html
   
   Regards,
   Christoph
   
   -- 
   Spare Space.
   
   
   
   

4. Re: default privileges are npt working

   David G. Johnston <david.g.johnston@gmail.com> — 2024-08-30T13:48:30Z

   On Friday, August 30, 2024, Atul Kumar <akumar14871@gmail.com> wrote:
   >
   >
   > I have a postgres instance running on version 15 in centos7.
   >
   > I have created a custom database and revoked all public privileges from
   > that database.
   >
   
   Would be better to provide the actual psql script of what you’ve done
   instead of writing it out in prose. That way the question of whether you’ve
   done something wrong or not is directly and accurately answerable
   
   David J.