Re: Getting permission denied after grant

El 17/06/13 17:08, François Beausoleil escribió:
> I have a problem granting permissions. The end result I'm looking for is:
>
> Dustin and Pablo are data analysts. When either creates a table, the table must be created outside of public, and
bothmust be able to delete the table when their work is finished. I would prefer that the tables they create be owned
bythe dataanalysts role, but that's not required. They should have read-only access to all tables in public. If a new
tableis created in public, they should automatically receive read-only access. 
>
> Here's my implementation of the requirements:
>
> -- Create both users
> CREATE USER dustin WITH LOGIN;
> CREATE USER pablo WITH LOGIN;
>
> -- Both belong to the same role/group
> CREATE USER dataanalysts WITH NOLOGIN;
> GRANT dataanalysts TO pablo;
> GRANT dataanalysts TO dustin;
>
> -- Common schema for both
> CREATE SCHEMA dataanalysts;
> ALTER SCHEMA dataanalysts SET OWNER TO dataanalysts;

Wrong syntax:

ALTER SCHEMA dataanalysts OWNER TO dataanalysts;

No SET there.

> -- Whenever a data analyst creates a table, prefer the dataanalysts schema
> ALTER USER pablo SET search_path = dataanalysts, public;
> ALTER USER dustin SET search_path = dataanalysts, public;
>
> -- When pablo creates a table, allow any data analyst to query / update / delete the table
> ALTER DEFAULT PRIVILEGES FOR USER pablo IN SCHEMA dataanalysts GRANT ALL PRIVILEGES ON TABLES TO dataanalysts;
>
> -- When dustin creates a table, allow any data analyst to query / update / delete the table
> ALTER DEFAULT PRIVILEGES FOR USER dustin IN SCHEMA dataanalysts GRANT ALL PRIVILEGES ON TABLES TO dataanalysts;

Here you change the default privileges for user pablo and dustin, but...

> And the default privileges in this database are:
>
> svanalytics_production=# \ddp
>                          Default access privileges
>      Owner     |    Schema    | Type  |         Access privileges
> --------------+--------------+-------+-----------------------------------
>   dataanalysts | dataanalysts | table | dataanalysts=arwdDxt/dataanalysts
>   svanalytics  | public       | table | dataanalysts=r/svanalytics
>
> I believe the first line means "if a data analyst creates a table, grant all privileges to dataanalysts". The 2nd
linemeans "when svanalytics creates a table in public, grant select to dataanalysts". 

Which are the defaults for pablo and dustin? If the ALTER DEFAULT
PRIVILEGES would have passed, you would see one line for each the two
users you created:

# \ddp
                   Default access privileges
  Owner  |    Schema    | Type  |      Access privileges
--------+--------------+-------+-----------------------------
  dustin | dataanalysts | table | dataanalysts=arwdDxt/dustin
  pablo  | dataanalysts | table | dataanalysts=arwdDxt/pablo

This is the output I see after executing the DDL from above.

> Did I miss anything? What did I do wrong? Why can't a dataanalyst view a table's contents?

Not sure. Looks like ALTER DEFAULT PRIVILEGES didn't pass for some reason.