Create postgres schema in db with multi user in AUTHORIZATION

Is it possible to create a schema that is owned or accessible by more than one user in the same database?

I was reading postgres create schema documentation and postgres alter schema documentation and there is no syntax to provide more than a single user.

I was also googling for such a solution, either in simple or hard way and found nothing, not even a single point to any workaround.

Could you, please, point me to a solution or possible ways to workaround that, or make me certain, that such a thing is not possible in any way?

Owned by more than one user: no

Accessible by more than one user: yes

To give a user (or role) access to the tables in a schema you can use something like this:

grant all on schema foobar to role_name;
grant all on all tables in schema foobar to role_name;
grant all on all sequences in schema foobar to role_name;
grant all on all functions in schema foobar to role_name;

The grant all on schema grants the two privileges usage and create on that schema. To quote the manual: "allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to "look up" objects within the schema". But this does not grant the privilege to select data from a table within the schema. So all of the four statements above are necessary. If you don't want the users to create new objects, then you would use grant usage on schema ....

It is probably easier to grant this to a role and then grant that role to the individual users if you need to do this for more than one user.

If you want to extend the grants to any newly created table (or other objects), you need to change the default privileges on the schema:

alter default privileges in schema foobar grant all on tables to role_name;
alter default privileges in schema foobar grant all on sequences to role_name;
alter default privileges in schema foobar grant all on functions to to role_name;
alter default privileges in schema foobar grant all on types to role_name;

Don't forget to commit those statements if you are not running in autocommit mode.

Of course if you only want those users to access the objects replace the ALL keyword with SELECT or SELECT,INSERT,UPDATE depending on the object type and what you want to allow them.

You probably also want to set the default search_path for those users (if they only need to access that single schema) to avoid having to fully qualify the object names

StackExchange Database Administrators Q#108967, answer score: 10