Logins and users

On this page:

• Prerequisites
• Roles and users in PostgreSQL
• Logins and users in Babelfish
• Creating login with sysadmin privileges
• Creating login-owned databases
• Granting user permissions for particular tables

Prerequisites

1. Install WiltonDB
2. Setup TDS connection from SSMS
3. Setup PostgreSQL connection from PgAdmin
4. Creating multiple databases

Roles and users in PostgreSQL

"Role" is the only one type of authentication principal in Postgres. Roles are global, they exist on a DB cluster level. Privileges are granted to roles for particular DB objects.

By convention, roles which has "login" attribute are called "Users". And roles without "login" attribute are called "Groups".

Logins and users in Babelfish

Babelfish supports the notion of "Logins" and "Users" from MSSQL.

"Login" is a server-wide (DB cluster-wide) authentication principal. Login's name and password are used to establish connection to DB.

Babelfish "Login" corresponds directly to PostgreSQL "User". The same name and password are used to connect on both TDS (default port 1433) and PostgreSQL (default 5432) ports.

Existing logins can be listed by querying pg_user Postgres system view or one of MSSQL-specific system views - sys.syslogins or sys.server_principals:

select usename, usecreatedb, usesuper from pg_user

select * from sys.syslogins

select * from sys.server_principals

"User" is an entity created inside a particular logical DB. Login can be mapped to one or more users. User can be granted permissions to access particular DB objects (tables, functions etc).

Babelfish has limited support for logins/users permission management. Below we are listing common scenarios that are supported in WiltonDB 3.3 (update 8.13.x or later).

Creating login with sysadmin privileges

MSSQL has a number of server-level permissions and predefined server-level roles (details).

WiltonDB supports only one sysadmin server-level role, and does NOT support assigning server-level permissions.

Create new login:

create login login_1 with password = 'login_1'

select usename, usecreatedb, usesuper from pg_user

usename                        usecreatedb usesuper
------------------------------ ----------- --------
postgres                       1           1
wilton                         1           1
login_1                        0           0

This new login can be used to connect to DB, but cannot create new databases or access objects created in these databases by other users.

sqlcmd -S 127.0.0.1,1433 -U login_1 -P login_1
1> create database testdb_1
2> go
Msg 33557097, Level 16, State 1, Server BABELFISH, Line 1
permission denied to create database

Add login_1 to the pre-defined sysadmin server role:

alter server role sysadmin add member login_1

Now usecreatedb attribute is set for login_1, it has all permissions on TDS connection, can access all DBs and create new ones:

select usename, usecreatedb, usesuper from pg_user

usename                        usecreatedb usesuper
------------------------------ ----------- --------
postgres                       1           1
wilton                         1           1
login_1                        1           0

sysadmin membership can be revoked using:

alter server role sysadmin drop member login_1

Creating login-owned databases

A common scenario with DB permissions setup is when new login is created for a particular application. New DB is created for this login and it is set as a "DB owner" for this DB. Such login has all the privileges in the DB it owns and has no access to other DBs.

Create new login:

create login login_1 with password = 'login_1'

select usename, usecreatedb, usesuper from pg_user

usename                        usecreatedb usesuper
------------------------------ ----------- --------
postgres                       1           1
wilton                         1           1
login_1                        0           0

Create new DB:

create database db_1

Make login_1 the owner of this new DB:

alter authorization on database::db_1 to login_1

Now connecting as login_1 we can open and use db_1 with full permissions:

> sqlcmd -S 127.0.0.1,1433 -U login_1 -P login_1
1> use db_1
2> go
Changed database context to 'db_1'.
1> create table tab1 (col1 int)
2> go

But we cannot access other DBs or create new ones:

1> use db_2
2> go
Msg 33557097, Level 16, State 1, Server BABELFISH, Line 1
The server principal "login_1" is not able to access the database "db_2" under the current security context
1>

DB ownership can be checked using the following query:

select name, owner, crdate
from sys.babelfish_sysdatabases
where name = 'db_1'

name            owner           crdate
--------------- --------------- ------------------------------
db_1            login_1         2024-04-22 16:27:56+01

Granting user permissions for particular tables

In some scenarios it may be necessary to give a user granular (and usually very limited) permissions for particular DB objects. For example to only allow querying a particular view or writing to a particular table. To grant permission to DB objects it is necessary first to create a DB user and map it to server login.

Create new login:

create login login_1 with password = 'login_1'

Create new DB:

create database db_1

Create a user in this DB and map it to login_1 login. User is created in a currently-active DB, so changing to db_1 is necessary:

use db_1

create user user_1 for login login_1

Users mapped to particular login can be listed with the following query:

select login_name, orig_username, database_name, create_date, modify_date
from sys.babelfish_authid_user_ext
where login_name = 'login_1'

login_name      orig_username   database_name   create_date               modify_date
--------------- --------------- --------------- ------------------------- -------------------------
login_1         user_1          db_1            2024-04-22 15:52:55.99348 2024-04-22 15:52:55.99348

Lets create two tables and one view in db_1:

use db_1

create table tab_1 (col1 int)
insert into tab_1 values (42)

create table tab_2 (col2 int)

create view view_1 as select * from tab_1

Grant user_1 permissions to read and insert into (but not delete/modify) tab_1 and to read view_1:

grant select on tab_1 to user_1
grant insert on tab_1 to user_1
grant select on view_1 to user_1

Now connecting to db_1 as login_1 we can access tab_1 and view_1:

> sqlcmd -S 127.0.0.1,1433 -U login_1 -P login_1
1> use db_1
2> go
Changed database context to 'db_1'.
1> insert into tab_1 values(43)
2> go

(1 rows affected)
1> select * from view_1
2> go
col1
-----------
         42
         43

(2 rows affected)

But cannot access tab_2 or delete from tab_1:

1> select * from tab_2
2> go
Msg 33557097, Level 16, State 1, Server BABELFISH, Line 1
permission denied for table tab_2
1> delete from tab_1
2> go
Msg 33557097, Level 16, State 1, Server BABELFISH, Line 1
permission denied for table tab_1

Permissions granted to objects in a particular DB to a particular user can be listed with the following query:

select
    rel.relname,
    nsp.nspname,
    gtor.rolname as grantor,
    gtee.rolname as grantee,
    acl.privilege_type,
    acl.is_grantable
from pg_class rel
join aclexplode(coalesce(relacl, acldefault('r', relowner))) acl on 1 = 1
join pg_namespace nsp on rel.relnamespace = nsp.oid
join pg_roles gtor on acl.grantor = gtor.oid
join pg_roles gtee on acl.grantee = gtee.oid
where gtee.rolname = 'db_1' + '_' + 'user_1'

relname    nspname    grantor         grantee         privilege_type is_grantable
---------- ---------- --------------- --------------- -------------- ------------
tab_1      db_1_dbo   db_1_dbo        db_1_user_1     INSERT         0
tab_1      db_1_dbo   db_1_dbo        db_1_user_1     SELECT         0
view_1     db_1_dbo   db_1_dbo        db_1_user_1     SELECT         0

Granted permissions can be taken out using revoke:

revoke select on view_1 from user_1