Encrypt password when changing it
CREATE/ROLE ... PASSWORD 'some_password'
sends and logs the specified password as is. Thus, specifying
an unencrypted password is dangerous.
Those statements accept an encrypted password (hashed with MD5 or SCRAM).
ltsql's \password is convenient
ltsql runs
"SHOW password_encryption" to
determine the password hash scheme (MD5 or SCRAM), hashes
the supplied password, and then issues an ALTER command.
The hashed password still can appear in the server log. Temporarily setting log_min_error_statement to 'PANIC' prevents that.
In-database authentication profile is very limited
Lightdb offers only password expiration by
CREATE/ROLE VALID UNTIL 'some_timestamp'.
Does not provide functionality such as:
Enforcing password complexity
Locking out a user account when the number of failed login attempts exceeds a threshold within a certain period of time
Restricting reuse of the same password before a certain number of days pass
Implementing password complexity: use either of:
An external identity service such as LDAP or Kerberos
Uses SSL client certificates to perform authentication. Does not require password.
Tracking failed login attempts: Do either of:
Search the server log for messages that include "password authentication failed" or the SQLSTATE 28P01 (invalid_password)
Using SQLSTATE is better than the message text, because the message can vary depending on the server version and lc_message setting. (Add %e to log_line_prefix to emit the SQLSTATE.)
Role privileges are inherited by default
In the SQL standard and other DBMSs,
SET ROLE needs to be used to gain
privileges of another role.
In Lightdb, a role automatically inherits the privileges of other roles that it is a member of. This might be surprising.
To approximate the SQL standard, use NOINHERIT for users and NOINHERIT for roles.
Predefined roles
Some roles are provided to give part of administrative privileges to non-superusers.
They can be given by GRANT.
The representative roles are:
pg_monitor: can read various useful configuration settings, statistics and other system information.
pg_signal_backend: can send signals to other backends to cancel a query or terminate a session.
pg_read_server_files, pg_write_server_files and pg_execute_server_program: access files and run programs on the database server as the user the database runs as. e.g., these enable COPY data to/from files on the server or another program like gzip and curl.
Default privileges
ALTER DEFAULT PRIVILEGES can set the
default privileges that will be automatically given to
database objects created in the future.
ex.
ALTER DEFAULT PRIVILEGES IN SCHEMA app_schema GRANT INSERT, UPDATE, DELETE, SELECT ON TABLES TO app_user;
The target database objects are schema, table, view, sequence, function, and type.
Does not change the privileges of existing database objects.