In SQL Server, I can have Login level account and User account(in particular database). So, I can show the relationship using this SQL statement in SQL Server:

SELECT @DBUser = a.[name]
FROM sys.database_principals dp
RIGHT JOIN sys.sql_logins sl ON dp.sid = sl.sid

I would like to create a user for a particular database in my PostgreSQL and give permissions for SELECT, CREATE, UPDATE, DELETE operations.

In SQL Server I can do this in this way:

-- login was created before
CREATE USER MyUser FOR LOGIN MyUser

IF (IS_ROLEMEMBER('db_datareader', MyUser) = 0)
BEGIN
    EXEC sp_addrolemember 'db_datareader', MyUser
END

IF (IS_ROLEMEMBER('db_datawriter', MyUser) = 0)
BEGIN
    EXEC sp_addrolemember 'db_datawriter',MyUser
END

But how can I do the similar task in PostgreSQL correctly?

CodePudding user response：

1. PostGreSQL does not have two level security (at the Instance level with login and at the db level with user) like SQL Server... There is only user in databases.

2. There is not builtin role in PostGreSQL like the one you have at Instance or DB level. You must create those role and apply them to the SQL users you want

3. by the way sp_addrolemember is a deprecacted feature in MS SQL Server and must not be use... The Transact SQL command to do that is :

   ALTER ROLE ... ADD MEMBER ...