Grant Access to Just One Schema in Postgresql

Grant all on a specific schema in the db to a group role in PostgreSQL

You found the shorthand to set privileges for all existing tables in the given schema. The manual clarifies:

(but note that ALL TABLES is considered to include views and foreign tables).

Bold emphasis mine. serial columns are implemented with nextval() on a sequence as column default and, quoting the manual:

For sequences, this privilege allows the use of the currval and nextval functions.

So if there are serial columns, you'll also want to grant USAGE (or ALL PRIVILEGES) on sequences

GRANT USAGE ON ALL SEQUENCES IN SCHEMA foo TO mygrp;

Note: identity columns in Postgres 10 or later use implicit sequences that don't require additional privileges. (Consider upgrading serial columns.)

What about new objects?

You'll also be interested in DEFAULT PRIVILEGES for users or schemas:

ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT ALL PRIVILEGES ON TABLES TO staff;
ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT USAGE          ON SEQUENCES TO staff;
ALTER DEFAULT PRIVILEGES IN SCHEMA foo REVOKE ...;

This sets privileges for objects created in the future automatically - but not for pre-existing objects.

Default privileges are only applied to objects created by the targeted user (FOR ROLE my_creating_role). If that clause is omitted, it defaults to the current user executing ALTER DEFAULT PRIVILEGES. To be explicit:

ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo GRANT ...;
ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo REVOKE ...;

Note also that all versions of pgAdmin III have a subtle bug and display default privileges in the SQL pane, even if they do not apply to the current role. Be sure to adjust the FOR ROLE clause manually when copying the SQL script.

Gant select on schema in Postgres

With event triggers you can handle that :

CREATE OR REPLACE FUNCTION auto_grant_func()
RETURNS event_trigger AS $$
BEGIN
    GRANT CONNECT ON DATABASE postgres TO readonly;
    GRANT USAGE ON SCHEMA schema1 TO readonly;
    GRANT SELECT ON ALL TABLES IN SCHEMA schema1 TO readonly;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER auto_grant_trigger
    ON ddl_command_end
    WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS')
EXECUTE PROCEDURE auto_grant_func();

Postgresql 2 users with different permissions on a specific schema

Thank you @JGH, that was it. : the user2 had to give himself access to user1 to schema some_shema.
What i did :

psql
CREATE USER user2 WITH PASSWORD '***';
\c my_database
CREATE SCHEMA some_schema AUTHORIZATION user2;
\c my_database user2
create table some_schema.test(id int);
insert into some_schema.t(100);
GRANT USAGE ON SCHEMA some_schema TO user1;
GRANT SELECT ON ALL TABLES IN SCHEMA some_schema TO user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema GRANT SELECT ON TABLES TO user1;

then :

\c my_database user1
select * from some_schema.t;

=> shows result 100 :)

Restrict user to one schema in PostgreSQL?

It is impossible to configure PostgreSQL so that a user can only see those objects in the system catalogs for which he or she has permissions.

If you need such a setup, you should create a database per user.

how to allow access only to specific and public schemas in Postgres

This query:

select 
    kasutaja,
    rtrim(
        'public,' ||
            coalesce(
                regexp_replace(
                    allowedcompanies, '(\d+)', 'company\1', 'g'),
            ''),
        ',') companies
from kasutaja

gives:

kasutaja  companies
user1     public,company1
user2     public,company1,company2
user3     public,company3
user4     public 

In anonymous code block you can walk on this result in loop and execute apprioriate revoke and grant:

do $$
declare
    rec record;
begin
    for rec in
        select 
            kasutaja,
            rtrim(
                'public,' ||
                    coalesce(
                        regexp_replace(
                            allowedcompanies, '(\d+)', 'company\1', 'g'),
                    ''),
                ',') companies
        from kasutaja
    loop
        execute format(
            'revoke all on all tables in schema company1,company2,company3 from %s',
            rec.kasutaja);
        execute format(
            'grant all on all tables in schema %s to %s',
            rec.companies, rec.kasutaja);
    end loop;
end $$;

Use e.g. grant insert, delete, update (or whatever you need) instead of grant all.

---

Anonymous code block was introduced in Postgres 9.0. In 8.4 you have to use a function instead:

create function set_privileges()
returns void language plpgsql as $$
...
end $$;

select set_privileges();

What does GRANT USAGE ON SCHEMA do exactly?

GRANTs on different objects are separate. GRANTing on a database doesn't GRANT rights to the schema within. Similiarly, GRANTing on a schema doesn't grant rights on the tables within.

If you have rights to SELECT from a table, but not the right to see it in the schema that contains it then you can't access the table.

The rights tests are done in order:

Do you have `USAGE` on the schema? 
    No:  Reject access. 
    Yes: Do you also have the appropriate rights on the table? 
        No:  Reject access. 
        Yes: Check column privileges.

Your confusion may arise from the fact that the public schema has a default GRANT of all rights to the role public, which every user/group is a member of. So everyone already has usage on that schema.

The phrase:

(assuming that the objects' own privilege requirements are also met)

Is saying that you must have USAGE on a schema to use objects within it, but having USAGE on a schema is not by itself sufficient to use the objects within the schema, you must also have rights on the objects themselves.

It's like a directory tree. If you create a directory somedir with file somefile within it then set it so that only your own user can access the directory or the file (mode rwx------ on the dir, mode rw------- on the file) then nobody else can list the directory to see that the file exists.

If you were to grant world-read rights on the file (mode rw-r--r--) but not change the directory permissions it'd make no difference. Nobody could see the file in order to read it, because they don't have the rights to list the directory.

If you instead set rwx-r-xr-x on the directory, setting it so people can list and traverse the directory but not changing the file permissions, people could list the file but could not read it because they'd have no access to the file.

You need to set both permissions for people to actually be able to view the file.

Same thing in Pg. You need both schema USAGE rights and object rights to perform an action on an object, like SELECT from a table.

(The analogy falls down a bit in that PostgreSQL doesn't have row-level security yet, so the user can still "see" that the table exists in the schema by SELECTing from pg_class directly. They can't interact with it in any way, though, so it's just the "list" part that isn't quite the same.)

---

Related Topics