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?
Advertisement
Answer
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 futrue and backup
is the user that will be able to read from new tables created by webapp
.