Manage PostgreSQL User Permissions – Multiple Databases and Users

to do it correctly is not very simple, although once setup, your done.

Data users or application roles default have database scoped permissions. Database users are linked to Logins and therefore can have server wide permissions from their Login. Application roles aren't linked and can only get server wide permission by using a form of impersonation.

Any way to give these server scope permissions by means of impersonating will fail by default.This is by design.

You have 2 options:

1. Tell SQL Server that you as a DBA trust any user in that database to have privilegde escalation to server wide permission if impersonation is used. You acomplish this by setting the option TRUSTWORTHY to ON However if you don't fully understand what this option does first read: SQL Server 2012 Best Practice Whitepaper (Specifically page 18 and 19 about "Database Ownership and Trust")
2. Use signed stored procedures to accomplish your task.

I'll first give you the example of option 1:

CREATE LOGIN [appRoleProxy] WITH PASSWORD ='1234abcd!@'
GO

EXEC sp_addsrvrolemember    @loginame='appRoleProxy',
                            @rolename='securityadmin'

GO

CREATE DATABASE [Stefaan]
go

ALTER DATABASE [Stefaan] SET TRUSTWORTHY ON
GO

USE [Stefaan]
GO

--add the proxy account to the dbo role since your stored procedure will be running in the context of this account and is also accessing your user database.
ALTER ROLE [db_owner] ADD MEMBER [appRoleProxy]
GO

CREATE APPLICATION ROLE appRole WITH PASSWORD ='Passw0rd!';
GO

