78

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?

Andrey Chernih
  • 3,513
  • 2
  • 27
  • 27

3 Answers3

87

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.

Adam Rosenthal
  • 458
  • 5
  • 9
Andrey Chernih
  • 3,513
  • 2
  • 27
  • 27
  • 1
    Relevant documentation: http://www.postgresql.org/docs/9.0/static/sql-alterdefaultprivileges.html – aaaaaa Mar 15 '15 at 19:23
  • 34
    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;` – Mat Schaffer Mar 30 '15 at 08:02
  • 9
    It's confusing to me how the limits of alter default privileges are documented. Am I correct that this answer does not actually work if backup is not a member of the role that owns the table? It's not working for me when I try to `alter default privileges in schema public grant SELECT on tables to backup`, when my backup user is not an owner or superuser and has no other permissions. – danny Feb 24 '16 at 18:17
  • 1
    I have a superuser and a read-only. This doesn't work for me when I add a new table and then try to SELECT from the read-only. I have to re-run the GRANT SELECT ON... statement every time I add a table. – JP Duffy Mar 04 '20 at 20:58
  • 4
    @danny No, the answer only works as-is if the alter statements are executed as the user/role who creates the tables. That means if they are executed by - say - juser and only juser creates new tables then everything works fine. If other users (e.g. user postgres) create tables in that database/schema then these defaults aren't applied to those tables then. – maxschlepzig Sep 14 '20 at 10:58
  • 1
    @JPDuffy how are your users called? It should work if your super-user executes the ALTER statements and if it's also the same user who creates the tables. Otherwise you have to add a FOR ROLE rule like this `FOR ROLE user_who_creates_the_tables` . You can check the existing default privileges with `\ddp` from `psql` - check there if the `owner` column `/usersuffix` matches the user who creates the tables. – maxschlepzig Sep 14 '20 at 11:03
  • 1
    I have 1. `adminuser`, 2. `tableuser`, 3. `readuser`. What kind of fancy line do I have to write so the permission is given from the `adminuser`, table is created from `tableuser`, and readonly permissions on `readuser`? – Esqarrouth Oct 10 '21 at 14:56
  • @Esqarrouth `ALTER DEFAULT PRIVILEGES FOR USER tableuser IN SCHEMA public GRANT SELECT ON TABLES TO readuser;` – Adam Rosenthal Jul 17 '22 at 14:02
1

If you want the backup user to have access to the future tables of userN, you must run the code below under each userN who creates new tables, because ALTER DEFAULT PRIVILEGES... works only for objects by that user under whom you run ALTER DEFAULT PRIVILEGES...

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO backup;
Denis Davydov
  • 91
  • 1
  • 3
  • 3
    [@Andrey Chernih's answer from eight years ago](https://stackoverflow.com/a/22684537/3025856) already proposes that exact change. – Jeremy Caney Jan 06 '22 at 00:31
  • 1
    For accuracy, @JeremyCaney, I note that the "exact change" proposed by Denis was only added 2 years ago, not the 8 years of the original question, from the comment by Mat Schaffer, albeit still older than Denis's answer, just not by 8 years. – NeilG May 20 '22 at 07:51
  • 2
    @JeremyCaney From that answer, it was not clear to me that you have to run the code under the user who creates the tables, or under postgres but with the "FOR USER" clause. Denis' answer helped me understand it better. – lukfi Jan 11 '23 at 10:08
0

I am trying to create a role, grant connect access to the role and then alter default privileges to keep access for future objects. However, it seems that the below command doesn't work at role level. alter default privileges in schema public grant all on tables to backup;

I followed the below documentation but seems that there are two command do not work for roles. DOC: https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/ First command: GRANT CONNECT ON DATABASE mydatabase TO readonly; Second command: GRANT USAGE ON SCHEMA myschema TO readonly; (For ROLES usually it needs TO ROLE, I also tried TO ROLE but still doesn't work.

arman nemat pasand
  • 21
  • 3