I can now connect to the test2 database using the test user role by specifying the test2 database using the -d flag as follows. In order for the changes in the pg_hba.conf config file to take effect the PostgreSQL service must be reloaded (the below command is for the Ubuntu system, to reload on CentOS use postgresql-10 instead of postgresql in this example). If you are unsure of where the pg_hba.conf file is on either the Ubuntu or CentOS operating systems consult my earlier article on PostgreSQL Installation and Configuration files for help. I can make this possible by editing the pg_hba.conf file specifying that the user test can make local peer based connections to the test2 database as shown below. Lets say I want to be able to connect to the test2 database I created in the last article as the user role named test. Often times it is desirable to connect to a database whose name is different from that of the user role that you are connecting from. Peer Authentication When User Roles Differ from Database Names If you are just joining in the tutorial series you will need to create a database named test by issuing the following command as the postgres Linux account user. Psql: FATAL: database "test" does not exist If I had not already created the test datadase I would get the following error. Recall from the last article on creating PostgreSQL databases I had already created a database named test as well as one named test2. Now I can switch to the new Linux user account named test and connect using psql as follows. To add a user on CentOS again requires the adduser command but also needs to be followed with the passwd command to specify its password like so. Is the information correct? Y Adding Linux System User on CentOS Įnter the new value, or press ENTER for the default Adding Linux System User on UbuntuĪdding a Linux system user and specifying its password is accomplished in one interactive adduser command on Ubuntu # adduser testĪdding new user `test' (1000) with group `test'. To get Peer Authentication to work I create a Linux system account named test which of course needs to be done with either the root Linux system account or another with sudo privileges. Psql: FATAL: Peer authentication failed for user "test" Thus, if I try to connect using the user role named test specifying the -U flag I get the following peer authentication error because their does not yet exist a Linux system account named test. Peer Authentication requires connecting from a Linux system account which has a name that matches the PostgreSQL user role and database name. If I try to connect to the database cluster with the psql program specifying the test user role it will fail because by default PostgreSQL uses Peer Authentication. You can see that the output from \du shows no privileges have been granted to the test and test2 user roles which matches what I interactively entered in the createuser commands. Postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | Postgres=# ALTER ROLE test WITH PASSWORD 'testuser' Īs I described my earlier article on using the psql interactive shell you can list all available roles by entering the psql shell and issuing the \du command as follows. $ createuser -pwprompt -interactive -echoĬREATE ROLE test2 PASSWORD 'md528d910d391470ad5ae52a54e9f1a1840' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN įor completeness I also show how to alter the test user role by adding a password in SQL. To specify a password to allow for a user to later connect via password authentication you should issue the createuser command with the -pwprompt flag as shown below to create a user role named test2. Note that this command is ran from the regular Linux shell, not the psql interactive shell. SELECT pg_t_config('search_path', '', false)ĬREATE ROLE test NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN Shall the new role be allowed to create more new roles? (y/n) n Shall the new role be allowed to create databases? (y/n) n Shall the new role be a superuser? (y/n) n I preferred to use the createuser program, in interactive mode, and with the echo flag set which displays the corresponding SQL statement being sent to the PostgreSQL server.įor example, to create a user account named test using the createuser command interactively in echo mode I use the following. To create a new user, or user role, you can either use a SQL statement or the createuser utility program. Think of roles as a set of privileges that are granted to perform various actions. User accounts in PostgreSQL are synonymous with the PostgreSQL concept of roles.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |