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).

how do?

Show thread

@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 $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. :blobcatpat2:

Sign in to participate in the conversation

A lonely little town in the wider world of the fediverse.