CREATE PROCEDURE dbo.usp_testproc 
@LoginName nvarchar(128),
@Password nvarchar(128),
@DBName nvarchar(128)
WITH EXECUTE AS 'appRoleProxy' --Impersonate a SQL server login with the proper server level permissions
AS
DECLARE @SQL varchar(max)
BEGIN
    IF NOT EXISTS ( SELECT  name
                    FROM    sys.server_principals
                    WHERE   name = @LoginName )
        BEGIN
            SET @SQL = 'CREATE LOGIN [' + @LoginName + '] WITH PASSWORD = '''
                + @Password + ''', DEFAULT_DATABASE=[' + @DBNAME
                + '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
            EXECUTE(@SQL);
        END

    IF NOT EXISTS ( SELECT  name
                    FROM    sys.database_principals
                    WHERE   name = @LoginName )
        BEGIN
            SET @SQL = 'CREATE USER [' + @LoginName + '] FOR LOGIN ['
                + @LoginName + ']';
            EXECUTE(@SQL);
        END

    IF EXISTS ( SELECT  name
                FROM    sys.server_principals
                WHERE   name = @LoginName )
        BEGIN
            EXEC sp_addsrvrolemember @LoginName, 'securityadmin'
        END
END
GO

GRANT EXECUTE ON [dbo].[usp_testproc] TO [appRole]
GO


sp_setapprole   @rolename='appRole',
                @password='Passw0rd!'



EXEC usp_testproc 'testlogin','password23@','stefaan'

Now for an example of option 2 I'd like to point out a answer a gave on another question: Msg 4834 “You do not have permission to use the bulk load statement”

Where can I find a decent guide, tutorial or video series on this?

You'll find everything in the manual. Links below.
Granted, the matter is not trivial and sometimes confusing. Here is a recipe for the use case:

Recipe

I want to have it configured so only the hostdb_admin can create (and drop and alter) tables;
the hostdb_mgr can read, insert, update and delete on all tables by default;
and the hostdb_usr can only read all tables (and views).

As superuser postgres:

CREATE USER schma_admin WITH PASSWORD 'youwish';
-- CREATE USER schma_admin WITH PASSWORD 'youwish' CREATEDB CREATEROLE; -- see below
CREATE USER schma_mgr   WITH PASSWORD 'youwish2';
CREATE USER schma_usr   WITH PASSWORD 'youwish3';

If you want a more powerful admin that can also manage databases and roles, add the role attributes CREATEDB and CREATEROLE above.

Grant each role to the next higher level, so all levels "inherit" at least the set of privileges from the next lower level (cascading):

GRANT schma_usr TO schma_mgr;
GRANT schma_mgr TO schma_admin;

CREATE DATABASE hostdb;
REVOKE ALL ON DATABASE hostdb FROM public;  -- see notes below!

GRANT CONNECT ON DATABASE hostdb TO schma_usr;  -- others inherit

\connect hostdb  -- psql syntax

I am naming the schema schma (not hostdb which would be confusing). Pick any name. Optionally make schma_admin the owner of the schema:

CREATE SCHEMA schma AUTHORIZATION schma_admin;

SET search_path = schma;  -- see notes

ALTER ROLE schma_admin IN DATABASE hostdb SET search_path = schma; -- not inherited
ALTER ROLE schma_mgr   IN DATABASE hostdb SET search_path = schma;
ALTER ROLE schma_usr   IN DATABASE hostdb SET search_path = schma;

GRANT USAGE  ON SCHEMA schma TO schma_usr;
GRANT CREATE ON SCHEMA schma TO schma_admin;

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT SELECT                           ON TABLES TO schma_usr;  -- only read

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO schma_mgr;  -- + write, TRUNCATE optional

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO schma_mgr;  -- SELECT, UPDATE are optional 

For and drop and alter see notes below.

As things get more advanced I'll also have questions to apply similar restrictions on TRIGGERS, stored procedures, VIEWS and perhaps other objects.

Views are special. For one:

... (but note that ALL TABLES is considered to include views and foreign tables).

And for Updatable Views:

Note that the user performing the insert, update or delete on the view must have the corresponding insert, update or delete privilege on the view. In addition the view's owner must have the relevant privileges on the underlying base relations, but the user performing the update does not need any permissions on the underlying base relations (see Section 38.5).

Triggers are special, too. You need the TRIGGER privilege on the table, and:

• What are the privileges required to execute a trigger function in PostgreSQL 8.4?

But we are already over-expanding the scope of this question ...

Important Notes

Ownership

If you want to allow schma_admin (alone) to drop and alter tables, make the role own all objects. The documentation:

The right to drop an object, or to alter its definition in any way, is not treated as a grantable privilege; it is inherent in the owner, and cannot be granted or revoked. (However, a similar effect can be obtained by granting or revoking membership in the role that owns the object; see below.) The owner implicitly has all grant options for the object, too.

ALTER TABLE some_tbl OWNER TO schma_admin;

Or create all objects with the role schma_admin to begin with, then you need not set the owner explicitly. It also simplifies default privileges, which you then only have to set for the one role:

Pre-existing objects

Default privileges only apply for newly created objects and only for the particular role they are created with. You'll want to adapt permissions for existing objects, too:

• Permission denied for relation <table>

The same applies if you create objects with a role that does not have DEFAULT PRIVILEGES set, like the superuser postgres. Reassign ownership to schma_admin and set privileges manually - or set DEFAULT PRIVILEGES for postgres as well (while connected to the right DB!):

ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ...  -- etc.

Default privileges

You were missing an important aspect of the ALTER DEFAULT PRIVILEGES command. It applies to the current role unless specified otherwise:

• Can't alter default privileges

Default privileges only apply to the current database. So you don't mess with other databases in the DB cluster. The documentation:

for all objects created in the current database

You may also want to set default privileges for FUNCTIONS and TYPES (not just TABLES and SEQUENCES), but those might not be needed.

Default privileges for PUBLIC

Default privileges granted to PUBLIC are rudimentary and overestimated by some. The documentation:

PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces. For other types, the default privileges granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for databases; EXECUTE privilege for functions; and USAGE privilege for languages.

Bold emphasis mine. typically the one command above is enough to cover everything:

REVOKE ALL ON DATABASE hostdb FROM public;

In particular, no default privileges are granted to PUBLIC for new schemas. It may be confusing that the default schema named "public" starts with ALL privileges for PUBLIC. That's just a convenience feature to ease the start with newly created databases. It does not affect other schemas in any way. You can revoke these privileges in the template database template1, then all newly created databases in this cluster start without them:

\connect template1
REVOKE ALL ON SCHEMA public FROM public;

The privilege TEMP

Since we revoked all privileges on hostdb from PUBLIC, regular users cannot create temporary tables unless we explicitly allow it. You may or may not want to add this:

GRANT TEMP ON DATABASE hostdb TO schma_mgr;

search_path

Don't forget to set the search_path. If you only got the one database in the cluster you can just set the global default in postgresql.conf. Else (more likely) set it as property of the database, or just for involved roles or even the combination of both. Details:

• How does the search_path influence identifier resolution and the “current schema”

You may want to set it to schma, public if you use the public schema as well, or even (less likely) $user, schma, public ...

An alternative would be to use the default schema "public" which should work with default settings for search_path unless you changed that. Remember to revoke privileges for PUBLIC in this case.

Related

• Grant privileges for a particular database in PostgreSQL
• PostgreSQL - DB user should only be allowed to call functions