In order to create a test user with password instead of peer authentication, let's create test user:
createuser -P user0
createdb user0-P makes it prompt for the users password.Alternatively, to create the password non-interactively stackoverflow.com/questions/42419559/postgres-createuser-with-password-from-terminal:Can't find a way using the
psql -c "create role NewRole with login password 'secret'"createuser helper.We can then login with that password with:which asks for the password we've just set, because the
psql -U user0 -h localhost-h option turns off peer authentication, and turns off password authentication.The password can be given non-interactively as shown at stackoverflow.com/questions/6405127/how-do-i-specify-a-password-to-psql-non-interactively with the
PGPASSWORD environment variable:PGPASSWORD=a psql -U user0 -h localhostNow let's create a test database which
user0 can access with an existing superuser account:createdb user0db0
psql -c 'GRANT ALL PRIVILEGES ON DATABASE user0db0 TO user0'We can check this permission with:which now contains:The permission letters are explained at:
psql -c '\l' List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
user0db0 | ciro | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/ciro +
| | | | | ciro=CTc/ciro +
| | | | | user0=CTc/cirouser0 can now do the usual table operations on that table:PGPASSWORD=a psql -U user0 -h localhost user0db0 -c 'CREATE TABLE table0 (int0 INT, char0 CHAR(16));'
PGPASSWORD=a psql -U user0 -h localhost user0db0 -c "INSERT INTO table0 (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');"
PGPASSWORD=a psql -U user0 -h localhost user0db0 -c 'SELECT * FROM table0;'Articles by others on the same topic
There are currently no matching articles.