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 both
mustbe able to delete the table when their work is finished. I would prefer that the tables they create be owned by the
dataanalystsrole, but that's not required. They should have read-only access to all tables in public. If a new table is
createdin 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;
-- 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;
-- Existing tables in public are read-only for all dataanalysts
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dataanalysts;
-- There were already existing tables in schema dataanalysts, so grant everything to all data analysts
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA dataanalysts TO dataanalysts;
Now, the problem is whenever dustin or pablo connect, they don't seem to have usage privilege on schema dataanalysts:
$ psql -U pablo svanalytics_production
psql (9.1.9)
Type "help" for help.
svanalytics_production => select count(*) from dataanalysts."CFM";
ERROR: permission denied for schema dataanalysts
LINE 1: select count(*) from dataanalysts."CFM";
Logging in as the DB superuser, I can list the permissions on the schema:
List of schemas
Name | Owner | Access privileges | Description
--------------------+--------------+------------------------------+----------------------------------
dataanalysts | dataanalysts | dataanalysts=UC/dataanalysts |
public | postgres | postgres=UC/postgres +| standard public schema
According to my understanding, UC means: USAGE and CREATE privileges are granted to dataanalysts. They can list the
contentsof the schema, and the schema of the tables, but can't access the data.
As the DB superuser, checking privileges on CFM says:
svanalytics_production=# \dp dataanalysts."CFM"
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------------+------+-------+--------------------------------------+--------------------------
dataanalysts | CFM | table | dataanalysts=arwdDxt/dataanalysts +|
| | | svanbatch=arwdDxt/dataanalysts +|
| | | svaninteractive=arwdDxt/dataanalysts+|
| | | svaninject=r/dataanalysts |
Which again means to me "dataanalysts have all privileges", and dustin and pablo are part of dataanalysts, as evidenced
here:
svanalytics_production=# \dg
List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------+----------------
dataanalysts | Cannot login | {}
dustin | | {dataanalysts}
pablo | | {dataanalysts}
postgres | Superuser, Create role, Create DB, Replication | {}
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 line
means"when svanalytics creates a table in public, grant select to dataanalysts".
Did I miss anything? What did I do wrong? Why can't a dataanalyst view a table's contents?
Thanks,
François Beausoleil
