Postgresql – Which grants are needed to access a table

permissionspostgresql

I want to know which grants at the very least are required to select/insert/update/delete from a table.

Say I have a table t in schema s in database d and a role r.

I start with granting the required permissions mentioned above:

grant select, insert, update, delete on table s.t to r;

What else do I need? Are USAGE on schema s and CONNECT on the database absolutely required, but also enough?

grant connect on database d to r;
grant usage   on schema   s to r;

Best Answer

There is no simple list of necessary and sufficient grants, because there are multiple paths that can be used. CONNECT on the database and USAGE on a schema are often done through "PUBLIC" rather than to individual roles, for example.

Newly created databases will have CONNECT privilege for PUBLIC by default, so unless you have gone out of your way to remove it no explicit grant is needed in that case. When you create a database, it comes with a schema named "public" which also has USAGE granted to PUBLIC, but schemas you create manually do not come with that privilege.

Related Solutions

SQL Server Permissions – Detect Execute Permission Granted to Role Without ON Clause

GRANT EXECUTE TO [principal] is simply a shortcut for GRANT EXECUTE ON DATABASE::<dbname> TO [principal];

You can check this using the following:

SELECT dp.name
    , perms.class_desc
    , perms.permission_name
    , perms.state_desc
FROM sys.database_permissions perms
    INNER JOIN sys.database_principals dp ON perms.grantee_principal_id = dp.principal_id 
WHERE dp.name = 'MyRole'

Sql-server – Accessing Table Data ONLY From View

Unless you grant SELECT to 'public' role, you must explicitly grant access to each user, group or database role.

For simplicity, you can create a new database role, (i.e. my_users), then grant select to this role and finally add new users to the role.

Best Answer

Related Solutions

SQL Server Permissions – Detect Execute Permission Granted to Role Without ON Clause

Sql-server – Accessing Table Data ONLY From View

Related Question