Grant privileges on future tables in PostgreSQL?
database, postgresql, sql
Solution
It looks like the solution is to alter default privileges for `backup` user:
alter default privileges in schema public grant all on tables to backup;
alter default privileges in schema public grant all on sequences to backup;
From the comment by Matt Schaffer:
As caveat, the default only applies to the user that executed the `alter` statement. This confused me since I was driving most of my permissions statements from the postgres user but creating tables from an app user. In short, you might need something like this depending on your setup:
ALTER DEFAULT PRIVILEGES FOR USER webapp IN SCHEMA public GRANT SELECT ON SEQUENCES TO backup;
ALTER DEFAULT PRIVILEGES FOR USER webapp IN SCHEMA public GRANT SELECT ON TABLES TO backup;
Where `webapp` is the user that will be creating new tables in the future and `backup` is the user that will be able to read from new tables created by `webapp`.
Problem
I am running PostgreSQL 9.3.1. I have `test` database and `backup` user which is used to backup the database. I have no problems with granting privileges to all current tables, but I have to grant privileges each time the new table is added to schema. ``` createdb test psql test test=# create table foo(); CREATE TABLE test=# grant all on all tables in schema public to backup; GRANT test=# create table bar(); CREATE TABLE psql -U backup test test=> select * from foo; test=> select * from bar; ERROR: permission denied for relation bar ``` Is it possible to grant access to tables which will be created in future without making user owner of the table?