The PostgreSQL 9.3 Documentation has outlined how to alter default permissions. Follow this link to learn more!
Here is an excerpt that demonstrates how one would change the default permissions for a GROUP:
ALTER DEFAULT PRIVILEGES
[ FOR { ROLE | USER } target_role [, ...] ]
[ IN SCHEMA schema_name [, ...] ]
abbreviated_grant_or_revoke
where abbreviated_grant_or_revoke is:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
You may also benefit from learning more about ROLES (follow link to documentation here). That would make it easier if you ever needed to re-assign permissions to other people to.
It is frequently convenient to group users together to ease management of privileges: that way, privileges can be granted to, or revoked from, a group as a whole. In PostgreSQL this is done by creating a role that represents the group, and then granting membership in the group role to individual user roles.
A good place to do some practice/tutorial work on permissions, groups, roles, etc. is a site called Tutorials Point. They've got examples that will help you work through setting up permissions.
The documentation about ALTER DEFAULT PRIVILEGES
tells you a possible reason - it is not that clearly described, though.
Let's see, what is said:
You can change default privileges only for objects that will be
created by yourself or by roles that you are a member of.
This means that the default privileges defined by this statement applies only to objects that are created by you (or the role you are a member of). Let's see this in action!
alice
's schema
First, we are logged in as alice
. Then, in a newly created schema, we create a table and grant some rights to bob
:
SELECT current_user;
current_user
──────────────
alice
SHOW search_path ;
search_path
──────────────
test, public
CREATE SCHEMA alicetest;
ALTER DEFAULT PRIVILEGES
FOR ROLE alice
IN SCHEMA alicetest
GRANT ALL ON TABLES TO alice;
GRANT SELECT ON alicetest.a TO bob;
-- this I do only for showing the privileges -
-- the owner has by default ALL and is not shown by \dp
\dp alicetest.a
Access privileges
Schema │ Name │ Type │ Access privileges │ Column access privileges
───────────┼──────┼───────┼─────────────────────┼──────────────────────────
alicetest │ a │ table │ alice=arwdDxt/alice↵│
│ │ │ bob=r/alice │
alice
now has all rights on her table, as expected.
bob
' table in the same schema
Now, after obtaining access to this schema, bob
tries to create a table:
SELECT current_user;
current_user
──────────────
bob
CREATE TABLE alicetest.b (id integer);
GRANT SELECT ON alicetest.b TO alice;
\dp alicetest.b
Access privileges
Schema │ Name │ Type │ Access privileges │ Column access privileges
───────────┼──────┼───────┼───────────────────┼──────────────────────────
alicetest │ b │ table │ bob=arwdDxt/bob ↵│
│ │ │ alice=r/bob │
As you can see, despite creating the table in alice
's schema where she set the default privileges, bob
's table doesn't have all those permissions. This happens because alice
is not a member of bob
.
Let's check this membership thing, too, and try to define default privileges by alice
again, this time for another role:
ALTER DEFAULT PRIVILEGES
FOR ROLE charlie
IN SCHEMA alicetest
GRANT ALL ON TABLES TO alice;
ERROR: must be member of role "charlie"
So, some mighty enough user grants her a membership in charlie
, then she tries again, with success:
ALTER DEFAULT PRIVILEGES
FOR ROLE charlie
IN SCHEMA alicetest
GRANT ALL ON TABLES TO alice;
ALTER DEFAULT PRIVILEGES
charlie
's round
Then charlie
creates a new table:
CREATE TABLE alicetest.c (id integer);
And the privileges:
\dp alicetest.c
Access privileges
Schema │ Name │ Type │ Access privileges │ Column access privileges
───────────┼──────┼───────┼─────────────────────────┼──────────────────────────
alicetest │ c │ table │ charlie=arwdDxt/charlie↵│
│ │ │ alice=arwdDxt/charlie │
As you see, alice
, as a member of charlie
, gets her access to this table.
To answer your question,
I guess you defined the default privileges for yourself (alice
in the example), but you developers act as a bunch of bob
s here, not getting the necessary privileges. One way to get around this (as we do it at work) is to do a
SET ROLE TO schema_owner;
every time before creating a new object in the schema. This should be a role that all developers are a member of (otherwise you'd get an error).
NOTE that \dp
is a psql
command.
Best Answer
No postgresql does not inherit permissions in this fashion, Pretty much anything using the Create command sets the owner to the users that created it.
Use the GRANT command
Or after creating the tables set the Owner with Alter command