Thread: How to avoid having to run the GRANT command for newly added tables?
Hello - I have a Postgres 14 server. I created a database and then gave all privileges to tables in the database to a user as shown below:
grant connect on database testdb to test_user;
GRANT all ON ALL TABLES IN SCHEMA "public" TO test_user;
I now verified that test_user is able to access an EXISTING table:
select count(*) from test;
count
-------
0
(1 row)
count
-------
0
(1 row)
However when the postgres superuser creates a NEW table:
create table test2( id integer primary key);
CREATE TABLE
CREATE TABLE
the test_user is not able to access it:
select count(*) from test2;
ERROR: permission denied for table test2
ERROR: permission denied for table test2
This is really inconvenient for me and I'd like to avoid having to run the GRANT command again and again after creating new tables. Is there any way to accomplish what I want? Thanks.
Re: How to avoid having to run the GRANT command for newly added tables?
From
Christophe Pettus
Date:
> On Nov 23, 2022, at 12:06, Siddharth Jain <siddhsql@gmail.com> wrote:
> Is there any way to accomplish what I want? Thanks.
There is:
https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html
Thanks Christophe. it works.
On Wed, Nov 23, 2022 at 12:08 PM Christophe Pettus <xof@thebuild.com> wrote:
> On Nov 23, 2022, at 12:06, Siddharth Jain <siddhsql@gmail.com> wrote:
> Is there any way to accomplish what I want? Thanks.
There is:
https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html