Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Fetching contributors…

Cannot retrieve contributors at this time

326 lines (277 sloc) 9.787 kB
-- sql/03-users.sql SQL Migration
SET client_min_messages TO warning;
BEGIN;
CREATE TYPE status AS ENUM(
'new',
'active',
'inactive',
'deleted'
);
CREATE TABLE users (
nickname LABEL PRIMARY KEY,
password TEXT NOT NULL,
full_name TEXT NOT NULL,
email EMAIL NOT NULL UNIQUE,
uri URI NULL,
twitter CITEXT NOT NULL DEFAULT '',
why TEXT NOT NULL DEFAULT '',
status STATUS NOT NULL DEFAULT 'new',
set_by LABEL NOT NULL REFERENCES users(nickname),
is_admin BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
visited_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
GRANT SELECT ON users TO pgxn;
CREATE OR REPLACE FUNCTION insert_user(
nickname LABEL,
password TEXT,
full_name TEXT DEFAULT '',
email EMAIL DEFAULT NULL,
uri URI DEFAULT NULL,
twitter CITEXT DEFAULT NULL,
why TEXT DEFAULT NULL
) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
/*
% SELECT insert_user(
nickname := 'theory',
password := '***',
full_name := 'David Wheeler',
email := 'theory@pgxn.org',
uri := 'http://justatheory.com/',
twitter := 'theory',
why := 'Because I’m a bitchin’ Pg developer, yo.'
);
insert_user
─────────────
t
Inserts a new user into the database. The nickname must not already exist or
an exception will be thrown. The password must be at least four characters
long or an exception will be thrown. The status will be set to "new" and the
`set_by` set to the new user's nickname. The other parameters are:
full_name
: The full name of the user.
email
: The email address of the user. Must be a valid email address as verified by
[Email::Valid](http://search.cpan.org/perldoc?Email::Valid).
uri
: Optional URI for the user. Should be a valid URI as verified by
[Data::Validate::URI](http://search.cpan.org/perldoc?Data::Validate::URI).
twitter
: Optional Twitter username. Case-insensitive. A leading "@" will be removed.
why
: Optional text from the user explaining why she should be allowed access.
Returns true if the user was inserted, and false if not.
*/
BEGIN
IF char_length(password) < 4 THEN
RAISE EXCEPTION 'Password must be at least four characters long';
END IF;
INSERT INTO users (
nickname,
password,
full_name,
email,
uri,
twitter,
why,
set_by
)
VALUES (
insert_user.nickname,
crypt(insert_user.password, gen_salt('des')),
COALESCE(insert_user.full_name, ''),
insert_user.email,
insert_user.uri,
COALESCE(trim(leading '@' FROM insert_user.twitter), ''),
COALESCE(insert_user.why, ''),
insert_user.nickname
);
RETURN FOUND;
END;
$$;
CREATE OR REPLACE FUNCTION change_password(
nickname LABEL,
oldpass TEXT,
newpass TEXT
) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
/*
% SELECT change_password('strongrrl', '****', 'whatever');
change_password
─────────────────
t
Changes a user's password. The user must be active, and the old password must
match the existing password for the nickname or the password will not be set.
The password must be at least four charcters long or an exception will be
thrown. Returns true if the password was changed and false if it was not.
*/
BEGIN
IF char_length(newpass) < 4 THEN
RAISE EXCEPTION 'Password must be at least four characters long';
END IF;
UPDATE users
SET password = crypt(newpass, gen_salt('des')),
updated_at = NOW()
WHERE users.nickname = change_password.nickname
AND users.password = crypt(oldpass, password)
AND users.status = 'active';
RETURN FOUND;
END;
$$;
CREATE OR REPLACE FUNCTION update_user(
nickname LABEL,
full_name TEXT DEFAULT NULL,
email EMAIL DEFAULT NULL,
uri URI DEFAULT NULL,
twitter CITEXT DEFAULT NULL
) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
/*
% SELECT update_user(
nickname := 'theory',
full_name := 'David E. Wheeler',
email := 'justatheory@pgxn.org',
uri := 'http://www.justatheory.com/',
twitter :- 'theory'
);
update_user
─────────────
t
Update the specified user. The user must be active. The nickname cannot be
changed. The password can only be changed via `change_password()` or
`reset_password()`. Pass other attributes as:
full_name
: The full name of the user.
email
: The email address of the user. Must be a valid email address as verified by
[Email::Valid](http://search.cpan.org/perldoc?Email::Valid).
uri
: Optional URI for the user. Should be a valid URI as verified by
[Data::Validate::URI](http://search.cpan.org/perldoc?Data::Validate::URI).
twitter
: Optional Twitter username. A leading "@" wil be removed.
Returns true if the user was updated, and false if not.
*/
BEGIN
UPDATE users
SET full_name = COALESCE(update_user.full_name, users.full_name),
email = COALESCE(update_user.email, users.email),
uri = COALESCE(update_user.uri, users.uri),
twitter = COALESCE(trim(leading '@' FROM update_user.twitter), users.twitter),
updated_at = NOW()
WHERE users.nickname = update_user.nickname
AND users.status = 'active';
RETURN FOUND;
END;
$$;
CREATE OR REPLACE FUNCTION log_visit(
nickname LABEL
) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
/*
% SELECT log_visit('theory');
log_visit
───────────
t
Log the visit for the specified user. At this point, that just means that
`users.visited_at` gets set to the current time.
*/
BEGIN
UPDATE users
SET visited_at = NOW()
WHERE users.nickname = $1;
RETURN FOUND;
END;
$$;
CREATE OR REPLACE FUNCTION set_user_status(
setter LABEL,
nickname LABEL,
status STATUS
) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
/*
% SELECT set_user_status('admin', 'strongrrl', 'active');
set_user_status
─────────────────
t
Sets a user's status. The status may be one of "active", "inactive", or
"deleted". The nickname of the user who sets the status must be passed as the
first argument, and that user must be an administrator or an exception will be
thrown. That nickname will be stored in the `set_by` column. Users cannot
change their own status; if `setter` and `nick` are the same, an exception
will be thrown. Returns true if the status was set, and false if not. If the
status is already set to the specified value, the record will not be updated
and `false` will be returned.
*/
BEGIN
-- Make sure we have an admin.
PERFORM TRUE FROM users WHERE users.nickname = setter AND is_admin;
IF NOT FOUND THEN
RAISE EXCEPTION 'Permission denied: User “%” is not an administrator', setter;
END IF;
-- Prevent user from changing own status.
IF setter = nickname THEN
RAISE EXCEPTION 'Permission denied: User cannot modify own status';
END IF;
-- Go ahead and do it.
UPDATE users
SET status = set_user_status.status,
set_by = setter,
updated_at = NOW()
WHERE users.nickname = set_user_status.nickname
AND users.status <> set_user_status.status;
RETURN FOUND;
END;
$$;
CREATE OR REPLACE FUNCTION set_user_admin(
setter LABEL,
nickname LABEL,
set_to BOOLEAN
) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
/*
% select set_user_admin('admin', 'strongrrl', true);
set_user_admin
────────────────
t
Sets a user's administrator flag. The nickname of the user who does so must be
passed as the first argument, and that user must be an administrator.
Administrators may set their own administrator flags to `false`. If the
administrator flag is already set to the specified value, the record will not
be updated and `false` will be returned.
*/
BEGIN
-- Make sure we have an admin.
PERFORM TRUE FROM users WHERE users.nickname = setter AND is_admin;
IF NOT FOUND THEN
RAISE EXCEPTION 'Permission denied: User “%” is not an administrator', setter;
END IF;
-- Go ahead and do it.
UPDATE users
SET is_admin = set_to,
updated_at = NOW()
WHERE users.nickname = set_user_admin.nickname
AND is_admin <> set_to;
RETURN FOUND;
END;
$$;
CREATE OR REPLACE FUNCTION authenticate_user(
nickname CITEXT,
password TEXT
) RETURNS BOOLEAN LANGUAGE plpgsql VOLATILE SECURITY DEFINER AS $$
/*
% select authenticate_user('admin', '*****');
authenticate_user
────────────────
t
Returns true if the user with the specified nickname exists, is active, and
the password matches. Also updates the value of the `visited_at` column for
the user to the current timestamp. Returns false if the nickname and password
don't match or the user is not active.
*/
BEGIN
UPDATE users
SET visited_at = NOW()
WHERE users.nickname = authenticate_user.nickname
AND status = 'active'
AND users.password = crypt(authenticate_user.password, users.password);
RETURN FOUND;
END;
$$;
COMMIT;
Jump to Line
Something went wrong with that request. Please try again.