Re: [GENERAL] I'm in need of something that should be there
Ralph Smith wrote: >> Ralph Smith wrote: >> >> > And should be easier to find in the manual! >> > >> > I've looked in many related chapters of the 8.2 manual for a way to >> > find out >> > WHY a specific user has access to a database. >> > >> > Chapter 5Data Definition >> > Chapter 18 Database Roles & Privileges >> > Chapter 20 Client Authorization >> >> >> > postgres=# select * from pg_roles; >> > rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | >> rolcatupdate | rolcanlogin | rolconnlimit | rolpassword | >> rolvaliduntil | rolconfig | oid >> > --+--++---+- >> +--+-+--+- >> +---+---+--- >> > lines removed >> > smithrn | f| f | t | t | >> f| t | -1 | | >> infinity | | 16393 >> > >> > This user can connect via his .pgpass or manually since he's in a >> > netID range that requires a password. >> > But he can create and drop tables in any database!!! >> > >> > Why is that? >> > How can I find out what he can do? >> > The GRANT and REVOKE sections say nothing about which pg_ tables >> > to query, and I've been lookin'! >> > >> > >> > Thank you! >> > >> > Ralph Smith >> > >> > = >> >> http://www.postgresql.org/docs/8.3/interactive/sql-grant.html >> >> "Depending on the type of object, the initial default privileges might >> include granting some privileges to PUBLIC. The default is ... >> CONNECT >> privilege and TEMP table creation privilege for databases" >> >> http://www.postgresql.org/docs/8.3/interactive >> /ddl-schemas.html#DDL-SCHEMAS-PUBLIC >> >> Note that by default, everyone has CREATE and USAGE privileges on >> the schema >> public. This allows all users that are able to connect to a given >> database >> to create objects in its public schema. If you do not want to allow >> that, >> you can revoke that privilege: >> >> REVOKE CREATE ON SCHEMA public FROM PUBLIC; > > Ralph's followup. > > So am I to assume that there is no way to query just what privs a user/ > role has on an object, anything, from a DB to an index? > > > Thank you again, > Ralph Smith > You could also query information_schema.table_privileges for information about table grants. http://www.postgresql.org/docs/8.3/interactive/infoschema-table-privileges.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] I'm in need of something that should be there
Ralph Smith wrote: > And should be easier to find in the manual! > > I've looked in many related chapters of the 8.2 manual for a way to > find out > WHY a specific user has access to a database. > > Chapter 5Data Definition > Chapter 18 Database Roles & Privileges > Chapter 20 Client Authorization > postgres=# select * from pg_roles; > rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | > rolcatupdate | rolcanlogin | rolconnlimit | rolpassword | > rolvaliduntil | rolconfig | oid > --+--++---+- > +--+-+--+- > +---+---+--- > lines removed > smithrn | f| f | t | t | > f| t | -1 | | > infinity | | 16393 > > This user can connect via his .pgpass or manually since he's in a > netID range that requires a password. > But he can create and drop tables in any database!!! > > Why is that? > How can I find out what he can do? > The GRANT and REVOKE sections say nothing about which pg_ tables > to query, and I've been lookin'! > > > Thank you! > > Ralph Smith > > = http://www.postgresql.org/docs/8.3/interactive/sql-grant.html "Depending on the type of object, the initial default privileges might include granting some privileges to PUBLIC. The default is ... CONNECT privilege and TEMP table creation privilege for databases" http://www.postgresql.org/docs/8.3/interactive /ddl-schemas.html#DDL-SCHEMAS-PUBLIC Note that by default, everyone has CREATE and USAGE privileges on the schema public. This allows all users that are able to connect to a given database to create objects in its public schema. If you do not want to allow that, you can revoke that privilege: REVOKE CREATE ON SCHEMA public FROM PUBLIC; ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Question about Postgres
NetComrade wrote: > I apologize for cross-posting, but I need some help w/o too many > advices RTFM :). After Oracle and MySQL, this becomes the third > product that I need to learn to some degree, and I need a few links > which would provide a 'quick tutorial' especially for folks with > Oracle background like myself. Last time I had to deal with MySql it > took me a few days just to figure out how to login, and then how to > poke around, and then a few more to finally start writing some useful > code in whatever language they use that's similar to PL/SQL. > > We are running a mail server, which for whatever stupid reason uses a > database (stupid, b/c it only uses it for web access, mail is actually > on the file system) > > I'd like to know a couple of things > a) how do I access this thing as a DBA to poke around The command line client is psql. Since psql defaults to port 5432 and you do appear to have two clusters running use psql -p 5733 to access the other cluster. > b) how do I poke around psql -l from the command line to list databases: [EMAIL PROTECTED]:~$ psql -l List of databases Name| Owner | Encoding ---+--+--- amarok| john | UTF8 john | john | UTF8 postgres | postgres | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII (5 rows) \? for help \dS to list system tables \dt to list tables \d table_name to describe a table amarok=> \d pg_tables View "pg_catalog.pg_tables" Column| Type | Modifiers -+-+--- schemaname | name| tablename | name| tableowner | name| tablespace | name| hasindexes | boolean | hasrules| boolean | hastriggers | boolean | View definition: SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS "tablespace", c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.reltriggers > 0 AS hastriggers FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace WHERE c.relkind = 'r'::"char"; > c) do I need to make any modifications to config file > d) what is the most common 'default' values that need to be changed > what's the best way to see what a performance bottleneck is (i > e) why this doesn't work: > [EMAIL PROTECTED] httpd]# vacuumdb -a -f -z -q -h 10.0.1.93 -p5733 > vacuumdb: could not connect to database template1: FATAL: no > pg_hba.conf entry for host "10.0.1.93", user "root", database > "template1", SSL off pg_hba.conf controls who can connect to which database. If the two lines below are your pg_hba.conf file then only the scalix user can connect to the scalix database from 10.0.1.201 using a password. Nobody else can use tcp/ip connections. Try vacuumdb -avz as the postgres user and vacuumdb -avz -p5733 as whatever user the other cluster is running as > Some 'details' on the server: (ps -ef) > 00:00:04 /usr/bin/postmaster -i -h mt-adm.mentora.biz -p 5733 -D > /var/opt/scalix/mm/postgres/data -k /var/opt/scalix/mm/postgres/data > 00:00:00 postgres: stats buffer process > 00:00:00 postgres: stats collector process > 00:00:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data > 00:00:00 postgres: stats buffer process > 00:00:00 postgres: stats collector process > > The above is confusing.. do I have 2 instances (or databases) running > on different ports? Should I shutdown the default one? (Scalix is the > product that uses the db) That does look like 2 clusters running. Use -p on the command line or set PGPORT to get to the one on 5733. Try listing the databases for each cluster with psql -l. If the /var/lib/pgsql/data cluster does not have any databases you may not need it. > Should I be 'playing' with > /var/opt/scalix/mm/postgres/data/postgresql.conf ? Yes, each cluster will have its own set of config files. > [EMAIL PROTECTED] httpd]# du -skh /var/opt/scalix/mm/postgres/data > 276M/var/opt/scalix/mm/postgres/data > > # "database" is rather small If all of your attempts to vacuum are failing then some of that will be bloat. > # there are no more than 20-30 users on the server at any given time > # the disks are fast (50megs/sec, RAID10, SCSI) > # memory is big 8g > # cpu count is 2 with hyperthreading (it's a dell 2650) > > [EMAIL PROTECTED] data]# rpm -qa|grep post > postgresql-libs-7.4.17-1.RHEL4.1 > postgresql-server-7.4.17-1.RHEL4.1 > postgresql-7.4.17-1.RHEL4.1 > scalix-postgres-11.0.4.25-1 > > Files that seem important: > /var/opt/scalix/mm/postgres/data > > > [EMAIL PROTECTED] data]# cat pg_hba.conf|grep -v \# > hostscalix scalix 10.0.1.201/32 md5 > local allall ident sameuser Is this from /var/opt/scalix/mm/postgres/data/pg_hba.conf or /var/lib/pgsql/data/pg_hba.conf > postgresql.conf (comments taken out) > max_connections = 100 > shared_buffers = 1000 > # bunch of locale params en_US.UTF-8' >
Re: [GENERAL] HINT: Perhaps out of disk space?
On Fri, 23 Dec 2005 13:42:13 -0500, Michael Adler wrote: > On Fri, Dec 23, 2005 at 11:36:54AM -0500, Tom Lane wrote: >> Michael Adler <[EMAIL PROTECTED]> writes: >> > I'm investigating a problem that happened last night and I would >> > appreciate any recommendations. The logs indicate that the disks were >> > full, but I truly doubt that since we only use about 14GB out of the >> > available 65GB. >> >> > I found entries like this in the logs: >> >> > ERROR: could not write block 2354 of temporary file: No space left on >> > device >> > HINT: Perhaps out of disk space? >> > >> > ERROR: could not extend relation "parent_table": No space left on device >> > HINT: Check free disk space. >> > >> > LOG: could not close temporary statistics file >> > "/var/lib/postgres/data/global/pgstat.tmp.1464": No space left on device >> >> > According to the logs, the problem went away after a reboot. I wonder >> > if the kernel or the RAID device got confused and postgres was simply >> > echoing what it was told. We run a couple hundred postgres servers and >> > we have not seen this before (except when the disks truly were full). > I really appreciate any guidance you could offer. > Are there any errors about running out of shared memory? I have seen the "No space left on device" error for that on FreeBSD before. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
