7

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?

1 Answer 1

10

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

4
  • could you explain a little more? for first block of code - do I need to use them all or the first matches next 3 by itself? second block - do I have to execute all 4 lines separately? Is there no way to alter these defaults in single alter command?
    – tymik
    Aug 5, 2015 at 12:51
  • 1
    @tymik: no unfortunately you have to run each of those statements. There is no single privilege - especially for the default privileges. But you only need to do this once to grant this to a role. Then grant the role to the users who should actually get those privileges.
    – user1822
    Aug 5, 2015 at 12:57
  • and how about the first block of code? it is still unclear for me - it seems like the grant all on schema foobar to role_name; covers the following 3 statements. Am I right or the first block of code also requires al 4 statements to be executed?
    – tymik
    Aug 5, 2015 at 13:00
  • 1
    @tymik: those are different things: grant all on schema grants the right to "use" the schema in the first place. It's a pre-requisite to be able to "access" anything in the schema. See my edit. This is explained quite nicely in the manual: postgresql.org/docs/current/static/sql-grant.html
    – user1822
    Aug 5, 2015 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.