1

For a production server, I wanted to separate the super user, admin and app roles to limit scope of mistakes.

So I've created an account that our app uses to access the DB, and an admin account. The admin account is also used to run migrations to create tables.

I thought I'd ironed out all the issues, the app user can access all existing tables (which were created by the super user), however having now run a migration that creates a table, the app has no access to that table.

The users were granted access with the following commands:

GRANT CREATE, CONNECT ON DATABASE test TO admin;
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON ALL TABLES IN SCHEMA public 
  TO admin;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO admin;

GRANT CONNECT ON DATABASE test TO api;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public
  TO api;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO api;

Then the admin user ran CREATE TABLE new_table; and trying to access that table, the app gets permission denied for relation new_table

1) What grant am I missing to allow api access to the new table 2) Out of curiousity, if I didn't have a record of the previous grants, how would I examine the grants of the users and discover the privilege that's missing

1

ALL TABLES/ALL SEQUENCES in that context means all tables/sequences that exist now, at the time the grant is issued. It doesn't include tables/sequences created after the grant was issued.

Your first option is to include the grants explicitly in the migrate scripts. Every time you create a table/sequence also issue the respective grants to api.

Or you can alter the default privileges. In Postgres default privileges can be stored, that will be applied to a newly created object automatically. You can change them with ALTER DEFAULT PRIVILEGES.

In your case your probably after

ALTER DEFAULT PRIVILEGES IN SCHEMA public
                            GRANT SELECT,
                                  INSERT,
                                  UPDATE,
                                  DELETE
                                  ON TABLES
                                  TO api;

and

ALTER DEFAULT PRIVILEGES IN SCHEMA public
                            GRANT USAGE,
                                  SELECT
                                  ON SEQUENCES
                                  TO api;

Note that the migration scripts need to run under the same user, that did the ALTER DEFAULT PRIVILEGES for them to take effect.

The privileges on objects can be obtained via the pg_class catalog table. For example, if you wanted to query all regular tables in the schema public you could do:

SELECT rel.relname,
       rel.relacl
       FROM pg_class rel
            INNER JOIN pg_namespace nsp
                       ON nsp.oid = rel.relnamespace
       WHERE nsp.nspname = 'public'
             AND rel.relkind = 'r';

For sequences the relkind is S instead of r, so

SELECT rel.relname,
       rel.relacl
       FROM pg_class rel
            INNER JOIN pg_namespace nsp
                       ON nsp.oid = rel.relnamespace
       WHERE nsp.nspname = 'public'
             AND rel.relkind = 'S';

would give you the sequences in public.

The column relname displays the object's name and relacl holds an array of the privileges on the object in form of an aclitem. An ACL is represented by a string of the form <grantee>=<privileges>/<grantor>. <grantee> can be empty if the privileges are granted to public. <privileges> encodes the privileges in form of a string of letters. In your case you want to see api=arwd/admin for a regular table and api=rU/admin for sequences.

  • a is for "append" (INSERT),
  • r for "read" (SELECT),
  • w for "write" (UPDATE),
  • d for DELETE and
  • U for USAGE.

More details on the ACLs can be found in the manual on GRANT

Explicit granting is more "secure" in the way, that you don't "forget" the default privileges are in place one day when creating a table api shouldn't be able to access. You'd have to explicitly revoke the privileges after the creation of the object in such a case.

Using default privileges is more convenient of course.

  • Thanks, but this doesn't seem to work. Having applies the default privileges, i dropped the table and re-ran the migration and still the api cannot access it. Strangely, when I ran the query you suggested, the row for the new table is NULL, ie no user has and privileges assigned to the table – ChristopherJ Jan 12 at 13:30
  • Let us continue this discussion in chat. – ChristopherJ Jan 12 at 14:05
  • Ah! got it. I didn't realise the default privileges were tied to the user that ran ALTER DEFAULT PRIVILEGES I had run that command as the super user, then created the table as the admin user Running the ALTER DEFAULT PRIVILEGES as admin seems to have solved it – ChristopherJ Jan 12 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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