PostgreSQL and privileges

Question

How does privileges for new relations in PostgreSQL work?

Steps:

Create DB (from user postgres) and connect to it

CREATE DATABASE test; \c test

Create user site with some privileges

CREATE USER site NOCREATEDB NOINHERIT; GRANT SELECT, UPDATE, INSERT, DELETE, TRUNCATE, REFERENCES ON ALL TABLES IN SCHEMA public TO site; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO site;

Change default privileges for user site

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, UPDATE, INSERT, DELETE, TRUNCATE, REFERENCES ON TABLES TO site;

Create user migration with all privileges

CREATE USER migration NOCREATEDB NOINHERIT; GRANT ALL PRIVILEGES ON DATABASE test TO migration;

Connect to DB from user migration and create table

CREATE TABLE test (id serial);

Connect to DB from user site and select data from created table

SELECT * FROM test; ERROR: permission denied for relation test

But if I create table from user postgres, all work fine!

Why default privileges didn't work in this case? How can I grant permissions for new tables for user site?

Nick Barnes · Accepted Answer · 2015-02-24 17:06:22Z

1

ALTER DEFAULT PRIVILEGES only affects objects created by the user specified in the FOR ROLE clause. If you omit this clause, it only applies to the user running the command (in your case, postgres).

You want ALTER DEFAULT PRIVILEGES FOR USER migration ... instead.

answered Feb 24, 2015 at 17:06

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

vanchester Over a year ago

Thanks! It helps, but after this change user site can change table schemas and drop table: test=> SELECT * FROM test ; id ---- (0 rows) test=> ALTER TABLE test ADD column test_column char(1); ALTER TABLE test=> DROP TABLE test; DROP TABLE

Nick Barnes Over a year ago

ALTER / DROP permissions cannot be granted. They are only available to the table owner (and superusers like postgres, of course). If site is allowed to drop the table, then site is the one who created it. I think you ran some part of your test script from the wrong session.

Collectives™ on Stack Overflow

PostgreSQL and privileges

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related