Postgresql – Postgres Access Roles

At the SQL level you can't, since all those tasks are governed by table ownership.

The CREATE on a tablespace is required but not sufficient to create an index on a table and store the index in that tablespace. If you don't have the CREATE right on the tablespace you want to put the index in then you cannot CREATE INDEX that index. However, having that right is not enough; otherwise anybody could create indexes on any table if they had the right to create anything in any tablespace, and we don't want that. Indexes have a performance cost, take heavy locks during creation, and perhaps most importantly an expression index can leak data about the table via a malicious function or operator. So you must also own the table the index is to be created on.

Support for a separate INDEX right on a table could be added to PostgreSQL, but has not been, and might not get accepted if submitted. For now, you're stuck with having to own the table.

You could write a C extension that installs a ProcessUtility_hook that checks what operations are being performed and the current user identity, then rejects or permits them as appropriate. You can find examples of ProcessUtility_hook use in contrib/sepgsql and externally in the bdr_commandfilter.c file in the BDR project source code. You have to compile the extension, install it into the file system, then add it to shared_preload_libraries to install it, so you need full filesystem level access to the server, and usually root access.

A more practical approach is to use a SECURITY DEFINER function as a wrapper. Write a PL/PgSQL function that runs as the table owner and accepts as arguments the table to index, the column(s) to index, etc. Have it create the CREATE INDEX expression using format(...) then pass it to EXECUTE. Do not allow the user to pass arbitrary SQL expressions as arguments, or you're basically giving them full access via SQL injection. Want multiple columns? You'll have to accept colname text[] as an argument and quote_ident each one. And so on. Search for "dynamic SQL plpgsql" to learn more about this approach.

While having SELECT (or another permission) on the entire database implies having SELECT on an object within the database, having the GRANT OPTION for SELECT on the entire database does not imply you have the GRANT OPTION for SELECT on an object in the database.

Correct. There is a difference between "actual" / "explicit" permissions and "effective" permissions. Granting SELECT at the Database-level is a distinctly different permission than granting SELECT on one or more objects within that Database. You would see this difference when looking in the System Catalog View sys.database_permissions. Hence, SELECT permissions on individual tables is not an actual subset of the Database-level SELECT permission. The same holds true for SELECT permission on a Table as opposed to SELECT permission on individual columns in that Table.

