CODEWITHSUNDEEP
postgresqlpasswordspassword-encryptionpostgresql-9.6
fadedbee
fadedbee

Reputation: 44807

How can I use postgres roles and passwords for website authentication

I want a future web application's users to be able to log into a postgres database using their website usernames and passwords.

The website will connect to postgres with a special username (e.g. "web") which will be unavailable as a website username. (As would "postgres", etc.)

No users or passwords exist yet, so there are no migration issues. All website users will be postgres users. The website can hash passwords however postgres does.

What is the most sensible way to achieve this with the least duplication of data?

Can I use/extend postgres's internal roles tables as a general source of username/password authentication? i.e. I would need to be able to run a query which will tell me if a given username/password combination is valid.

Or is it better to a have a separate store of website usernames and passwords and somehow get postgres to use this?

Upvotes: 1

Views: 422

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51649

You can indeed check authentication with select

t=# select 'md5'||md5('somePass'||usename) = passwd from pg_shadow where usename = 'web';
 ?column?
----------
 t
(1 row)

but You should not manipulate pg_authid directly, instead using SQL statements, like:

t=# create user web password 'somePass';
CREATE ROLE

or 

t=# alter user web password 'anotherP';
ALTER ROLE
t=# select 'md5'||md5('somePass'||usename) = passwd from pg_shadow where usename = 'web';
 ?column?
----------
 f
(1 row)

t=# select 'md5'||md5('anotherP'||usename) = passwd from pg_shadow where usename = 'web';
 ?column?
----------
 t
(1 row)

and:

t=# drop user web;
DROP ROLE
t=# select 'md5'||md5('anotherP'||usename) = passwd from pg_shadow where usename = 'web';
 ?column?
----------
(0 rows)

to avoid SU role with application, just create wrapups for account manipulations, like:

t=# create or replace function adduser(_u text,_p text) returns boolean as $$
begin
 execute format('create user %I password %L',_u,_p);
 return true;
end;
$$ language plpgsql security definer;
CREATE FUNCTION

security definer will alow app user to create new role:

t=# select adduser('web','newP');
 adduser
---------
 t
(1 row)

t=# select 'md5'||md5('newP'||usename) = passwd from pg_shadow where usename = 'web';
 ?column?
----------
 t
(1 row)

don't forget to add some logic and exceptions, so application would not interfiere with existing SU roles (not droppping, not changing passwords).

Lastly I think such idea can be dangerous. Plan it before implementing...

Upvotes: 2

fadedbee
fadedbee

Reputation: 44807

I've found https://www.postgresql.org/docs/9.6/static/catalog-pg-authid.html which writes about a pg_authid table, which contains usernames and md5 hashes of password || username.

When I've created a user which can update this table, I hope to be able to use it for website logins. It constrains me to using md5, which is not not ideal.

Upvotes: 0

Related Questions