how to setup a dev environment with postgresql...?
(i come from a mysql background)
I want to install a postgres database on my laptop for dev reasons.
i want my user account to have full password-prompt-less access to all databases, and i need a virtual user account that has access to some databases (based on a glob/prefix/suffix of the database name).
@subleq There are many ways to do that, depending on pg_hba.conf. But I think the following would work on a standard Gentoo install:
root# psql -U postgres
CREATE ROLE $username LOGIN PASSWORD 'abcd' SUPERUSER;
CREATE ROLE $virtualuser LOGIN PASSWORD 'xyz';
CREATE DATABASE somedb OWNER $virtualuser;
username$ cat >.pgpass
Now you can login:
$ psql somedb
$ psql -U $virtualuser somedb
Substitue $username and $virtualuser as appropriate. Globbing for permissions is not supported, AFAIK.
@ayo instead of globbing i might be able to do the same thing with samerole/CREATEROLE, but i'd want to limit CREATEROLE just for database names that it created...
$virtualuser=> create database $blah;
$virtualuser=> grant role $blah to $virtualuser;
@subleq The CREATEDB flag would allow a role to create databases and take ownership of those databases (but no name limitation).
...but it seems a little weird to me to allow non-superusers to create new databases. What're you using multiple databases for?
@ayo per-(website)user data separation
e.g. in case of gdpr data requests, i just dump the one database
@subleq I think the usual user_id column and an extraction script is the easier approach, but if you want table-level separation I think schemas can do the trick as well. pg_dump can limit the dump to a single schema.
(I've never had a data request for vndb btw, so optimizing for that seems a little silly :P)
@ayo well i wanted lazy (as in the next time the user accesses the website) programmatic per-user database migrations
this is gonna be a weird database setup, i know...
@ayo but i think the CREATEDB + OWNER $virtualuser might just work...
@subleq Sounds painful, but would probably work. Good luck.
A lonely little town in the wider world of the fediverse.