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.
Best Answer
Set your default privileges for the schema using
ALTER DEFAULT PRIVILEGES