One of the most common (or perhaps even the most common) question people have regarding PostgreSQL, is how to change privileges for all tables.
Usually they want something like MySQL's:
GRANT ... ON DATABASE.* TO ...
Which is interesting as it is actually 2 distinct features in one:
- grant privileges on all existing tables
- automatically grant privileges on all tables that will be created in this database in the future
Now, thanks to 2 new patches – we have this possibility in PostgreSQL.
On 5th of October Tom Lane committed patch by Petr Jelinek:
Log Message: ----------- CREATE an ALTER DEFAULT PRIVILEGES command, which allows users TO adjust the privileges that will be applied TO subsequently-created objects. Such adjustments are always per owning ROLE, AND can be restricted TO objects created IN particular schemas too. A notable benefit IS that users can override the traditional DEFAULT privilege settings, eg, the PUBLIC EXECUTE privilege traditionally GRANTED BY DEFAULT FOR functions. Petr Jelinek
Then, on 12th, there was another patch, also by Petr Jelinek, and also committed by Tom Lane, which added:
Log Message: ----------- Support GRANT/REVOKE ON ALL TABLES/SEQUENCES/FUNCTIONS IN SCHEMA. Petr Jelinek
( Both of these were later on 12th modified to work with plural word forms ).
So, how does that work?
Let's start with the 2nd patch – GRANT/REVOKE on all.
To test it, we will need some tables:
# CREATE TABLE test1 (x int4); CREATE TABLE # CREATE TABLE test2 (x int4); CREATE TABLE # CREATE TABLE test3 (x int4); CREATE TABLE
Let's see how it looks:
# \z
Access privileges
Schema │ Name │ Type │ Access privileges │ Column access privileges
────────┼───────┼───────┼───────────────────┼──────────────────────────
public │ test1 │ table │ │
public │ test2 │ table │ │
public │ test3 │ table │ │
(3 rows)OK. Now, let's grant some things:
# GRANT ALL ON ALL TABLES IN schema public TO postgres; GRANT # GRANT ALL ON ALL TABLES IN schema public TO depesz; GRANT # REVOKE ALL ON TABLE test2 FROM postgres; REVOKE # \z Access privileges Schema │ Name │ TYPE │ Access privileges │ COLUMN access privileges ────────┼───────┼───────┼─────────────────────────┼────────────────────────── public │ test1 │ TABLE │ depesz=arwdDxt/depesz │ ╷ ╷ ╎ postgres=arwdDxt/depesz ╷ public │ test2 │ TABLE │ depesz=arwdDxt/depesz │ public │ test3 │ TABLE │ depesz=arwdDxt/depesz │ ╷ ╷ ╎ postgres=arwdDxt/depesz ╷ (3 ROWS)
Pretty cool. Of course, this doesn't change what will happen with new tables:
# \z
Access privileges
Schema │ Name │ Type │ Access privileges │ Column access privileges
────────┼───────┼───────┼─────────────────────────┼──────────────────────────
public │ test1 │ table │ depesz=arwdDxt/depesz │
╷ ╷ ╎ postgres=arwdDxt/depesz ╷
public │ test2 │ table │ depesz=arwdDxt/depesz │
public │ test3 │ table │ depesz=arwdDxt/depesz │
╷ ╷ ╎ postgres=arwdDxt/depesz ╷
public │ test4 │ table │ │
(4 rows)As you can see “Access privileges" for test4 are empty.
But here, we can use the second (actually first if we'll check if chronologically) feature – default privileges:
# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO depesz, postgres; ALTER DEFAULT PRIVILEGES # CREATE TABLE test5 (x int4); CREATE TABLE # \z Access privileges Schema │ Name │ TYPE │ Access privileges │ COLUMN access privileges ────────┼───────┼───────┼─────────────────────────┼────────────────────────── public │ test1 │ TABLE │ depesz=arwdDxt/depesz │ ╷ ╷ ╎ postgres=arwdDxt/depesz ╷ public │ test2 │ TABLE │ depesz=arwdDxt/depesz │ public │ test3 │ TABLE │ depesz=arwdDxt/depesz │ ╷ ╷ ╎ postgres=arwdDxt/depesz ╷ public │ test4 │ TABLE │ │ public │ test5 │ TABLE │ depesz=arwdDxt/depesz │ ╷ ╷ ╎ postgres=arwdDxt/depesz ╷ (5 ROWS)
Sweet 🙂
All subsequent ALTER DEFAULT PRIVILEGES modify current default privileges, so instead of writing ALTER DEFAULT PRIVILEGES … GRANT … TO depesz, postgres; I could:
# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO depesz;
and later on:
# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO postgres;
Of course current default privileges can be viewed from psql itself, or from any application with simple sql:
# \ddp
DEFAULT access privileges
Owner │ Schema │ TYPE │ Access privileges
────────┼────────┼───────┼─────────────────────────
depesz │ public │ TABLE │ depesz=arwdDxt/depesz
╷ ╷ ╎ postgres=arwdDxt/depesz
(1 ROW)
# SELECT * FROM pg_default_acl ;
defaclrole │ defaclnamespace │ defaclobjtype │ defaclacl
────────────┼─────────────────┼───────────────┼─────────────────────────────────────────────────
16385 │ 2200 │ r │ {depesz=arwdDxt/depesz,postgres=arwdDxt/depesz}
(1 ROW)Of course you can grant/revoke privileges not only to tables, but also to functions or sequences. Views as I see are treated like tables:
# \z
Access privileges
Schema │ Name │ Type │ Access privileges │ Column access privileges
────────┼───────┼───────┼─────────────────────────┼──────────────────────────
public │ test1 │ table │ depesz=arwdDxt/depesz │
╷ ╷ ╎ postgres=arwdDxt/depesz ╷
public │ test2 │ table │ depesz=arwdDxt/depesz │
public │ test3 │ table │ depesz=arwdDxt/depesz │
╷ ╷ ╎ postgres=arwdDxt/depesz ╷
public │ test4 │ table │ │
public │ test5 │ table │ depesz=arwdDxt/depesz │
╷ ╷ ╎ postgres=arwdDxt/depesz ╷
public │ v1 │ view │ depesz=arwdDxt/depesz │
╷ ╷ ╎ postgres=arwdDxt/depesz ╷
(6 rows)Finally a good way to handle privileges in non-trivial databases 🙂
I attempted to write it, twice – both times, refused by Tom. So finally he took the matter into his own hands. good man,
was anything added table wise? will this have any negative performance impacts? also aren’t your first examples supposed to include more than the words GRANT an REVOKE?
@Caleb:
I don’t think there will be any performance penalty – after all, it just grant some privileges.
As for examples – not sure what you mean. i can see the sqls. which box you mean?
@gregj: Actually I wrote it, Tom commited it (with some polishing). And BTW I had to write it twice too 😉
@Caleb: Nope, that table is only used to get privileges template when you are creating new table (sequence, view, …) so no performace penaly for queries.
@depesz: that code under “OK. Now, let’s grant some things:” contains GRANT, GRANT, REVOKE with nothing after them.
The GRANT,GRANT,REVOKE you are seeing is the return value from the command directly above.
As shown below
# grant all on all tables in schema public to postgres;
GRANT
@PJMODOS: can’t replicate. I tried with 3 themes, and in all the commands are directly above “GRANT/REVOKE” replies.
could you send me screenshot, browser information, and exact source of page that you had while displaying? Including all external files – like styles.
6th box
# grant all on all tables in schema public to postgres;
GRANT
# grant all on all tables in schema public to depesz;
GRANT
# revoke all on table test2 from postgres;
REVOKE
# \z
Access privileges
Schema │ Name │ Type │ Access privileges │ Column access privileges
────────┼───────┼───────┼─────────────────────────┼──────────────────────────
public │ test1 │ table │ depesz=arwdDxt/depesz │
╷ ╷ ╎ postgres=arwdDxt/depesz ╷
public │ test2 │ table │ depesz=arwdDxt/depesz │
public │ test3 │ table │ depesz=arwdDxt/depesz │
╷ ╷ ╎ postgres=arwdDxt/depesz ╷
(3 rows)
I think the problem is that people are seeing:
# grant all on all tables in schema public to postgres;
as a comment because of the leading #, not as the statement being run.
ah you’re right… yeah it is kinda misleading as # is usually a comment and SQL is usually caps. plus in other box’s the sql is capped making it inconsistent.
@Caleb:
# is just my prompt (actually it’s 2nd line of prompt in psql, which consits of “%# ” only.
as for upper/lower case – sorry, i type those commands by hand, so they usually are mixed case.