Now, while it would help to get some feedback / clarification on the overall goal of this request, it at least appears -- based on the example code in the question -- that the desire is to have a deployment / rollout Login (or Logins) that are not in the sysadmin fixed server role. To that end, you can still accomplish this type of setup using Module Signing. The idea is to create a Certificate and Certificate-based Login and associated User that cannot be Impersonated (i.e. can't log in or become current Login/User via EXECUTE AS) but can hold all of the required permissions necessary to carry out the tasks that the deployment process needs to do. Then, you create one or more Stored Procedures that will execute the required commands. Signing the Stored Procedure(s) with the same Certificate that was used to create the Login and User will allow the permissions granted to the Certificate-based Login and User to apply to code within the Stored Procedure. Given the dynamic nature of the operation, it will need to be done via Dynamic SQL, but this works just fine with Module Signing.

This approach allows you to have a generic set of permissions (assigned to the Certificate-based Login / User) that can be used to assign other permissions. For the moment, it seems easiest to put the User in the db_owner fixed database role as that allows for doing anything you would need to do. And since Module Signing gives the permissions to the code, not the Login / User / Role executing the module (which is how it works with Impersonation via EXECUTE AS), anyone who can execute that code can only ever do what the code is set up to do. In this way, Module Signing allows for much more granular control over extending permissions than Impersonation (it doesn't matter what the permissions granted to the Certificate-based Login / User can do, it only matters what the signed code actually does). And if someone changes that signed code, then the module loses the signature, alerting you to the change, at which time you can review the changes and if ok, then re-sign the module.

Please see the example below that allows for granting SELECT on a particular object to a given role. It is very easy to extend the example to allow for creating the new Role as well as anything else that needs to be done as part of the deployment process.

Solution Setup, Part 1 (General Supporting Structure)

USE [master];
GO

CREATE CERTIFICATE [DeploymentPermissionsKey]
  ENCRYPTION BY PASSWORD = 'not_really_a_password'
  WITH SUBJECT = 'Deployment Permissions';

CREATE LOGIN [DeploymentPermissions]
  FROM CERTIFICATE [DeploymentPermissionsKey];

GRANT VIEW SERVER STATE TO [DeploymentPermissions];

DECLARE @Certificate VARBINARY(2000),
        @PrivateKey VARBINARY(2000);

SELECT @Certificate = CERTENCODED(CERT_ID(N'DeploymentPermissionsKey')),
       @PrivateKey = CERTPRIVATEKEY(CERT_ID(N'DeploymentPermissionsKey'),
                                    'EncryptPass', 'not_really_a_password');

---

USE [tempdb];

--DROP CERTIFICATE [DeploymentPermissionsKey];
DECLARE @CertSQL NVARCHAR(MAX) = N'
CREATE CERTIFICATE [DeploymentPermissionsKey]
  FROM BINARY = ' + CONVERT(NVARCHAR(MAX), @Certificate, 1) + N'
   WITH PRIVATE KEY ( BINARY = ' + CONVERT(NVARCHAR(MAX), @PrivateKey, 1) + N',
                      DECRYPTION BY PASSWORD = ''EncryptPass'',
                      ENCRYPTION BY PASSWORD = ''not_really_a_password''
                    );
';
PRINT @CertSQL;
EXEC(@CertSQL);


--DROP ROLE [DeploymentRole];
CREATE ROLE [DeploymentRole];

--DROP USER [MrDeploy];
CREATE USER [MrDeploy] WITHOUT LOGIN;

ALTER ROLE [DeploymentRole] ADD MEMBER [MrDeploy];


--DROP USER [DeploymentPermissions];
CREATE USER [DeploymentPermissions]
  FROM LOGIN [DeploymentPermissions];

ALTER ROLE [db_owner] ADD MEMBER [DeploymentPermissions];

Solution Setup, Part 2 (Allow Granting Permission to Role)

Please note that due to needing to use Dynamic SQL, we need to protect against SQL Injection by checking / verifying the string input parameters: @RoleName and @ObjectName.

USE [tempdb];
GO
CREATE
--ALTER
PROCEDURE dbo.GrantObjectPermissionsToRole
(
  @RoleName [sysname],
  @ObjectName NVARCHAR(261),
  @PermissionID TINYINT
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);

-- Check parameter values to prevent errors and SQL Injection:
IF (NOT EXISTS(
               SELECT dp.*
               FROM   sys.database_principals dp
               WHERE  dp.[type] = 'R' -- DATABASE_ROLE
               AND    dp.[name] = @RoleName
              )
    )
BEGIN
  RAISERROR(N'Role does not exist: %s', 16, 1, @RoleName);
  RETURN -1;
END;

IF (OBJECT_ID(@ObjectName) IS NULL)
BEGIN
  RAISERROR(N'Object does not exist: %s', 16, 1, @ObjectName);
  RETURN -2;
END;

SET @SQL = N'GRANT '  + CASE @PermissionID
                           WHEN 1 THEN N'SELECT'
                           WHEN 2 THEN N'EXECUTE'
                           ELSE N'Invalid_PermissionID_'
                                + CONVERT(NVARCHAR(10), @PermissionID)
                         END
            + N' ON ' + @ObjectName
            + N' TO ' + @RoleName
            + N' AS [dbo];';

PRINT @SQL; -- debug
EXEC(@SQL);

RETURN;
GO

GRANT EXECUTE ON dbo.[GrantObjectPermissionsToRole] TO [DeploymentRole];

ADD SIGNATURE TO dbo.[GrantObjectPermissionsToRole]
  BY CERTIFICATE [DeploymentPermissionsKey]
  WITH PASSWORD = 'not_really_a_password';

Example Setup

USE [tempdb];
GO

CREATE TABLE [dbo].[TestTable] ([ID] INT NULL);

CREATE ROLE [TestRole];

CREATE USER [TestUser] WITHOUT LOGIN;

ALTER ROLE [TestRole] ADD MEMBER [TestUser];

Tests

Test 1 shows that initially, TestUser, who is in TestRole, does not have permission to SELECT from TestTable.

Test 2 shows that the deployment User, MrDeploy, also does not have SELECT permission on TestTable. Additionally, MrDeploy does not even have the ability to grant any permissions to TestRole. However, MrDeploy can execute the Stored Procedure which does grant the SELECT permission to TestRole.

Test 3 shows that TestUser, via its membership in TestRole, now does have SELECT permission on TestTable. Using sys.fn_my_permissions() we can see the "effective" permissions which cascade down to individual columns. However, selecting from sys.database_permissions we can see the "actual" permission which is just on the Table itself (showing that actual permissions do not cascade).

Test 4 grants the Database-level SELECT permission to MrDeploy so that it can be tested against the sys.fn_my_permissions() functions (to see how the permission cascades down to subobjects) and the sys.database_permissions system catalog view (to see that there are no object-level permissions at all). This again illustrates the difference between "effective" and "actual" / "explicit" permissions.

--- Test # 1 ---
EXECUTE AS USER = 'TestUser';
SELECT SESSION_USER AS [CurrentUser];

SELECT * FROM [dbo].[TestTable]; -- error:
-- The SELECT permission was denied on the object 'TestTable',
--    database 'tempdb', schema 'dbo'.

REVERT;
SELECT SESSION_USER AS [CurrentUser];


--- Test # 2 ---
EXECUTE AS USER = 'MrDeploy';
SELECT SESSION_USER AS [CurrentUser];

SELECT * FROM [dbo].[TestTable]; -- error:
-- The SELECT permission was denied on the object 'TestTable',
--    database 'tempdb', schema 'dbo'.

GRANT SELECT ON [dbo].[TestTable] TO [TestUser]; -- error:
-- Cannot find the object 'TestTable', because it does not exist or you
--    do not have permission.

GRANT SELECT ON [dbo].[TestTable] TO [TestUser] AS [dbo]; -- error:
-- Cannot find the user 'dbo', because it does not exist or you do not have permission.

SELECT dp.*
FROM   sys.database_principals dp
WHERE  dp.[type] = 'R' -- DATABASE_ROLE
AND    dp.[name] = N'TestRole'; -- no rows due to no VIEW SERVER STATE permission

EXEC dbo.[GrantObjectPermissionsToRole]
  @RoleName = N'TestRole',
  @ObjectName = N'TestTable',
  @PermissionID = 1;
-- Success!

SELECT * FROM [dbo].[TestTable]; -- error (still):
-- The SELECT permission was denied on the object 'TestTable',
--    database 'tempdb', schema 'dbo'.

REVERT;
SELECT SESSION_USER AS [CurrentUser];


--- Test # 3 ---
EXECUTE AS USER = 'TestUser';
SELECT SESSION_USER AS [CurrentUser];

SELECT * FROM [dbo].[TestTable]; -- Success!!

SELECT * FROM sys.fn_my_permissions(N'dbo.TestTable', 'OBJECT');

SELECT *,
       USER_NAME([grantee_principal_id]) AS [Grantee],
       USER_NAME([grantor_principal_id]) AS [Grantor]
FROM   sys.database_permissions
WHERE  [class] = 1 -- OBJECT_OR_COLUMN
AND    [major_id] = OBJECT_ID(N'dbo.TestTable');

REVERT;
SELECT SESSION_USER AS [CurrentUser];


--- Test # 4 ---
GRANT SELECT TO [MrDeploy];


EXECUTE AS USER = 'MrDeploy';
SELECT SESSION_USER AS [CurrentUser];

SELECT * FROM sys.fn_my_permissions(N'dbo.TestTable', 'OBJECT');

SELECT *,
       USER_NAME([grantee_principal_id]) AS [Grantee],
       USER_NAME([grantor_principal_id]) AS [Grantor]
FROM   sys.database_permissions
WHERE  [class] = 1 -- OBJECT_OR_COLUMN
AND    [major_id] = OBJECT_ID(N'dbo.TestTable');

REVERT;
SELECT SESSION_USER AS [CurrentUser];