List of sql queries to manage users, roles
brew services start postgresql
/usr/local/var/log/postgres.log
db.m6g.large + 100go + General purpose SSD storage (avoids extra IOPS costs)
SELECT
usename AS role_name,
CASE WHEN usesuper
AND usecreatedb THEN
CAST('superuser, create database' AS pg_catalog.text)
WHEN usesuper THEN
CAST('superuser' AS pg_catalog.text)
WHEN usecreatedb THEN
CAST('create database' AS pg_catalog.text)
ELSE
CAST('' AS pg_catalog.text)
END role_attributes
FROM
pg_catalog.pg_user
ORDER BY
role_name DESC;
SELECT * FROM pg_roles;
SELECT
*
FROM
information_schema.role_table_grants
WHERE
table_schema = 'public';
SELECT datname
FROM pg_database
WHERE has_database_privilege('username', datname, 'CONNECT')
SELECT
r.usename AS grantor,
e.usename AS grantee,
nspname,
privilege_type,
is_grantable
FROM
pg_namespace
JOIN LATERAL (
SELECT
*
FROM
aclexplode(nspacl) AS x) a ON TRUE
JOIN pg_user e ON a.grantee = e.usesysid
JOIN pg_user r ON a.grantor = r.usesysid;
Some context for AWS and the lack of superuser privileges.
/* Note: does not affect objects within other databases, so repeat for all DBs where the role owns anything. */
REASSIGN OWNED BY username TO postgres;
/* Drops all the objects within the current database that are owned by the role. */
DROP OWNED BY username;
DROP ROLE username;
The following might be necessary:
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM username;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM username;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM username;
I tend to use 3 roles: a simple readonly one (often going to a read-replica), write for row creation or update, ops for migrations.
readonly rolereadonly roleCREATE ROLE role_appname_readonly;
GRANT CONNECT ON DATABASE database_appname_production TO role_appname_readonly;
GRANT USAGE ON SCHEMA public TO role_appname_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO role_appname_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO role_appname_readonly;
CREATE USER user_appname_readonly WITH PASSWORD 'xxxxxx';
GRANT role_appname_readonly TO user_appname_readonly;
write rolewrite roleCREATE ROLE role_appname_write;
GRANT CONNECT ON DATABASE database_appname_production TO role_appname_write;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO role_appname_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO role_appname_write;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO role_appname_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO role_appname_write;
CREATE USER user_appname_write WITH PASSWORD 'xxxxxx';
GRANT role_appname_write TO user_appname_write;
ops roleops roleCREATE ROLE role_appname_ops;
GRANT CONNECT ON DATABASE database_appname_production TO role_appname_ops;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO role_appname_ops;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO role_appname_ops;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO role_appname_ops;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO role_appname_ops;
CREATE USER user_appname_ops WITH PASSWORD 'xxxxxx';
GRANT role_appname_ops TO user_appname_ops;
On your local setup, in order to be able to run all migrations, add:
ALTER USER user_appname_ops CREATEDB;
Note: in PostgreSQL, only the owner of a database can drop a database. This is a non issue in production, as on AWS the db already exists and won’t be owned by user_appname_ops
pwgen 128 3