Postgresql – Automatically give grants to particular users PostgreSQL

It is impossible to do this using permissions only .

The only way is to create a stored procedure as described here

If you want to avoid stored procedures, a workaround is: GRANT ALL PRIVILEGES ONtestuser_%. * TO 'testuser'@'%'; (as suggested here); however, this has the problem that the users must then be very careful in naming their databases.

For example if user aaa creates database bbb_xyz, it can then be accessed exclusively by user bbb but not by user aaa.

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.