CODEWITHSUNDEEP
sqlsecuritypostgresqlsql-grant
HouseMD
HouseMD

Reputation: 242

Can I create a group/user role in postgres so that it can only execute predefined functions?

I want to make a database user, which would use my predefined functions (login(pass, login), get_users_you_are_in_charge_off(login, pass) etc.).

Because this should be open to anybody, i want to create a db user/group role that would only allow that user to execute my predefined functions.

The problem is that those functions use SELECT, UPDATE, INSERT. And when I only give rights to execute the functions, they throw errors, that they don't have permisions to do that.

Any ideas? (Maybe i could statically change a role inside the functions?)

// TO answer some of the comments To 1th comment: OK, I will look at it and reply if it can help me. Thanks. OK, thanks for your answer, it is EXACTLY what I need. Thanks user "plundra"!

To 2th comment: I already have a table or users (with their logins). But if I make a function checkLogin(name, pass), that function needs to select from the table users. And if I don't give right to the user to do SELECTS, it doesn't work. If I do give the rights, then the user can just do SELECT * from users; and see what the passwords are.

OK, thanks for your answers, the first comment is EXACTLY what I need. Thanks user "plundra"!

Upvotes: 0

Views: 472

Answers (2)

HouseMD
HouseMD

Reputation: 242

Check out "SECURITY DEFINER" at postgresql.org/docs/current/static/sql-createfunction.html – plundra

Answered by user plundra. Thats what I needed, thanks :)

Upvotes: 0

Wayne Conrad
Wayne Conrad

Reputation: 107989

The answer is, don't store passwords in the database.

Instead, store salts and password hashes (both text fields).

When creating a new user, you create the salt randomly, hash their selected password with that, and store the hash.

To authenticate someone, take the password they enter, the salt stored in their user record, run them through your hash algorithm (SHA1, etc.), and compared it to the stored hash.

Although easy in concept, the details can bite you, so it's worth using someone else's code for this.

Upvotes: 1

Related Questions